0

I use Streaming Replication,and Replication works normally. Two servers are HA and PostgreSQL DB is replicated. The data in the DB is about 40G. When a failover occurs, the slave is successfully promoted to master, master becomes slave, and tries to replicate new master data. But, It takes a long time to replicate data from the new master server. (using pg_rewind / success / 38G - 6minutes).

Please let me know if there are other ways to save time.

This is the command I ran:

pg_rewind --target-pgdata="targetdir" --source-server="sourceserver"

This is the output:

connected to server
servers diverged at WAL position 35/DD0D2260 on timeline 37
rewinding from last common checkpoint at 35/DC4E94F8 on timeline 37
reading source file list
reading target file list
reading WAL in target
need to copy 39193 MB (total source directory size is 77268 MB)
698400/40134372 kB (1%) copied
  • What is your setting of checkpoint_timeout and max_wal_size? – Laurenz Albe Jan 7 at 7:43
  • The settings are currently commented out. Should I uncomment? #checkpoint_timeout = 5min #max_wal_size = 1GB #min_wal_size = 80MB – youngwoongcha Jan 7 at 8:27
  • No, these settings should be fine for fast pg_rewind. – Laurenz Albe Jan 7 at 8:45
  • Thank you for your support. pg_rewind ran successfully and immediately found the wal file of the checkpoint. But it takes a long time to copy the whole file into the postgresql folder. This seems to be a structural problem in pg_rewind. Please let me know if you have any other comments or if you have a way to shorten the time to failover situation. – youngwoongcha Jan 7 at 9:13
0

pg_rewind connects to the new master and locates the latest checkpoint it shares with the old master. Then it examines local WAL to find all blocks that have been modified since and copies these blocks from the new master.

So the procedure is slow if

  • promotion happened a long time ago, so pg_rewind has to dig through many WAL files

or

  • many blocks have changed since the promotion

From the output of pg_rewind it becomes obvious that half of the blocks in the database cluster have been modified since the slave was promoted. So the problem is that you waited too long after promotion. Immediately after failover, pg_rewind would be much faster.

  • When pg_rewind is run immediately after a failover, this log is shown."source and target cluster are on the same timeline // no rewind required" – youngwoongcha Jan 9 at 0:44
  • That's the best of all possible outcomes. You always run pg_rewind; sometimes there is nothing to do, sometimes a few transactions are rewound. Looks like the question is answered. – Laurenz Albe Jan 9 at 9:02
  • Yes. It seems that pg_rewind did not execute correctly. However, there is no replication in this situation. Something seems to be out of sync. This link seems to be the same situation as me. "github.com/vmware/pg_rewind/issues/4" Anyway, in the end, it seems that many blocks have changed to recover the entire file. – youngwoongcha Jan 10 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.