Home > mailing lists

Thread: Resync second slave to new master

Resync second slave to new master

From

Dylan Luong

Date:

06 March 2018, 10:45:10

We are currently on PostgreSQL 9.6 and our HA is configured as master/slave with wal streaming replication.

We would like to setup a second slave which replicates from the same master.

Eg:

Current setup:

Slave ß Master

Slave is setup for automatic failover and we use pg_rewind after failover to rewind the old master as new slave.

New setup:

Slave1 ß Master à Slave2

Slave 2 (new) is for reporting and nightly pg_dumps, as we have some very big databases.

Slave 1 is for HA and is setup automatic failover.

After a failover (promote) to the Slave1, is it easily resync the Slave2 to the new master (old slave1)? Do we need to do full rebuild of the Slave2 from new master everytime we failover to Slave1 from Master? Can we use pg_rewind on Slave2 to resyn it with new master (old slave1)?

Thanks

Dylan

Re: Resync second slave to new master

From

Michael Paquier

Date:

06 March 2018, 11:23:41

On Tue, Mar 06, 2018 at 04:45:10AM +0000, Dylan Luong wrote:
> After a failover (promote) to the Slave1,  is it easily resync the
> Slave2 to the new master (old slave1)? Do we need to do  full rebuild
> of the Slave2 from new master everytime we failover to Slave1 from
> Master? Can we use pg_rewind on Slave2 to resyn it with new master
> (old slave1)?

After promoting slave 1, it could be possible that some records have
slipped to slave 2 from the primary.  In this case, a rewind would be
recommended.  You should be careful that your slave 2 has not received
WAL to a position newer than where WAL has forked because of the
promotion.  If that happened, then a rewind would be necessary before
replugging slave 2 to the newly-promoted server.  Be very careful with
your failover flow here.  pg_rewind also would not run if it finds that
the target server does not need a rewind, so you could stop the slave 2,
and run pg_rewind unconditionally to keep things simple.
--
Michael

Attachment

signature.asc

RE: Resync second slave to new master

From

Dylan Luong

Date:

06 March 2018, 12:00:40

Thanks Michael,
So everytime after promoting Slave to become master (either manually or automatic), just stop Slave2 and run pg_rewind
onslave2 against the new maser (old slave1). And when old master server is available again, use pg_rewind on that serve
aswell against new master to return to original configuration. 

-----Original Message-----
From: Michael Paquier [mailto:michael@paquier.xyz]
Sent: Tuesday, 6 March 2018 3:54 PM
To: Dylan Luong <Dylan.Luong@unisa.edu.au>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Resync second slave to new master

On Tue, Mar 06, 2018 at 04:45:10AM +0000, Dylan Luong wrote:
> After a failover (promote) to the Slave1,  is it easily resync the
> Slave2 to the new master (old slave1)? Do we need to do  full rebuild
> of the Slave2 from new master everytime we failover to Slave1 from
> Master? Can we use pg_rewind on Slave2 to resyn it with new master
> (old slave1)?

After promoting slave 1, it could be possible that some records have slipped to slave 2 from the primary.  In this
case,a rewind would be recommended.  You should be careful that your slave 2 has not received WAL to a position newer
thanwhere WAL has forked because of the promotion.  If that happened, then a rewind would be necessary before
repluggingslave 2 to the newly-promoted server.  Be very careful with your failover flow here.  pg_rewind also would
notrun if it finds that the target server does not need a rewind, so you could stop the slave 2, and run pg_rewind
unconditionallyto keep things simple. 
--
Michael

Re: Resync second slave to new master

From

Michael Paquier

Date:

06 March 2018, 13:24:46

On Tue, Mar 06, 2018 at 06:00:40AM +0000, Dylan Luong wrote:
> So everytime after promoting Slave to become master (either manually
> or automatic), just stop Slave2 and run pg_rewind on slave2 against
> the new maser (old slave1). And when old master server is available
> again, use pg_rewind on that serve as well against new master to
> return to original configuration.

