I'm rather new to pg so please bear with me.
I've set up two hosts and installed pg10 on both. Then I configured log streaming replication between them. This worked fine.
I wanted to test fail over, so I stopped postgresql service on Primary and create trigger file on Standby, this also worked find. I check and my standby is not in recovery mode anymore.
Now I want to recover old Primary as new standby. I tried using pg_rewind but it gives me the following error message
/usr/lib/postgresql/10/bin/pg_rewind --target-pgdata=/var/lib/postgresql/10/main --source-server="host=server2ip port=5432 user=postgres"
target server needs to use either data checksums or "wal_log_hints = on"
Failure, exiting
I have wal_log_hints = on set on both servers.
Can anybody help me figure out how to get this to work.
Below are my settings which are the same on both servers. Replication before failing Primary worked great.
Below are lines which I changed in postgresql .conf
listen_addresses = '*'
max_connections = 200
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 5
wal_level = replica
wal_log_hints = on
archive_mode = on
archive_command = '/bin/true'
max_wal_senders = 5
wal_keep_segments = 5000
max_replication_slots = 5
effective_cache_size = 2GB
I also added the below lines to pg_hba.conf file
host all postgres server1ip/24 trust
host all postgres server2ip/24 trust
And finally I create recovery.conf file as below.
standby_mode = 'on'
primary_conninfo = 'host=sciontsdbbc01 port=5432 user=replicate password=rep-password'
trigger_file = '/tmp/PG_MasterNow'
Best Answer
But presumably it was set too late, that is, after the generation of at least some of the WAL files involved in the rewind (in the
pg_waldirectory).