patternsqlMinor
PostgreSQL streaming replication with slots works for one slave, but not for another slave
Viewed 0 times
postgresqlwithslavebutreplicationslotsworksstreamingforone
Problem
Master (10.0.0.1):
Edit postgresql.conf
Edit pg_hba.conf
Create a replication slot for the slave
Slave #1 (10.0.0.2):
Remove 9.4/main directory
Do a base backup of the master
Edit recovery.conf
Copy conf files from master
Restart cluster
Slave #2 (10.0.0.3):
Same steps as for slave #1 except the ip of the slave is different.
The problem: A change on the master propagates the change onto slave #1 but not slave #2. Tried rebooting slave #2 and setting up again from a fresh install but still doesn't work. Any ideas?
Thanks!
Edit postgresql.conf
listen_addresses = '*'
wal_level = logical
max_wal_senders = 3
max_replication_slots = 3
hot_standby = onEdit pg_hba.conf
host replication postgres 0.0.0.0/0 trustCreate a replication slot for the slave
select * from pg_create_physical_replication_slot('master');Slave #1 (10.0.0.2):
Remove 9.4/main directory
rm -rf /var/lib/postgresql/9.4/mainDo a base backup of the master
pg_basebackup -P -R -X stream -c fast -h 10.0.0.1 -U postgres -D /var/lib/postgresql/9.4/mainEdit recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=postgres host=10.0.0.1 sslmode=prefer sslcompression=1 krbsrvname=postgres'
primary_slot_name = 'master'Copy conf files from master
rsync -av user@10.0.0.1:/etc/postgresql/ /etc/postgresql/Restart cluster
pg_ctlcluster 9.4 main restartSlave #2 (10.0.0.3):
Same steps as for slave #1 except the ip of the slave is different.
The problem: A change on the master propagates the change onto slave #1 but not slave #2. Tried rebooting slave #2 and setting up again from a fresh install but still doesn't work. Any ideas?
Thanks!
Solution
Community Wiki answer generated from a comment left by the question author. Feel free to edit to improve
Nevermind, I fixed it. There needs to be one replication slot per slave. I added another replication slot "master2". So slave #1 is using the "master" replication slot and slave #2 is using the "master2" replication slot. Works fine now.
Nevermind, I fixed it. There needs to be one replication slot per slave. I added another replication slot "master2". So slave #1 is using the "master" replication slot and slave #2 is using the "master2" replication slot. Works fine now.
Context
StackExchange Database Administrators Q#119026, answer score: 3
Revisions (0)
No revisions yet.