Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

This would help me. I will try out the pg_rewind and rsync options. On Mon, 25 Nov 2024 at 15:19, Ilya Anfimov wrote: > On Sun, Nov 24, 2024 at 09:35:15PM +0530, Subhash Udata wrote: > >Dear PostgreSQL Community, > > > >I have a production database setup with a primary server and a stand

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 09:35:15PM +0530, Subhash Udata wrote: >Dear PostgreSQL Community, > >I have a production database setup with a primary server and a standby >server. The database is currently running on PostgreSQL 15.0, and I plan >to upgrade both servers to 15.9. > >I

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 4:58 PM Adrian Klaver wrote: > On 11/24/24 13:00, Ron Johnson wrote: > > On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus > > wrote: > > > > > On Nov 24, 2024, at 09:15, Ron Johnson > > wrote: > > >

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On 11/24/24 13:00, Ron Johnson wrote: On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus > wrote: > On Nov 24, 2024, at 09:15, Ron Johnson mailto:ronljohnso...@gmail.com>> wrote: > > Doesn't the existence of a replication slot force PG to retain WAL fi

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus wrote: > > On Nov 24, 2024, at 09:15, Ron Johnson wrote: > > > > Doesn't the existence of a replication slot force PG to retain WAL files > when replication is broken? > > It does. I don't recall if the OP said that they were using a persistent

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

> On Nov 24, 2024, at 09:15, Ron Johnson wrote: > > Doesn't the existence of a replication slot force PG to retain WAL files when > replication is broken? It does. I don't recall if the OP said that they were using a persistent replication slot or not; it's not as common with binary replic

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

Thank you, everyone, for your valuable clarifications and insights. Your guidance has been incredibly helpful in addressing my concerns and understanding the best approach to proceed. I truly appreciate the time and effort you’ve taken to assist me. On Sun, 24 Nov 2024 at 22:36, Christophe Pettus

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

> On Nov 24, 2024, at 09:03, Subhash Udata wrote: > When we shut down the standby, upgrade it, and then start it back up, will > the replication automatically resume from the primary to the standby? Assuming that the standby has access to any WAL generated during the shutdown (either still i

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 12:06 PM Christophe Pettus wrote: > > On Nov 24, 2024, at 09:03, Subhash Udata wrote: > > When we shut down the standby, upgrade it, and then start it back up, > will the replication automatically resume from the primary to the standby? > > Assuming that the standby has a

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

> On Nov 24, 2024, at 08:51, Subhash Udata wrote: > However, my concern lies in the fact that we are working with production > servers, where downtime is not acceptable. There is no way to upgrade community PostgreSQL, either to a new minor version or a new major version, with absolute zero

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

Thank you for your valuable suggestion! I have a question regarding the process: When we shut down the standby, upgrade it, and then start it back up, will the replication automatically resume from the primary to the standby? Looking forward to your clarification. 2) What do you mean by resource

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 11:52 AM Subhash Udata wrote: > I understand your point and appreciate the clarification. > > I have reviewed the references and now have a better understanding of the > minor upgrade process. > > However, my concern lies in the fact that we are working with production > s

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

I understand your point and appreciate the clarification. I have reviewed the references and now have a better understanding of the minor upgrade process. However, my concern lies in the fact that we are working with production servers, where downtime is not acceptable. Additionally, if a failov

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 11:41 AM Adrian Klaver wrote: > On 11/24/24 08:36, Subhash Udata wrote: > > The reason to upgrade from 15.0 to 15.9 is this > > https://www.postgresql.org/support/security/CVE-2024-10979/ > > > > > > Here it is

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On Sun, Nov 24, 2024 at 11:05 AM Subhash Udata wrote: > Dear PostgreSQL Community, > > I have a production database setup with a primary server and a standby > server. The database is currently running on *PostgreSQL 15.0*, and I > plan to upgrade both servers to *15.9*. > > I have the following

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On 11/24/24 08:36, Subhash Udata wrote: The reason to upgrade from 15.0 to 15.9 is this https://www.postgresql.org/support/security/CVE-2024-10979/ Here it is mentioned that this vulnerability is fixed in 15.9 So our organization wa

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

