pg_rewind is a PostgreSQL tool since 9.5 version. It provides synchronization between standby and old primary. Especially, pg_rewind provides advantage for big databases. You can sync databases without using pg_basebackup.
pg_rewind scans the old primary database’s pgdata folder and identifies data blocks changed during the switch to standby database, then copies only changed blocks from relation files are copied; all other files are copied in full, including configuration files from promoted standby database. And the old primary database synchronize with applying changed blocks.
pg_rewind requires that the target server either has the wal_log_hints option enabled in postgresql.conf or data checksums enabled when database was initialized with initdb.
Also, you should be careful about configuration files. postgresql.conf,pg_hba.conf and postgresql.auto.conf files copied from the promoted standby database. You should configure access list of pg_hba, port information and primary_conninfo parameters.
Finally, you should create standby.signal file in data folder of old primary database before start of old primary database’s postgresql service.
TEST
I applied failover. And insert some test data to promoted standby database. Now I will sync promoted standby database and old primary database with pg_rewind.
Test Systems
Primary Database : 192.168.9.128 – 5432
Standby Database : 192.168.9.129 – 5433
PG_REWIND Steps
1 – Stop Old Primary Database
systemctl stop postgresql-pri.service
2- Run pg_rewind on Old Primary Database
Source : Promoted Standby Database
Target : Old Primary Database
su – postgres
pg_rewind –target-pgdata=/pgdata/12/data –source-server=”host=192.168.9.129 port=5433 user=postgres”
pg_rewind: servers diverged at WAL location 0/E0000D8 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/E000028 on timeline 1pg_rewind: Done!
If you get an error about could not found WAL records, you can copy manually wal file from promoted standby database to old primary database’s pg_wal directory. If you do not want to get error message like above, you should configure restore_command parameter.
3- Create standby.signal File
touch standby.signal
4- Configure postgresql.auto.conf File on Old Primary Database
Change host and port parameter like below. These are promoted standby database information.
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = ‘user=repuser password=yourpass host=192.168.9.129 port=5433 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any’
5- Configure postgresql.conf File on Old Primary Database
port = 5432
Also, If you use, you can configure archive_command and restore_command parameters.
6- Start Old Primary Database Service
systemctl start postgresql-pri.service
Finally, your old primary database is standby database now and follow up to promoted standby database. You can test :)
Source : postgresql.org