Postgresql - Streaming replication - STONITH

Question

Current situation:

1. PostgreSQL, streaming replication
2. Master failed, I created the trigger file. Slave is promoted to new master
3. After the old master restarted successfully, I have to create base backup and change config to change old master to new slave. (https://wiki.postgresql.org/wiki/Streaming_Replication: How to use/13).

My problem: the data amount is very large (sometimes 100GB), therefore the base backup takes a long time. How to turn the old master to a new slave without basebackup? (For example, with administrator command like pg_switch_xlog().)

Answer 1

well, you cannot switch back easily. what you can do is to use the old backup method:

SELECT pg_start_backup('whatever');
rsync the data dir
SELECT pg_stop_backup();

this is the best way as not all the data has to be transported over the network. i definitely recommend this replacement for pg_basebackup because it is the only RELIABLE way to get things back in order (nobody knows for sure what happens before the crash). sure, there might be other tricks out there but i would strongly vote for this method.