The reason to upgrade from 15.0 to 15.9 is this https://www.postgresql.org/support/security/CVE-2024-10979/ Here it is mentioned that this vulnerability is fixed in 15.9 So our organization wants an upgrade from 15.0 to 15.9 On Sun, 24 Nov 2024 at 21:48, Adrian Klaver wrote: > On 11/24/24 08:05

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

On 11/24/24 08:05, Subhash Udata wrote: Dear PostgreSQL Community, I have a production database setup with a primary server and a standby server. The database is currently running on *PostgreSQL 15.0*, and I plan to upgrade both servers to *15.9*. I have the following questions regarding the

Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

Dear PostgreSQL Community, I have a production database setup with a primary server and a standby server. The database is currently running on *PostgreSQL 15.0*, and I plan to upgrade both servers to *15.9*. I have the following questions regarding the upgrade and replication process: 1.

Re: setting up streaming replication, part 2

Hi, Le mer. 25 oct. 2023 à 02:29, Brad White a écrit : > I have the replication server set up and the streaming is working. > The latest data show up, as desired. > > 3 minor issues. > > 1) I also have the WAL files being copied to a common location from the > primary server with > archive

Re: setting up streaming replication, part 2

From: Ron Sent: Wednesday, October 25, 2023 4:35:59 AM To: pgsql-generallists.postgresql.org Subject: Re: setting up streaming replication, part 2 On 10/24/23 23:47, Brad White wrote: On Tue, Oct 24, 2023, 9:02 PM Ron mailto:ronljohnso...@gmail.com>> wrote: On 10/24/23 19:29, Brad White

Re: setting up streaming replication, part 2

On 10/24/23 23:47, Brad White wrote: On Tue, Oct 24, 2023, 9:02 PM Ron wrote: On 10/24/23 19:29, Brad White wrote: > I have the replication server set up and the streaming is working. > The latest data show up, as desired. > > 3 minor issues. > > 2) I have the conn

Re: setting up streaming replication, part 2

On Tue, Oct 24, 2023, 9:02 PM Ron wrote: > On 10/24/23 19:29, Brad White wrote: > > I have the replication server set up and the streaming is working. > > The latest data show up, as desired. > > > > 3 minor issues. > > > > > 2) I have the connection string set for the replication server to > con

Re: setting up streaming replication, part 2

On 10/24/23 19:29, Brad White wrote: I have the replication server set up and the streaming is working. The latest data show up, as desired. 3 minor issues. 1) I also have the WAL files being copied to a common location from the primary server with       archive_command = 'copy %p "DISKST

setting up streaming replication, part 2

I have the replication server set up and the streaming is working. The latest data show up, as desired. 3 minor issues. 1) I also have the WAL files being copied to a common location from the primary server with archive_command = 'copy %p "DISKSTATION\\AccessData\\Prod\\WALfiles\\%f"'

Re: setting up streaming replication

On 10/24/23 13:31, Brad White wrote: On Tue, Oct 24, 2023 at 8:56 AM Ron wrote: On 10/24/23 08:14, b55white wrote: Is all that stuff with slots necessary since the backup will automatically create a temporary slot for replication? I want a permanent slot so that replication auto

Re: setting up streaming replication

> On Oct 24, 2023, at 11:31, Brad White wrote: > Are you saying that once I get streaming replication set up, it quits working > when I reboot the servers once a week? Not unless the downtime is sufficiently long that the replica can't find the WAL information it needs. You can avoid this w

Re: setting up streaming replication

On Tue, Oct 24, 2023 at 8:56 AM Ron wrote: > On 10/24/23 08:14, b55white wrote: > > Is all that stuff with slots necessary since the backup will automatically > create a temporary slot for replication? > > > I want a permanent slot so that replication automatically resumes if the > secondary mus

Re: setting up streaming replication

On 10/24/23 08:14, b55white wrote: On Oct 23, 2023 at 8:30 PM, Ron wrote: On 10/23/23 18:16, Brad White wrote: > I'm stumped. > > Using this command to set up the slave and replication on PG v 15: > "C:\Program Files\PostgreSQL\15\bin\pg_basebackup" -h 192.168.1.126 -U > pgrep_user -p 5433 -D

Re: setting up streaming replication

On Monday, October 23, 2023, Brad White wrote: > I'm stumped. > > Using this command to set up the slave and replication on PG v 15: > > You must, by some means: Have PostgreSQL binaries installed on the machine that is to become the replica. Have the output of pg_basebackup present on the repli