Yes.  That's exactly the idea.  Running pg_rewind on the old master will
be necessary anyway because you need to stop it cleanly once, which will
cause it to generate WAL records at least for the shutdown checkpoint,
while doing it on slave 2 may be optional, still safer to do.
--
Michael

Attachment

signature.asc

RE: Resync second slave to new master

From

Dylan Luong

Date:

08 March 2018, 13:41:31

Hi Michael,

I tested the failover today and the slave 2 failed to resync with the new master (old slave1).

After I promoted the slave1 to become master,  I was able to use pg_rewind on the old master and bring it back as new
slave.

I then stopped slave2 and ran pg_rewind on slave2 against new master, it report that no rewind was required:

      $ pg_rewind -D /var/lib/pgsql/9.6/data --source-server="host=xxxxx.xxx.xxxx port=5432 user=postgres"
      servers diverged at WAL position 1BB/AB000098 on timeline 5
      no rewind required

So I then updated the recovery.conf on slave2 with primary_conninfo equal to the new master IP.
When starting up posgres, it failed with the following error in the logs:

database system was shut down in recovery at 2018-03-08 17:52:10 ACDT
2018-03-08 17:56:27 ACDT [23026]: [2-1] db=,user= app=,host= LOG:  entering standby mode
cp: cannot stat '/pg_backup/backup/archive /00000005.history': No such file or directory
cp: cannot stat '/pg_backup/backup/archive /00000005000001BB000000AB': No such file or directory
2018-03-08 17:56:27 ACDT [23026]: [3-1] db=,user= app=,host= LOG:  consistent recovery state reached at 1BB/AB000098
2018-03-08 17:56:27 ACDT [23026]: [4-1] db=,user= app=,host= LOG:  record with incorrect prev-link 1B9/73000040 at
1BB/AB000098
2018-03-08 17:56:27 ACDT [23024]: [3-1] db=,user= app=,host= LOG:  database system is ready to accept read only
connections
2018-03-08 17:56:27 ACDT [23032]: [1-1] db=,user= app=,host= LOG:  started streaming WAL from primary at 1BB/AB000000
ontimeline 5 
2018-03-08 17:56:27 ACDT [23032]: [2-1] db=,user= app=,host= LOG:  replication terminated by primary server
2018-03-08 17:56:27 ACDT [23032]: [3-1] db=,user= app=,host= DETAIL:  End of WAL reached on timeline 5 at 1BB/AB000098.
cp: cannot stat '/pg_backup/backup/archive_sync/00000005000001BB000000AB': No such file or directory
2018-03-08 17:56:27 ACDT [23032]: [4-1] db=,user= app=,host= LOG:  restarted WAL streaming at 1BB/AB000000 on timeline
5
2018-03-08 17:56:27 ACDT [23032]: [5-1] db=,user= app=,host= LOG:  replication terminated by primary server
2018-03-08 17:56:27 ACDT [23032]: [6-1] db=,user= app=,host= DETAIL:  End of WAL reached on timeline 5 at 1BB/AB000098.

On the new master in the /pg_backup/backup/archive folder I can see a file 00000005000001BB000000AB.partial
Eg.
ls -l
-rw-------. 1 postgres postgres 16777216 Mar  8 16:48 00000005000001BB000000AB.partial
-rw-------. 1 postgres postgres 16777216 Mar  8 16:49 00000006000001BB000000AB
-rw-------. 1 postgres postgres 16777216 Mar  8 16:49 00000006000001BB000000AC
-rw-------. 1 postgres postgres 16777216 Mar  8 16:49 00000006000001BB000000AD
-rw-------. 1 postgres postgres 16777216 Mar  8 16:49 00000006000001BB000000AE
-rw-------. 1 postgres postgres 16777216 Mar  8 16:49 00000006000001BB000000AF
-rw-------. 1 postgres postgres 16777216 Mar  8 16:49 00000006000001BB000000B0
-rw-------. 1 postgres postgres 16777216 Mar  8 16:49 00000006000001BB000000B1
-rw-------. 1 postgres postgres 16777216 Mar  8 16:49 00000006000001BB000000B2
-rw-------. 1 postgres postgres 16777216 Mar  8 16:50 00000006000001BB000000B3
-rw-------. 1 postgres postgres 16777216 Mar  8 17:01 00000006000001BB000000B4
-rw-------. 1 postgres postgres 16777216 Mar  8 17:14 00000006000001BB000000B5
-rw-------. 1 postgres postgres      218 Mar  8 16:48 00000006.history

