PostgreSQL V13 Replication Issue
Hello Everyone, Apologies in advance I don't know where to get knowledge regarding this issue that's why I posted here, Highly appreciated if someone could help on this regard We have installed and configured PostgreSQL V13 Master- Slave streaming replication on Windows server 2016 but replication is not working. I don't know where the error is because we are not facing any error regarding replication and replication also not working. Below are the Configuration steps performed on Master and Slave Server *Master Server postgresql.conf file changes made* listen_addresses = '*' wal_level = replica wal_writer_delay = 500ms archive_mode = on archive_command ='copy %p \\server IP\wal_archive\%f"' archive_timeout = 3600 max_wal_senders = 6 max_replication_slots = 6 *pg_hba Master Server File* host replication username slave_ip/32 md5 host replication username master_ip/32 md5 *Below are the Configuration steps performed on Slave Server* *Note:* I have copied Data directory from master server and paste it on Slave server *Slave Server postgresql.conf file changes made* listen_addresses = '*' wal_level = replica wal_writer_delay = 500ms #archive_mode = off max_wal_senders = 6 hot_standby = on *Slave pg_hba.conf file* host replication username slave_ip/32 md5 host replication username master_ip/32 md5 *recovery.conf file* standby_mode = 'on' restore_command= 'copy "C:\wal_archive\%f" %p' = 'host= port=5432 user=replicator password=*' After all these changes made when I restart the master and slave server and run the following command on master server to check whether replication is working or not the below mention query return 0 row *Master Server* select * from pg_stat_replication" (return 0 row) Would be highly appreciated, if someone could tell me what exactly the issue is or what I am missing in the configuration on both the servers (master- slave).Why replication is not working? *BR,* Haseeb Ahmad
Re: PostgreSQL V13 Replication Issue
I'm no expert, but it looks like you are reading v9.6 documentation for a v13 installation. On 2021-06-16 09:25, Haseeb Khan wrote: ... hot_standby = on ... *recovery.conf file* standby_mode = 'on'
Estimating wal_keep_size
Is this reasonable thinking? I'd think that one would want a *wal_keep_size* to cover the pending updates while the standby server might be unavailable, however long one might anticipate that would be. In my case, I get a complete replacement (in the form of "|"- delimited ASCII files) of one of the SCHEMAs every Sunday. The size of that ASCII data is about 2GB, so I'm thinking of doubling that to 4GB (256 WAL files) to protect me in the case of the standby being unavailable during the update. Note that a complete loss of both servers is not catastrophic (I have backups); it would just be annoying.
Re: Estimating wal_keep_size
On Wed, Jun 16, 2021 at 05:36:24PM -0700, Dean Gibson (DB Administrator) wrote: > Is this reasonable thinking? > > I'd think that one would want a *wal_keep_size* to cover the pending updates > while the standby server might be unavailable, however long one might > anticipate that would be. It's usually a better approach to use a replication slot, to keep all the required WAL files, and only when needed. See https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS for more details. Note that a replication slot will keep all WAL files, which might eventually lead to an outage if the standby doesn't come back before the filesystem containing the logs get full. You can cap the maximum amount of retained WAL filed since pg 13 using max_slot_wal_keep_size, see https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE.
Re: PostgreSQL V13 Replication Issue
On Wed, Jun 16, 2021 at 09:25:37PM +0500, Haseeb Khan wrote: > Would be highly appreciated, if someone could tell me what exactly the > issue is or what I am missing in the configuration on both the servers > (master- slave).Why replication is not working? Your issue is partially here, as recovery.conf is not supported in 13: > *recovery.conf file* I would recommend to read this area of the documentation first: https://www.postgresql.org/docs/13/warm-standby.html#STANDBY-SERVER-SETUP All the recovery parameters have been moved to postgresql.conf, and setting up a standby requires an extra file called standby.signal created at the root of the data folder. -- Michael signature.asc Description: PGP signature