Re: setting up streaming replication

> > On Oct 23, 2023 at 8:30 PM, Ronwrote: > > > On 10/23/23 18:16, Brad White wrote: > > I'm stumped. > > > > Using this command to set up the slave and replication on PG v 15: > > "C:\Program Files\PostgreSQL\15\bin\pg_basebackup" -h 192.168.1.126 -U > > pgrep_user -p 5433 -D

Re: setting up streaming replication

On 10/23/23 18:16, Brad White wrote: I'm stumped. Using this command to set up the slave and replication on PG v 15: "C:\Program Files\PostgreSQL\15\bin\pg_basebackup" -h 192.168.1.126 -U pgrep_user -p 5433 -D "C:\Program Files\PostgreSQL\15\data" -Fp -Xs -R If I have PG running on the remote

setting up streaming replication

I'm stumped. Using this command to set up the slave and replication on PG v 15: "C:\Program Files\PostgreSQL\15\bin\pg_basebackup" -h 192.168.1.126 -U pgrep_user -p 5433 -D "C:\Program Files\PostgreSQL\15\data" -Fp -Xs -R If I have PG running on the remote server and the data directory is intact,

Re: [(catch-ext)] Re: [(catch-ext)] Re: Setting up streaming replication on large database (20+ TB) for the first time

Yes, pgbackrest seems the best solution for faster backup and restore. We can close the discussion here for now, replaying million WAL files is just slow and this is normal. On Thu, Aug 18, 2022 at 12:10 PM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > Sorry for top posting, from p

Re: [(catch-ext)] Re: Setting up streaming replication on large database (20+ TB) for the first time

Sorry for top posting, from phone. But pgbackrest exactly helped with that. With compression and parallel process in backup, the backup and restore was quick. I used this, where I took a backup and immediately did a restore so less wals to replay, else wal replay is indeed slow. On Thu, Aug 18, 2

Re: [(catch-ext)] Re: Setting up streaming replication on large database (20+ TB) for the first time

Thank you, people. The big problem in my case, which I have not mentioned, is that I think the network is a bottleneck, because I am running pg_basebackup through internet from local country to Amazon instance in Germany and the speed in copying is around 50 MB/sec max, that is why it takes 2 days

Re: Setting up streaming replication on large database (20+ TB) for the first time

I just did a backup and restore of a replica using pgbackrest. db size 28tb nvme/ssd storage 96 cpu, 380 gb mem zst compression, 24 workers (backup, 12 workers restore) 2.5 hours to backup 2 hours to restore. Wal replay is something I forgot to tune, but I could now use https://pgbackrest.org/co

Re: Setting up streaming replication on large database (20+ TB) for the first time

pg_backrest will certainly backup your data faster. It might be able to be used as a seed instead of pg_basebackup. On 8/17/22 15:06, Ivan N. Ivanov wrote: I have a large database (~25 TB) and I want to set up streaming replication for the first time. My problem is that after completion of th

Re: [(catch-ext)] Re: Setting up streaming replication on large database (20+ TB) for the first time

Thank you for your answer! I have found this tool and I will try it tomorrow to see if this "read-ahead" feature will speed up the process. On Wed, Aug 17, 2022 at 11:09 PM Christophe Pettus wrote: > > > > On Aug 17, 2022, at 13:06, Ivan N. Ivanov > wrote: > > > > How to speed up recovering of

Re: Setting up streaming replication on large database (20+ TB) for the first time

> On Aug 17, 2022, at 13:06, Ivan N. Ivanov wrote: > > How to speed up recovering of WAL files? Since you are running on your own hardware, you might take a look at: https://github.com/TritonDataCenter/pg_prefaulter

Setting up streaming replication on large database (20+ TB) for the first time

I have a large database (~25 TB) and I want to set up streaming replication for the first time. My problem is that after completion of the pg_basebackup (which completed for 2 days with --wal-method=none) now PG is replaying the WAL files from the WAL archive directory but it can not keep up. The

Re: [solved] Setting up streaming replication problems

On 01/31/18 13:26, Thiemo Kellner wrote: master by pg_basebackup. This lead to the error of unequal identifier error. Maybe I was not reading carefully enough, however, I was not sure Addendum: The error shows up in the standby log. -- +49 (0)1578-772 37 37 +41 (0)78 947 36 21 SIP/iptel.org:

Re: [solved] Setting up streaming replication problems

Oh, I forgot again already. *shame on me* I tried to set up streaming replication without cloning standby from the master by pg_basebackup. This lead to the error of unequal identifier error. Maybe I was not reading carefully enough, however, I was not sure what to do with the created files an

Re: [solved] Setting up streaming replication problems

Andreas Kretschmer and others of the german mailing list put me on the right track again. My working config changes from standard is as listed below. My problem was, that application_name in primary_conninfo of the standby was missing. This lead the master not to recognise standby having taken

Re: FW: Setting up streaming replication problems

Hi, Am 29.01.2018 um 06:03 schrieb Thiemo Kellner: Thanks for your patience. you are welcome. From the other mail (in german) i think i know the issue now: you have placed the recovery.conf NOT in the data_directoy but in /etc/... PostgreSQL is looking for the recovery.conf in the data_d

Re: FW: Setting up streaming replication problems

Thanks for your patience. On 01/29/18 00:11, Martin Goodson wrote: "A password needs to be provided too, if the primary demands password authentication. It can be provided in the primary_conninfo string, or in a separate ~/.pgpass file on the standby server (use replication as the database name)

Re: FW: Setting up streaming replication problems

On 28/01/2018 22:24, Thiemo Kellner wrote: Me again. Hope you wont feel to bothered by me. I just summarise so far and am still in dire need of guidance. I am not sure whether to put the md5 value of the repuser password into primary_conninfo or the plain one. I don't feel the documentation or

Re: FW: Setting up streaming replication problems

Me again. Hope you wont feel to bothered by me. I just summarise so far and am still in dire need of guidance. Debian 9 with PostreSQL 10. I have two nodes in the cluster I use as master and as standby. I tried to setup replication with Rigg's book and the official documentation and a couple o

Re: FW: Setting up streaming replication problems

Do you mind me bumping the issue? Zitat von "Thiemo Kellner, NHC Barhufpflege" : Andreas, thanks for your reply. I try to set up synchronous streaming replication as try-out. I use my laptop with Debian 9 and PostgreSQL package 10+189.pgdg90+1. And of this PostgreSQL installation I have tw

Re: FW: Setting up streaming replication problems

On 23/01/2018 18:08, Pavan Teja wrote: On Jan 23, 2018 11:34 PM, "Martin Goodson" > wrote: On 23/01/2018 07:36, Thiemo Kellner, NHC Barhufpflege wrote: 1) I am not sure whether to put the md5 value of the repuser password

Re: FW: Setting up streaming replication problems

On Jan 23, 2018 11:34 PM, "Martin Goodson" wrote: On 23/01/2018 07:36, Thiemo Kellner, NHC Barhufpflege wrote: > 1) I am not sure whether to put the md5 value of the repuser password > >> into primary conninfo or the plain one. I don't feel the documentation >>> or the book is clear on that. >

Re: FW: Setting up streaming replication problems

On 23/01/2018 07:36, Thiemo Kellner, NHC Barhufpflege wrote: 1) I am not sure whether to put the md5 value of the repuser password into primary conninfo or the plain one. I don't feel the documentation or the book is clear on that. Anyone two dimes on that? Password, not an md5. Or, at leas

Re: FW: Setting up streaming replication problems

Andreas, thanks for your reply. I try to set up synchronous streaming replication as try-out. I use my laptop with Debian 9 and PostgreSQL package 10+189.pgdg90+1. And of this PostgreSQL installation I have two clusters main (master) and main2 (hot standby). I tried with Rigg's book and the Post

Re: Setting up streaming replication problems

Am 22.01.2018 um 07:39 schrieb Thiemo Kellner: Hi all I try to set up synchronous streaming replication as try-out. I use my laptop with Debian 9 and PostgreSQL package 10+189.pgdg90+1. And of this PostgreSQL installation I have two clusters main (master) and main2 (hot standby). I tried wi

Setting up streaming replication problems

Hi all I try to set up synchronous streaming replication as try-out. I use my laptop with Debian 9 and PostgreSQL package 10+189.pgdg90+1. And of this PostgreSQL installation I have two clusters main (master) and main2 (hot standby). I tried with Rigg's book and the PostgreSQL documentation an