A few more words about streaming replication in postgres...

Asynchronous streaming replication is a good thing. For her now there are many different tools available to build big, powerful and reliable system.

But suppose You have a small problem, a couple of servers and postgres built-in replication. About her setting up enough material and action in the event of failure of the master can also be found.

But the question of restoring the master was in trouble, so I share with You the pieces collected from the Internet a guide to action, tested and protestanism me on a bunch of servers Debian GNU/Linux and FreeBSD 8.2 with PostgreSQL 9.1



For a start we have:

Serv1 — Master
Serv2 — Slave

Fall of the Wizard

Suppose that DB on Serv1 (the master) collapsed, killing and rise she did not. While Serv2 has been operating in slave mode.

Then on Serv2 in postgresql.conf need to uncomment the
the

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 to rename recovery.conf into recovery.done

If anyone knows about the content of the file recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=master_host, port=master_port user=master_user'
restore_command = 'cp $LOG_DIR/archive/%f %p'
trigger_file = '$HOME/trigger'

To Restartati Serv2. So Serv2 will become master.

Restore Wizard

Now we Serv2 is running in wizard mode, Serv1 is disabled.

Need to make Serv1 the slave as follows:

On Serv2 to execute:
the

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 config file postgresql.conf on Serv1 comment out what uncomment to Serv2:
the

#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:
the

hot_standby = on

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

To run postgres on Serv1.
Now Serv1 is running in slave mode.

to Inspect the work of the slave can perform on it
the

ps aux | grep receiver

and getting the result of
the

postgres: wal receiver process (postgres)

Switch back to Master

( see the first paragraph and do the opposite )

Now the ex-Serv1 master Serv2 is the slave. Both are stable, a copy of the slave is correct, the difference is minimal.

For becoming a master Serv1:

On it in postgresql.conf to uncomment the
the

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 master) and for Serv1 in $HOME to rename recovery.conf into recovery.done

Now Serv1 is once again running the wizard.

For coupling the slave Serv2:

On Serv1 to execute:
the

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:
the

#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:
the

hot_standby = on

On Serv2 in $HOME to rename recovery.done in recovery.conf

To run postgres on Serv2. Now Serv2 working again slave.

Ready. Now everything is in place: Serv1 — master, Serv2 — slave.

---

I apologize in advance for the share of copy-paste and low-level chewing, but holistically and is available for mere mortals this information in the network is not found, so I hope you will find the application (:

Article based on information from habrahabr.ru