postgres replication without pg_basebackup? postgres 13.3

Dear all, We have a very large database of 37TB and we had to promote our standby to primary due to some disk failures. Now the issues are solved, we would like to make standby the old primary from a copy of the new primary which is already in place. Is it possible without using pg_basebackup?

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On Sun, Nov 06, 2022 at 09:48:32AM -0500, Tom Lane wrote: > Stefan Froehlich writes: > > | # create extension amcheck; > > | # select oid, relname from pg_class where relname ='faultytablename_pkey'; > > | [returns oid 537203] > > | # select bt_index_check(537203, true); > > | server closed the co

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On Mon, 2022-11-07 at 11:17 +0100, Stefan Froehlich wrote: > On Sun, Nov 06, 2022 at 09:48:32AM -0500, Tom Lane wrote: > > Stefan Froehlich writes: > > > > # create extension amcheck; > > > > # select oid, relname from pg_class where relname > > > > ='faultytablename_pkey'; > > > > [returns oid 5

Re: postgres replication without pg_basebackup? postgres 13.3

On Mon, 2022-11-07 at 11:02 +0100, Pilar de Teodoro wrote: > We have a very large database of 37TB and we had to promote our standby to > primary due to > some disk failures. Now the issues are solved, we would like to make standby   > the old primary > from a copy of the new primary which is alre

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On 11/7/22 06:19, Laurenz Albe wrote: Don't continue to work with that cluster even if everything seems OK now. "pg_dumpall" and restore to a new cluster on good hardware. Why would that be necessary if the original machine works well now? -- Mladen Gogala Database Consultant Tel: (347) 321-12

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On Mon, Nov 07, 2022 at 08:17:10AM -0500, Mladen Gogala wrote: > On 11/7/22 06:19, Laurenz Albe wrote: > >Don't continue to work with that cluster even if everything seems OK now. > >"pg_dumpall" and restore to a new cluster on good hardware. > Why would that be necessary if the original machine

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

Stefan Froehlich writes: > On Mon, Nov 07, 2022 at 08:17:10AM -0500, Mladen Gogala wrote: >> On 11/7/22 06:19, Laurenz Albe wrote: >>> Don't continue to work with that cluster even if everything seems OK now. >>> "pg_dumpall" and restore to a new cluster on good hardware. >> Why would that be ne

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On Mon, Nov 07, 2022 at 09:02:26AM -0500, Tom Lane wrote: > Stefan Froehlich writes: > > On Mon, Nov 07, 2022 at 08:17:10AM -0500, Mladen Gogala wrote: > >> On 11/7/22 06:19, Laurenz Albe wrote: > >>> Don't continue to work with that cluster even if everything seems OK now. > >>> "pg_dumpall" and

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On 11/7/22 08:02, Tom Lane wrote: [snip] call. It'd still be recommendable to pg_dumpall and restore into a freshly-initdb'd cluster, because otherwise you can't be real sure that you identified and cleared all the data corruption. Why *just* pg_dumpall instead of "pg_dumpall --globals-only" an

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

Ron writes: > On 11/7/22 08:02, Tom Lane wrote: >> call. It'd still be recommendable to pg_dumpall and restore into >> a freshly-initdb'd cluster, because otherwise you can't be real >> sure that you identified and cleared all the data corruption. > Why *just* pg_dumpall instead of "pg_dumpall --

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On 11/7/22 09:43, Tom Lane wrote: Ron writes: On 11/7/22 08:02, Tom Lane wrote: call. It'd still be recommendable to pg_dumpall and restore into a freshly-initdb'd cluster, because otherwise you can't be real sure that you identified and cleared all the data corruption. Why *just* pg_dumpall

AW: AW: Reducing bandwidth usage of database replication

Thanks, the wal_sender_timeout setting was the culprit! A bit hard to find that it influences a ping at half its interval time though. Sascha Zenglein Produktentwicklung [cid:gessler_email_logo_23bb5200-2c8c-4c2d-a63e-71d5bf29d89f.gif] Gessler GmbH Gutenber

