Just a couple of words about stream replication in postgres ...

Asynchronous streaming replication is a good thing. For her now there are many different utilities, you can build a large, powerful and true system.

But suppose you have a small task, a couple of servers and built-in postgres replication. It is enough about its setting up of materials, and about actions in case of a master’s refusal can also be found.
')
But with the question of restoring the wizard, it turned out to be a disaster, so I’m sharing with you the guide to action I collected piece by piece from the Internet, tested and protested on Debian GNU / Linux and FreeBSD 8.2 servers with PostgreSQL 9.1



For a start, we have:

Serv1 - Master
Serv2 - Slave

Fall of the Master

Suppose that the database on Serv1 (master) has collapsed, died and will not rebel itself. At the same time, Serv2 works stably in the slave mode.

Then on Serv2 in postgresql.conf it is necessary to uncomment

wal_level = hot_standby max_wal_senders = 2 wal_keep_segments = 64 archive_mode = on archive_command = 'cp %p $LOG_DIR/archive/%f Serv1' 

in $ HOME rename recovery.conf to recovery.done


Restart Serv2. So Serv2 will become a master.

Restoration of the former Master

Now we have Serv2 running in master mode, Serv1 is disabled.

You need to make Serv1 a slave like this:

On Serv2 run:

 psql -c "SELECT pg_start_backup('label', true)" rsync -avzh --progress $HOME/ Serv1:$HOME/ --exclude postmaster.pid psql -c "SELECT pg_stop_backup()" 

In the postgresql.conf config on Serv1, comment out what was uncommented on Serv2, namely:

 #wal_level = hot_standby #max_wal_senders = 2 #wal_keep_segments = 64 #archive_mode = on #archive_command = 'cp %p $LOG_DIR/archive/%f Serv2' 

And uncomment:

 hot_standby = on 

In $ HOME, rename recovery.done to recovery.conf

Run postgres on Serv1.
Serv1 is now in slave mode.

You can check the slave's work by running it.

 ps aux | grep receiver 

and getting the result of the form

 postgres: wal receiver process (postgres) 

Switch back to Master

(see the first paragraph and do the opposite)

Now the ex-master of Serv1 is a slave of Serv2. Both work stably, the copy of the slave is correct, the discrepancy is minimal.

To become a Serv1 master:

On it in postgresql.conf uncomment

 wal_level = hot_standby max_wal_senders = 2 wal_keep_segments = 64 archive_mode = on archive_command = 'cp %p $LOG_DIR/archive/%f' 

Stop Serv2 (which is the master for now) and on Serv1 in $ HOME rename recovery.conf to recovery.done

Now Serv1 is again a working wizard.

For attachment with Serv2:

On Serv1 run:

 psql -c "SELECT pg_start_backup('label', true)" rsync -avzh --progress $HOME/ Serv2:$HOME/ --exclude postmaster.pid psql -c "SELECT pg_stop_backup()" 

On Serv2 in postgresql.conf, comment out:

 #wal_level = hot_standby #max_wal_senders = 2 #wal_keep_segments = 64 #archive_mode = on #archive_command = 'cp %p $LOG_DIR/archive/%f' 

And uncomment:

 hot_standby = on 

Also on Serv2 in $ HOME rename recovery.done to recovery.conf

Run postgres on Serv2. Now Serv2 is a slave running again.

Is done. Now everything is in its place: Serv1 - master, Serv2 - slave.

---

I apologize in advance for the share of copy-paste and low-level chewing, but this information is not completely and comprehensively accessible to mere mortals, so I hope it will be used (: