Обсуждение: Broken replication in 3 node setup, master halt, standby 1 promoted,standby 2 - replication not working
Hi,
I am trying to test the failover scenario on a brand new 3 node setup.
pg1 is master
pg2 and pg3 are standby hosts in streaming replication, both following pg1.
When I halt pg1, promote pg2 and update recovery.conf on pg3 to point to pg2, the replication is broken. Can you please help me identify the correct procedure to achieve this failover?
Here are the details:
Software Versions:
PostgreSQL: 9.6
Ubuntu 16.04
PgBackRest 1.25
Here is the scenario:
Set up 3 nodes in streaming replication:
master (pg1), standby 1 (pg2), standby 2 (pg3)
Also, I have set up pgbackrest, to back up from standby. It's working correctly.
Run the following on pg1:
sudo -u postgres psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid | 20714
usesysid | 16384
usename | replicator
application_name | walreceiver
client_addr | 10.0.15.11
client_hostname |
client_port | 48640
backend_start | 2018-01-10 18:17:46.105694+00
backend_xmin |
state | streaming
sent_location | 0/6000060
write_location | 0/6000060
flush_location | 0/6000060
replay_location | 0/6000060
sync_priority | 0
sync_state | async
-[ RECORD 2 ]----+------------------------------
pid | 20715
usesysid | 16384
usename | replicator
application_name | walreceiver
client_addr | 10.0.15.12
client_hostname |
client_port | 42198
backend_start | 2018-01-10 18:17:48.712575+00
backend_xmin |
state | streaming
sent_location | 0/6000060
write_location | 0/6000060
flush_location | 0/6000060
replay_location | 0/6000060
sync_priority | 0
sync_state | async
It reports both the standbys working correctly.
Halt pg1.
On pg2:
sudo -u postgres pg_ctlcluster 9.6 main promote
On pg3:
sudo service postgresql stop
sudo -u postgres vim /var/lib/postgresql/9.6/main/recovery.conf
modify it's contents are as follows:
```
primary_conninfo = 'host=pg2 port=5432 user=replicator'
standby_mode = 'on'
restore_command = '/usr/bin/pgbackrest --stanza=main archive-get %f "%p"'
```
sudo service postgresql start
On pg2:
sudo -u postgres psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid | 21238
usesysid | 16384
usename | replicator
application_name | walreceiver
client_addr | 10.0.15.12
client_hostname |
client_port | 44852
backend_start | 2018-01-10 18:22:20.980701+00
backend_xmin |
state | startup
sent_location |
write_location |
flush_location |
replay_location |
sync_priority | 0
sync_state | async
As you can see sent_location, write_location, flush_location all are empty.
On pg3:
tail -f /var/log/postgresql/postgresql-9.6-main.log
2018-01-10 18:22:19.490 UTC [21229] LOG: restored log file "000000010000000000000005" from archive
2018-01-10 18:22:19.497 UTC [21229] LOG: redo starts at 0/5000028
2018-01-10 18:22:19.757 UTC [21243] postgres@postgres FATAL: the database system is starting up
2018-01-10 18:22:20.188 UTC [21229] LOG: restored log file "000000010000000000000006" from archive
2018-01-10 18:22:20.195 UTC [21229] LOG: consistent recovery state reached at 0/7000000
2018-01-10 18:22:20.195 UTC [21228] LOG: database system is ready to accept read only connections
2018-01-10 18:22:20.812 UTC [21229] LOG: invalid magic number 0000 in log segment 000000010000000000000007, offset 0
2018-01-10 18:22:20.824 UTC [21255] LOG: fetching timeline history file for timeline 2 from primary server
2018-01-10 18:22:20.828 UTC [21255] LOG: primary server contains no more WAL on requested timeline 1
2018-01-10 18:22:21.417 UTC [21255] LOG: primary server contains no more WAL on requested timeline 1
At this point is I insert data in pg2, it doesn't reflect in pg3.
Please help.
Regards,
Pritam.
Hi Pritam, On 1/10/18 2:06 PM, Pritam Barhate wrote: <...> > > It reports both the standbys working correctly. > > Halt pg1. > > On pg2: > > sudo -u postgres pg_ctlcluster 9.6 main promote > > On pg3: > > sudo service postgresql stop > > sudo -u postgres vim /var/lib/postgresql/9.6/main/recovery.conf > > modify it's contents are as follows: > > ``` > primary_conninfo = 'host=pg2 port=5432 user=replicator' > standby_mode = 'on' > restore_command = '/usr/bin/pgbackrest --stanza=main archive-get %f "%p"' > ``` This all looks fine. I'm not sure why (without testing the configuration), but the switch to timeline 2 switch is not happening. Try setting: recovery_target_timeline=latest in recovery.conf on pg3. It's possible that pg3 played past the time when timeline 2 diverged on pg2 (i.e. pg3 was more up to date), in which case you will need to restore pg3 from a backup and set the recovery_target_timeline as above. -- -David david@pgmasters.net
It appears that your missing is following in the recovery.conf file. It instructs the replica to follow a new timeline whenpresented; without it will refuse to follow the promoted node. recovery_target_timeline=latest Are you using sync replication? > On Jan 10, 2018, at 2:06 PM, Pritam Barhate <pritambarhate@gmail.com> wrote: > > Hi, > > I am trying to test the failover scenario on a brand new 3 node setup. > > pg1 is master > pg2 and pg3 are standby hosts in streaming replication, both following pg1. > > When I halt pg1, promote pg2 and update recovery.conf on pg3 to point to pg2, the replication is broken. Can you pleasehelp me identify the correct procedure to achieve this failover? > > Here are the details: > > Software Versions: > > PostgreSQL: 9.6 > Ubuntu 16.04 > PgBackRest 1.25 > > Here is the scenario: > > Set up 3 nodes in streaming replication: > > master (pg1), standby 1 (pg2), standby 2 (pg3) > > Also, I have set up pgbackrest, to back up from standby. It's working correctly. > > Run the following on pg1: > > sudo -u postgres psql -x -c "select * from pg_stat_replication;" > > -[ RECORD 1 ]----+------------------------------ > pid | 20714 > usesysid | 16384 > usename | replicator > application_name | walreceiver > client_addr | 10.0.15.11 > client_hostname | > client_port | 48640 > backend_start | 2018-01-10 18:17:46.105694+00 > backend_xmin | > state | streaming > sent_location | 0/6000060 > write_location | 0/6000060 > flush_location | 0/6000060 > replay_location | 0/6000060 > sync_priority | 0 > sync_state | async > -[ RECORD 2 ]----+------------------------------ > pid | 20715 > usesysid | 16384 > usename | replicator > application_name | walreceiver > client_addr | 10.0.15.12 > client_hostname | > client_port | 42198 > backend_start | 2018-01-10 18:17:48.712575+00 > backend_xmin | > state | streaming > sent_location | 0/6000060 > write_location | 0/6000060 > flush_location | 0/6000060 > replay_location | 0/6000060 > sync_priority | 0 > sync_state | async > > > It reports both the standbys working correctly. > > Halt pg1. > > On pg2: > > sudo -u postgres pg_ctlcluster 9.6 main promote > > On pg3: > > sudo service postgresql stop > > sudo -u postgres vim /var/lib/postgresql/9.6/main/recovery.conf > > modify it's contents are as follows: > > ``` > primary_conninfo = 'host=pg2 port=5432 user=replicator' > standby_mode = 'on' > restore_command = '/usr/bin/pgbackrest --stanza=main archive-get %f "%p"' > ``` > > sudo service postgresql start > > On pg2: > > sudo -u postgres psql -x -c "select * from pg_stat_replication;" > > -[ RECORD 1 ]----+------------------------------ > pid | 21238 > usesysid | 16384 > usename | replicator > application_name | walreceiver > client_addr | 10.0.15.12 > client_hostname | > client_port | 44852 > backend_start | 2018-01-10 18:22:20.980701+00 > backend_xmin | > state | startup > sent_location | > write_location | > flush_location | > replay_location | > sync_priority | 0 > sync_state | async > > As you can see sent_location, write_location, flush_location all are empty. > > On pg3: > > tail -f /var/log/postgresql/postgresql-9.6-main.log > 2018-01-10 18:22:19.490 UTC [21229] LOG: restored log file "000000010000000000000005" from archive > 2018-01-10 18:22:19.497 UTC [21229] LOG: redo starts at 0/5000028 > 2018-01-10 18:22:19.757 UTC [21243] postgres@postgres FATAL: the database system is starting up > 2018-01-10 18:22:20.188 UTC [21229] LOG: restored log file "000000010000000000000006" from archive > 2018-01-10 18:22:20.195 UTC [21229] LOG: consistent recovery state reached at 0/7000000 > 2018-01-10 18:22:20.195 UTC [21228] LOG: database system is ready to accept read only connections > 2018-01-10 18:22:20.812 UTC [21229] LOG: invalid magic number 0000 in log segment 000000010000000000000007, offset 0 > 2018-01-10 18:22:20.824 UTC [21255] LOG: fetching timeline history file for timeline 2 from primary server > 2018-01-10 18:22:20.828 UTC [21255] LOG: primary server contains no more WAL on requested timeline 1 > 2018-01-10 18:22:21.417 UTC [21255] LOG: primary server contains no more WAL on requested timeline 1 > > > At this point is I insert data in pg2, it doesn't reflect in pg3. > > Please help. > > Regards, > > Pritam. >
I am using async replication.
As both of you said:
recovery_target_timeline= latest
Was missing. Adding it to the recovery.conf did the trick.
Thanks a lot.
Regards,
Pritam.
On Thu, Jan 11, 2018 at 4:09 AM, Rui DeSousa <rui.desousa@icloud.com> wrote:
It appears that your missing is following in the recovery.conf file. It instructs the replica to follow a new timeline when presented; without it will refuse to follow the promoted node.
recovery_target_timeline=latest
Are you using sync replication?
> On Jan 10, 2018, at 2:06 PM, Pritam Barhate <pritambarhate@gmail.com> wrote:
>
> Hi,
>
> I am trying to test the failover scenario on a brand new 3 node setup.
>
> pg1 is master
> pg2 and pg3 are standby hosts in streaming replication, both following pg1.
>
> When I halt pg1, promote pg2 and update recovery.conf on pg3 to point to pg2, the replication is broken. Can you please help me identify the correct procedure to achieve this failover?
>
> Here are the details:
>
> Software Versions:
>
> PostgreSQL: 9.6
> Ubuntu 16.04
> PgBackRest 1.25
>
> Here is the scenario:
>
> Set up 3 nodes in streaming replication:
>
> master (pg1), standby 1 (pg2), standby 2 (pg3)
>
> Also, I have set up pgbackrest, to back up from standby. It's working correctly.
>
> Run the following on pg1:
>
> sudo -u postgres psql -x -c "select * from pg_stat_replication;"
>
> -[ RECORD 1 ]----+------------------------------
> pid | 20714
> usesysid | 16384
> usename | replicator
> application_name | walreceiver
> client_addr | 10.0.15.11
> client_hostname |
> client_port | 48640
> backend_start | 2018-01-10 18:17:46.105694+00
> backend_xmin |
> state | streaming
> sent_location | 0/6000060
> write_location | 0/6000060
> flush_location | 0/6000060
> replay_location | 0/6000060
> sync_priority | 0
> sync_state | async
> -[ RECORD 2 ]----+------------------------------
> pid | 20715
> usesysid | 16384
> usename | replicator
> application_name | walreceiver
> client_addr | 10.0.15.12
> client_hostname |
> client_port | 42198
> backend_start | 2018-01-10 18:17:48.712575+00
> backend_xmin |
> state | streaming
> sent_location | 0/6000060
> write_location | 0/6000060
> flush_location | 0/6000060
> replay_location | 0/6000060
> sync_priority | 0
> sync_state | async
>
>
> It reports both the standbys working correctly.
>
> Halt pg1.
>
> On pg2:
>
> sudo -u postgres pg_ctlcluster 9.6 main promote
>
> On pg3:
>
> sudo service postgresql stop
>
> sudo -u postgres vim /var/lib/postgresql/9.6/main/recovery.conf
>
> modify it's contents are as follows:
>
> ```
> primary_conninfo = 'host=pg2 port=5432 user=replicator'
> standby_mode = 'on'
> restore_command = '/usr/bin/pgbackrest --stanza=main archive-get %f "%p"'
> ```
>
> sudo service postgresql start
>
> On pg2:
>
> sudo -u postgres psql -x -c "select * from pg_stat_replication;"
>
> -[ RECORD 1 ]----+------------------------------
> pid | 21238
> usesysid | 16384
> usename | replicator
> application_name | walreceiver
> client_addr | 10.0.15.12
> client_hostname |
> client_port | 44852
> backend_start | 2018-01-10 18:22:20.980701+00
> backend_xmin |
> state | startup
> sent_location |
> write_location |
> flush_location |
> replay_location |
> sync_priority | 0
> sync_state | async
>
> As you can see sent_location, write_location, flush_location all are empty.
>
> On pg3:
>
> tail -f /var/log/postgresql/postgresql-9.6-main.log
> 2018-01-10 18:22:19.490 UTC [21229] LOG: restored log file "000000010000000000000005" from archive
> 2018-01-10 18:22:19.497 UTC [21229] LOG: redo starts at 0/5000028
> 2018-01-10 18:22:19.757 UTC [21243] postgres@postgres FATAL: the database system is starting up
> 2018-01-10 18:22:20.188 UTC [21229] LOG: restored log file "000000010000000000000006" from archive
> 2018-01-10 18:22:20.195 UTC [21229] LOG: consistent recovery state reached at 0/7000000
> 2018-01-10 18:22:20.195 UTC [21228] LOG: database system is ready to accept read only connections
> 2018-01-10 18:22:20.812 UTC [21229] LOG: invalid magic number 0000 in log segment 000000010000000000000007, offset 0
> 2018-01-10 18:22:20.824 UTC [21255] LOG: fetching timeline history file for timeline 2 from primary server
> 2018-01-10 18:22:20.828 UTC [21255] LOG: primary server contains no more WAL on requested timeline 1
> 2018-01-10 18:22:21.417 UTC [21255] LOG: primary server contains no more WAL on requested timeline 1
>
>
> At this point is I insert data in pg2, it doesn't reflect in pg3.
>
> Please help.
>
> Regards,
>
> Pritam.
>
You might want to consider sync replication instead to avoid split brain syndrome. It readily depending on how things are promoted and managed.
On Jan 12, 2018, at 10:50 AM, Pritam Barhate <pritambarhate@gmail.com> wrote:I am using async replication.As both of you said:recovery_target_timeline=latest Was missing. Adding it to the recovery.conf did the trick.Thanks a lot.Regards,Pritam.On Thu, Jan 11, 2018 at 4:09 AM, Rui DeSousa <rui.desousa@icloud.com> wrote:It appears that your missing is following in the recovery.conf file. It instructs the replica to follow a new timeline when presented; without it will refuse to follow the promoted node.
recovery_target_timeline=latest
Are you using sync replication?
> On Jan 10, 2018, at 2:06 PM, Pritam Barhate <pritambarhate@gmail.com> wrote:
>
> Hi,
>
> I am trying to test the failover scenario on a brand new 3 node setup.
>
> pg1 is master
> pg2 and pg3 are standby hosts in streaming replication, both following pg1.
>
> When I halt pg1, promote pg2 and update recovery.conf on pg3 to point to pg2, the replication is broken. Can you please help me identify the correct procedure to achieve this failover?
>
> Here are the details:
>
> Software Versions:
>
> PostgreSQL: 9.6
> Ubuntu 16.04
> PgBackRest 1.25
>
> Here is the scenario:
>
> Set up 3 nodes in streaming replication:
>
> master (pg1), standby 1 (pg2), standby 2 (pg3)
>
> Also, I have set up pgbackrest, to back up from standby. It's working correctly.
>
> Run the following on pg1:
>
> sudo -u postgres psql -x -c "select * from pg_stat_replication;"
>
> -[ RECORD 1 ]----+------------------------------
> pid | 20714
> usesysid | 16384
> usename | replicator
> application_name | walreceiver
> client_addr | 10.0.15.11
> client_hostname |
> client_port | 48640
> backend_start | 2018-01-10 18:17:46.105694+00
> backend_xmin |
> state | streaming
> sent_location | 0/6000060
> write_location | 0/6000060
> flush_location | 0/6000060
> replay_location | 0/6000060
> sync_priority | 0
> sync_state | async
> -[ RECORD 2 ]----+------------------------------
> pid | 20715
> usesysid | 16384
> usename | replicator
> application_name | walreceiver
> client_addr | 10.0.15.12
> client_hostname |
> client_port | 42198
> backend_start | 2018-01-10 18:17:48.712575+00
> backend_xmin |
> state | streaming
> sent_location | 0/6000060
> write_location | 0/6000060
> flush_location | 0/6000060
> replay_location | 0/6000060
> sync_priority | 0
> sync_state | async
>
>
> It reports both the standbys working correctly.
>
> Halt pg1.
>
> On pg2:
>
> sudo -u postgres pg_ctlcluster 9.6 main promote
>
> On pg3:
>
> sudo service postgresql stop
>
> sudo -u postgres vim /var/lib/postgresql/9.6/main/recovery.conf
>
> modify it's contents are as follows:
>
> ```
> primary_conninfo = 'host=pg2 port=5432 user=replicator'
> standby_mode = 'on'
> restore_command = '/usr/bin/pgbackrest --stanza=main archive-get %f "%p"'
> ```
>
> sudo service postgresql start
>
> On pg2:
>
> sudo -u postgres psql -x -c "select * from pg_stat_replication;"
>
> -[ RECORD 1 ]----+------------------------------
> pid | 21238
> usesysid | 16384
> usename | replicator
> application_name | walreceiver
> client_addr | 10.0.15.12
> client_hostname |
> client_port | 44852
> backend_start | 2018-01-10 18:22:20.980701+00
> backend_xmin |
> state | startup
> sent_location |
> write_location |
> flush_location |
> replay_location |
> sync_priority | 0
> sync_state | async
>
> As you can see sent_location, write_location, flush_location all are empty.
>
> On pg3:
>
> tail -f /var/log/postgresql/postgresql-9.6-main.log
> 2018-01-10 18:22:19.490 UTC [21229] LOG: restored log file "000000010000000000000005" from archive
> 2018-01-10 18:22:19.497 UTC [21229] LOG: redo starts at 0/5000028
> 2018-01-10 18:22:19.757 UTC [21243] postgres@postgres FATAL: the database system is starting up
> 2018-01-10 18:22:20.188 UTC [21229] LOG: restored log file "000000010000000000000006" from archive
> 2018-01-10 18:22:20.195 UTC [21229] LOG: consistent recovery state reached at 0/7000000
> 2018-01-10 18:22:20.195 UTC [21228] LOG: database system is ready to accept read only connections
> 2018-01-10 18:22:20.812 UTC [21229] LOG: invalid magic number 0000 in log segment 000000010000000000000007, offset 0
> 2018-01-10 18:22:20.824 UTC [21255] LOG: fetching timeline history file for timeline 2 from primary server
> 2018-01-10 18:22:20.828 UTC [21255] LOG: primary server contains no more WAL on requested timeline 1
> 2018-01-10 18:22:21.417 UTC [21255] LOG: primary server contains no more WAL on requested timeline 1
>
>
> At this point is I insert data in pg2, it doesn't reflect in pg3.
>
> Please help.
>
> Regards,
>
> Pritam.
>
Hi Pritam Barhate,
Please check after promoting pg2 as master pg3 is up and running.
Provide Ip address of pg2 in recovery.conf of pg3 instead hostname and pass recovery_target_timeline='latest'
Provide Ip address of pg2 in recovery.conf of pg3 instead hostname and pass recovery_target_timeline='latest'
Also check archive_command in postgresql.conf of pg2 and restore_command in recovery.conf of pg3 is identical. and permission for pgbackrest.
Hope this helps..
Best Regards,
Shreeyansh
On Thu, Jan 11, 2018 at 12:36 AM, Pritam Barhate <pritambarhate@gmail.com> wrote:
Hi,I am trying to test the failover scenario on a brand new 3 node setup.pg1 is masterpg2 and pg3 are standby hosts in streaming replication, both following pg1.When I halt pg1, promote pg2 and update recovery.conf on pg3 to point to pg2, the replication is broken. Can you please help me identify the correct procedure to achieve this failover?Here are the details:Software Versions:PostgreSQL: 9.6Ubuntu 16.04PgBackRest 1.25Here is the scenario:Set up 3 nodes in streaming replication:master (pg1), standby 1 (pg2), standby 2 (pg3)Also, I have set up pgbackrest, to back up from standby. It's working correctly.Run the following on pg1:sudo -u postgres psql -x -c "select * from pg_stat_replication;"-[ RECORD 1 ]----+------------------------------ pid | 20714usesysid | 16384usename | replicatorapplication_name | walreceiverclient_addr | 10.0.15.11client_hostname |client_port | 48640backend_start | 2018-01-10 18:17:46.105694+00backend_xmin |state | streamingsent_location | 0/6000060write_location | 0/6000060flush_location | 0/6000060replay_location | 0/6000060sync_priority | 0sync_state | async-[ RECORD 2 ]----+------------------------------ pid | 20715usesysid | 16384usename | replicatorapplication_name | walreceiverclient_addr | 10.0.15.12client_hostname |client_port | 42198backend_start | 2018-01-10 18:17:48.712575+00backend_xmin |state | streamingsent_location | 0/6000060write_location | 0/6000060flush_location | 0/6000060replay_location | 0/6000060sync_priority | 0sync_state | asyncIt reports both the standbys working correctly.Halt pg1.On pg2:sudo -u postgres pg_ctlcluster 9.6 main promoteOn pg3:sudo service postgresql stopsudo -u postgres vim /var/lib/postgresql/9.6/main/recovery.conf modify it's contents are as follows:```primary_conninfo = 'host=pg2 port=5432 user=replicator'standby_mode = 'on'restore_command = '/usr/bin/pgbackrest --stanza=main archive-get %f "%p"'```sudo service postgresql startOn pg2:sudo -u postgres psql -x -c "select * from pg_stat_replication;"-[ RECORD 1 ]----+------------------------------ pid | 21238usesysid | 16384usename | replicatorapplication_name | walreceiverclient_addr | 10.0.15.12client_hostname |client_port | 44852backend_start | 2018-01-10 18:22:20.980701+00backend_xmin |state | startupsent_location |write_location |flush_location |replay_location |sync_priority | 0sync_state | asyncAs you can see sent_location, write_location, flush_location all are empty.On pg3:tail -f /var/log/postgresql/postgresql-9.6-main.log 2018-01-10 18:22:19.490 UTC [21229] LOG: restored log file "000000010000000000000005" from archive2018-01-10 18:22:19.497 UTC [21229] LOG: redo starts at 0/50000282018-01-10 18:22:19.757 UTC [21243] postgres@postgres FATAL: the database system is starting up2018-01-10 18:22:20.188 UTC [21229] LOG: restored log file "000000010000000000000006" from archive2018-01-10 18:22:20.195 UTC [21229] LOG: consistent recovery state reached at 0/70000002018-01-10 18:22:20.195 UTC [21228] LOG: database system is ready to accept read only connections2018-01-10 18:22:20.812 UTC [21229] LOG: invalid magic number 0000 in log segment 000000010000000000000007, offset 02018-01-10 18:22:20.824 UTC [21255] LOG: fetching timeline history file for timeline 2 from primary server2018-01-10 18:22:20.828 UTC [21255] LOG: primary server contains no more WAL on requested timeline 12018-01-10 18:22:21.417 UTC [21255] LOG: primary server contains no more WAL on requested timeline 1At this point is I insert data in pg2, it doesn't reflect in pg3.Please help.Regards,Pritam.
--