Any ideas?

Dylan

-----Original Message-----
From: Michael Paquier [mailto:michael@paquier.xyz]
Sent: Tuesday, 6 March 2018 5:55 PM
To: Dylan Luong <Dylan.Luong@unisa.edu.au>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Resync second slave to new master

On Tue, Mar 06, 2018 at 06:00:40AM +0000, Dylan Luong wrote:
> So everytime after promoting Slave to become master (either manually
> or automatic), just stop Slave2 and run pg_rewind on slave2 against
> the new maser (old slave1). And when old master server is available
> again, use pg_rewind on that serve as well against new master to
> return to original configuration.

Yes.  That's exactly the idea.  Running pg_rewind on the old master will
be necessary anyway because you need to stop it cleanly once, which will
cause it to generate WAL records at least for the shutdown checkpoint,
while doing it on slave 2 may be optional, still safer to do.
--
Michael

Re: Resync second slave to new master

From

Yavuz Selim Sertoğlu

Date:

08 March 2018, 13:48:29

If not set, could you add recovery.conf file
recovery_target_timeline='latest'
parameter?
https://www.postgresql.org/docs/devel/static/recovery-target-settings.html

2018-03-08 10:41 GMT+03:00 Dylan Luong <Dylan.Luong@unisa.edu.au>:

Hi Michael,

I tested the failover today and the slave 2 failed to resync with the new master (old slave1).

After I promoted the slave1 to become master, I was able to use pg_rewind on the old master and bring it back as new slave.

I then stopped slave2 and ran pg_rewind on slave2 against new master, it report that no rewind was required:

$ pg_rewind -D /var/lib/pgsql/9.6/data --source-server="host=xxxxx.xxx.xxxx port=5432 user=postgres"
servers diverged at WAL position 1BB/AB000098 on timeline 5
no rewind required

So I then updated the recovery.conf on slave2 with primary_conninfo equal to the new master IP.
When starting up posgres, it failed with the following error in the logs:

database system was shut down in recovery at 2018-03-08 17:52:10 ACDT
2018-03-08 17:56:27 ACDT [23026]: [2-1] db=,user= app=,host= LOG: entering standby mode
cp: cannot stat '/pg_backup/backup/archive /00000005.history': No such file or directory
cp: cannot stat '/pg_backup/backup/archive /00000005000001BB000000AB': No such file or directory
2018-03-08 17:56:27 ACDT [23026]: [3-1] db=,user= app=,host= LOG: consistent recovery state reached at 1BB/AB000098
2018-03-08 17:56:27 ACDT [23026]: [4-1] db=,user= app=,host= LOG: record with incorrect prev-link 1B9/73000040 at 1BB/AB000098
2018-03-08 17:56:27 ACDT [23024]: [3-1] db=,user= app=,host= LOG: database system is ready to accept read only connections
2018-03-08 17:56:27 ACDT [23032]: [1-1] db=,user= app=,host= LOG: started streaming WAL from primary at 1BB/AB000000 on timeline 5
2018-03-08 17:56:27 ACDT [23032]: [2-1] db=,user= app=,host= LOG: replication terminated by primary server
2018-03-08 17:56:27 ACDT [23032]: [3-1] db=,user= app=,host= DETAIL: End of WAL reached on timeline 5 at 1BB/AB000098.
cp: cannot stat '/pg_backup/backup/archive_sync/00000005000001BB000000AB': No such file or directory
2018-03-08 17:56:27 ACDT [23032]: [4-1] db=,user= app=,host= LOG: restarted WAL streaming at 1BB/AB000000 on timeline 5
2018-03-08 17:56:27 ACDT [23032]: [5-1] db=,user= app=,host= LOG: replication terminated by primary server
2018-03-08 17:56:27 ACDT [23032]: [6-1] db=,user= app=,host= DETAIL: End of WAL reached on timeline 5 at 1BB/AB000098.