copy file from a client app to remote postgres isntance

Hi all, I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https:// stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy meta-command in a psql command: psql -U %s -p %s -d %s -f - <

Re: copy file from a client app to remote postgres isntance

On 11/7/22 09:57, Вадим Самохин wrote: Hi all, I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://stackoverflow.com/a/9327519/618020 ). It boils down to spec

Segmentation Fault PG 14

Hello! I started to use version `14.5-2.pgdg20.04+2` for a dedicated database and I'm facing many segmentation faults during the day when the database has more heavy queries. The server log there are many of this: ``` 2022-11-07 17:23:19.423 UTC [728] LOG: background worker "parallel worker" (PI

Re: copy file from a client app to remote postgres isntance

Well, actually, just ordinary 3 tier architecture. Simple UI connected via restful API with backend written in php, which copies some data in a remote database, that's pretty much it. пн, 7 нояб. 2022 г. в 20:30, Rob Sargent : > On 11/7/22 09:57, Вадим Самохин wrote: > > Hi all, > I have an appli

Re: copy file from a client app to remote postgres isntance

On 11/7/22 10:51, Вадим Самохин wrote: Well, actually, just ordinary 3 tier architecture. Simple UI connected via restful API with backend written in php, which copies some data in a remote database, that's pretty much it. пн, 7 нояб. 2022 г. в 20:30, Rob Sargent : On 11/7/22 09:57, Вадим

Re: Segmentation Fault PG 14

Willian Colognesi writes: > I started to use version `14.5-2.pgdg20.04+2` for a dedicated database and > I'm facing many segmentation faults during the day when the database has > more heavy queries. I take it things were okay with the version you used previously? What was that exactly? Has anyt

My account was locked in pgadmin4

I reset the password but i don't receive any message -- With kindest regards, William. ⢀⣴⠾⠻⢶⣦⠀ ⣾⠁⢠⠒⠀⣿⡁ Debian - The universal operating system ⢿⡄⠘⠷⠚⠋⠀ https://www.debian.org ⠈⠳⣄

Re: copy file from a client app to remote postgres isntance

On 11/7/22 8:57 AM, Вадим Самохин wrote: Hi all, I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://stackoverflow.com/a/9327519/618020 ). It boils down to sp

Re: Segmentation Fault PG 14

Hi Tom, `I take it things were okay with the version you used previously?` Yes, it was working pretty well in another instance with pg version `12.4-1.pgdg18.04+1`, and we had to make a migration of one database that was running in this server to another using Logical Replication. the process was

Re: Segmentation Fault PG 14

On 11/7/22 10:36 AM, Willian Colognesi wrote: Hi Tom, `I take it things were okay with the version you used previously?` Yes, it was working pretty well in another instance with pg version `12.4-1.pgdg18.04+1`, and we had to make a migration of one database that was running in this server to a

Re: Segmentation Fault PG 14

1) What versions of pg_dump and pg_restore did you use? A: pg_dump and pg_restore was done using pg 14 (the same as the destination was running) 2) To be clear the subscription was started after the restore? A: Yes 3) Where there any error messages issued at any point in below? A: no errors durin

Re: Segmentation Fault PG 14

On 11/7/22 10:57 AM, Willian Colognesi wrote: 1) What versions of pg_dump and pg_restore did you use? A: pg_dump and pg_restore was done using pg 14 (the same as the destination was running) 2) To be clear the subscription was started after the restore? A: Yes 3) Where there any error message

Re: Segmentation Fault PG 14

No, the origin where the database was was running ubuntu 18.04.5 x86_64 and the destination ubuntu 20.04.5 aarch64 On Mon, Nov 7, 2022 at 4:00 PM Adrian Klaver wrote: > On 11/7/22 10:57 AM, Willian Colognesi wrote: > > 1) What versions of pg_dump and pg_restore did you use? > > A: pg_dump and pg

Re: Segmentation Fault PG 14

On 11/7/22 11:03 AM, Willian Colognesi wrote: No, the origin where the database was was running ubuntu 18.04.5 x86_64 and the destination ubuntu 20.04.5 aarch64 Where I was going was this: https://wiki.postgresql.org/wiki/Locale_data_changes Then I realized you had not done any binary upgrade

