Postgresql – Streaming replication – STONITH

failoverpostgresqlreplication

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().)

Best Answer

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.

Related Question