On the new master in the /pg_backup/backup/archive folder I can see a file 00000005000001BB000000AB.partial
Eg.
ls -l
-rw-------. 1 postgres postgres 16777216 Mar 8 16:48 00000005000001BB000000AB.partial
-rw-------. 1 postgres postgres 16777216 Mar 8 16:49 00000006000001BB000000AB
-rw-------. 1 postgres postgres 16777216 Mar 8 16:49 00000006000001BB000000AC
-rw-------. 1 postgres postgres 16777216 Mar 8 16:49 00000006000001BB000000AD
-rw-------. 1 postgres postgres 16777216 Mar 8 16:49 00000006000001BB000000AE
-rw-------. 1 postgres postgres 16777216 Mar 8 16:49 00000006000001BB000000AF
-rw-------. 1 postgres postgres 16777216 Mar 8 16:49 00000006000001BB000000B0
-rw-------. 1 postgres postgres 16777216 Mar 8 16:49 00000006000001BB000000B1
-rw-------. 1 postgres postgres 16777216 Mar 8 16:49 00000006000001BB000000B2
-rw-------. 1 postgres postgres 16777216 Mar 8 16:50 00000006000001BB000000B3
-rw-------. 1 postgres postgres 16777216 Mar 8 17:01 00000006000001BB000000B4
-rw-------. 1 postgres postgres 16777216 Mar 8 17:14 00000006000001BB000000B5
-rw-------. 1 postgres postgres 218 Mar 8 16:48 00000006.history

Any ideas?

Dylan

-----Original Message-----
From: Michael Paquier [mailto:michael@paquier.xyz]
Sent: Tuesday, 6 March 2018 5:55 PM
To: Dylan Luong <Dylan.Luong@unisa.edu.au>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Resync second slave to new master

On Tue, Mar 06, 2018 at 06:00:40AM +0000, Dylan Luong wrote:
> So everytime after promoting Slave to become master (either manually
> or automatic), just stop Slave2 and run pg_rewind on slave2 against
> the new maser (old slave1). And when old master server is available
> again, use pg_rewind on that serve as well against new master to
> return to original configuration.

Yes. That's exactly the idea. Running pg_rewind on the old master will
be necessary anyway because you need to stop it cleanly once, which will
cause it to generate WAL records at least for the shutdown checkpoint,
while doing it on slave 2 may be optional, still safer to do.
--
Michael

Re: Resync second slave to new master

From

Michael Paquier

Date:

08 March 2018, 14:10:44

On Thu, Mar 08, 2018 at 10:48:29AM +0300, Yavuz Selim Sertoğlu wrote:
> If not set, could you add recovery.conf file
> recovery_target_timeline='latest'
> parameter?
> https://www.postgresql.org/docs/devel/static/recovery-target-settings.html

Yes, that's visibly the issue here.
--
Michael

Attachment

signature.asc

RE: Resync second slave to new master

From

Dylan Luong

Date:

08 March 2018, 16:38:30

Thanks! That fixed the issue.

-----Original Message-----
From: Michael Paquier [mailto:michael@paquier.xyz] 
Sent: Thursday, 8 March 2018 6:41 PM
To: Yavuz Selim Sertoğlu <yavuzselimsertoglu@gmail.com>
Cc: Dylan Luong <Dylan.Luong@unisa.edu.au>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Resync second slave to new master

On Thu, Mar 08, 2018 at 10:48:29AM +0300, Yavuz Selim Sertoğlu wrote:
> If not set, could you add recovery.conf file
> recovery_target_timeline='latest'
> parameter?
> https://www.postgresql.org/docs/devel/static/recovery-target-settings.html

Yes, that's visibly the issue here.
--
Michael