Re: Segmentation Fault PG 14

Willian Colognesi writes: > `I take it things were okay with the version you used previously?` > Yes, it was working pretty well in another instance with pg version > `12.4-1.pgdg18.04+1`, and we had to make a migration of one database that > was running in this server to another using Logical Re

Re: Segmentation Fault PG 14

All the extensions installed in this database are these: ``` List of installed extensions Name| Version | Schema | Description +-++--- amcheck

Re: Segmentation Fault PG 14

On 11/7/22 12:15, Willian Colognesi wrote: All the extensions installed in this database are these: ```                                      List of installed extensions         Name        | Version |   Schema   |  Description +-+---

Re: Segmentation Fault PG 14

No, the database is running well, no problem until now after disabled *jit.* I just realized that he send an email direct to me, the message was: ``` I had similar problems with and the cure was to turn off jit in Postgres.conf jit = off -- Boris ``` On Mon, Nov 7, 2022 at 5:25 PM Adrian Klave

Re: copy file from a client app to remote postgres isntance

On 11/7/22 10:57, Вадим Самохин wrote: Hi all, I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://stackoverflow.com/a/9327519/618020 ). It boils down to speci

Re: Setting up replication on Windows, v9.4

> > > > v9.4 has been EOL for 2 years 9 months. As I said, the next step will be to upgrade. It would make sense to upgrade first, since "there have been some big advances since then which make replication much easier" But when we upgraded, the app stopped working. So I'll need to go through and n

Re: Segmentation Fault PG 14

Willian Colognesi writes: > No, the database is running well, no problem until now after disabled *jit.* Interesting. Which version of LLVM is installed? regards, tom lane

Re: Setting up replication on Windows, v9.4

On 11/7/22 13:59, Brad White wrote: > v9.4 has been EOL for 2 years 9 months. As I said, the next step will be to upgrade. It would make sense to upgrade first, since "there have been some big advances since then which make replication much easier" But when we upgraded, the app stopped w

Re: postgres replication without pg_basebackup? postgres 13.3

Dear Laurenz, Thank you very much for the idea. We ran pg_rewind correctly: [postgres@gacsdb05 data-13.3]$ pg_rewind -c -R --target-pgdata=/PostgresDB/sas_hdd/data-13.3/ --source-server="host= port= user=postgres password=XXX" pg_rewind: source and target cluster are on the same timeline

Re: Segmentation Fault PG 14

Do you mean how it was compiled? the output of pg_config is it: ``` root@ip-10-x-x-x:/home/ubuntu# pg_config --configure '--build=aarch64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include' '--mandir=${prefix}/share/man' '--infodir=${prefix}/share/info' '--sysconfdir=/etc' '--localstatedir

Re: Segmentation Fault PG 14

Willian Colognesi writes: > There is no llvm installed on ubuntu server, postgresql was installed via > apt package `apt install postgresql-14` If there's no LLVM around, then disabling JIT wouldn't do anything, because it depends on LLVM to compile code. We should perhaps wait awhile to see if

Re: Segmentation Fault PG 14

On Mon, Nov 7, 2022 at 2:38 PM Tom Lane wrote: > > Willian Colognesi writes: > > `I take it things were okay with the version you used previously?` > > > Yes, it was working pretty well in another instance with pg version > > `12.4-1.pgdg18.04+1`, and we had to make a migration of one database th

PCI:SSF - Safe SQL Query & operators filter

Hi team, I am leading PCI:SSF audit on our payment switch platform product and having a issue where our QSA just came with multiple ways how to escalate privileges and get a shell to the remote host through our built-in Reporting system which runs on PostgreSQL (12-14). 1. select * from pg_

Re: PCI:SSF - Safe SQL Query & operators filter

> On Nov 7, 2022, at 17:24, Jan Bilek wrote: > Would there be any way to go around this? The typical configuration is to not permit the PostgreSQL superuser to log in remotely. The database can be managed by a different, non-superuser role, including schema migrations. > CREATE OR REPLACE

Re: PCI:SSF - Safe SQL Query & operators filter

On 11/8/22 11:29, Christophe Pettus wrote: > >> On Nov 7, 2022, at 17:24, Jan Bilek wrote: >> Would there be any way to go around this? > The typical configuration is to not permit the PostgreSQL superuser to log in > remotely. The database can be managed by a different, non-superuser role, > i

Re: PCI:SSF - Safe SQL Query & operators filter

> On Nov 7, 2022, at 17:43, Jan Bilek wrote: > > Well, superuser (our App) is already logged in and as it is designed > very much as an "appliance" it simply does that job - manages its > database. Well... don't do that. :) The problem is analogous to having root log into a Linux box and

Re: PCI:SSF - Safe SQL Query & operators filter

On Mon, Nov 7, 2022 at 6:25 PM Jan Bilek wrote: > The main problem comes from obvious - our application's PostgreSQL user > needs to have an Superuser role as it manages most of its (dedicated) > database (creates tables, drops those, manages views, triggers ... ). > No, the things you want to ma

Re: PCI:SSF - Safe SQL Query & operators filter

On Mon, Nov 7, 2022 at 8:25 PM Jan Bilek wrote: > ... > select * from pg_read_file('/etc/passwd' , 0 , 100); -> it's possible to > display content of '/etc/passwd/' file > select version(); -> Result of DBMS version request. Input filtering may help in the interim, until you get the roles an

Re: PCI:SSF - Safe SQL Query & operators filter

On Tue, 2022-11-08 at 01:24 +, Jan Bilek wrote: > I am leading PCI:SSF audit on our payment switch platform product and having > a issue where our QSA > just came with multiple ways how to escalate privileges and get a shell to > the remote host through > our built-in Reporting system which r

Re: postgres replication without pg_basebackup? postgres 13.3

On Mon, 2022-11-07 at 23:11 +0100, Pilar de Teodoro wrote: > Thank you very much for the idea. [of running pg_rewind] > We ran pg_rewind correctly: > [postgres@gacsdb05 data-13.3]$ pg_rewind -c -R > --target-pgdata=/PostgresDB/sas_hdd/data-13.3/  --source-server="host= > port= user=postgr

Re: PCI:SSF - Safe SQL Query & operators filter

On 11/8/22 11:50, Christophe Pettus wrote: > >> On Nov 7, 2022, at 17:43, Jan Bilek wrote: >> >> Well, superuser (our App) is already logged in and as it is designed >> very much as an "appliance" it simply does that job - manages its >> database. > Well... don't do that. :) The problem is analog

Re: My account was locked in pgadmin4

Please send your message to pgadmin-supp...@postgresql.org for getting suggestions on pgAdmin 4. -- Thanks & Regards, Ashesh Vashi EnterpriseDB INDIA: Enterprise PostgreSQL Company *http://www.linkedin.com/in/asheshvashi*

Re: PCI:SSF - Safe SQL Query & operators filter

On Tue, 2022-11-08 at 04:14 +, Jan Bilek wrote: > I know it is not exactly what you suggested (and agreeing a lot with our > app user shouldn't be running as superuser), but as all other inputs > from our application come sanitized through bind and this is the only > way where user can send

Re: Segmentation Fault PG 14

On Tue, Nov 8, 2022 at 11:45 AM Willian Colognesi wrote: > root@ip-10-x-x-x:/home/ubuntu# pg_config --configure > ... --with-extra-version= (Ubuntu 14.5-2.pgdg20.04+2)' ... > ... '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-10' ... > There is no llvm installed on ubuntu server, postgresql was

Re: PCI:SSF - Safe SQL Query & operators filter

On 11/8/22 17:03, Laurenz Albe wrote: > On Tue, 2022-11-08 at 04:14 +, Jan Bilek wrote: > >> I know it is not exactly what you suggested (and agreeing a lot with our >> app user shouldn't be running as superuser), but as all other inputs >> from our application come sanitized through bind and t