Replication in Postgresql 9.0

Hello. Considering that since the release of PostgreSQL 9 it's been some time — I decided to probe one of its new features — native replication. As you know, the new mechanism is based on transmitting a XLOG'from master to slave. One of the advantages of fatty can be called normal processing ALTER's. In other words — the administrator 9th version can do without Slony.

We assume that the packages you have installed(and if not, for Debian/Ubuntu you can get them here) has created a database testdb. I describe the process assuming that a base postgres'you are in /var/lib/postgresql/9.0/, if not — use the correct path for you.

So there you go:
1. Custom listen && pg_hba.conf
Suppose that the ip 192.168.0.1 we have a master and slave is 192.168.0.2. Then the string "listen" in postgresql.we have conf will look like this:
listen_addresses = '192.168.0.1'

And in pg_hba.conf on the master would be this entry:
the host replication postgres 192.168.0.2/32 trust

2. Turn on the master all that is needed for replication
#set the logging so that the slave could be used for reading. It is possible to put hot_standby instead of archive and then it will just store the magazine(unreadable).
wal_level = hot_standby

#Maximum number of slaves
max_wal_senders = 2

#How many pieces of the log will store?If suddenly you have a large load on the entry in the database - perhaps this value would need to increase to time to get to the replicas.
wal_keep_segments = 32

#In case of nuclear war, duplicate the log in a separate place(it is better to clean the crown of this location, deleting everything more than a day). Although, off. the man says that it is generally not necessary.
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.0/main/archive/%f'

Now the master needs to be restarted.

3. Sent the master to the slave.
We need something that can send data over the network. I used rsync, but of course, you can use any other tool. Simmer postgresql on the slave then on the master execute the following:
$ psql -c "SELECT pg_start_backup('label', true)"
$ rsync -a /var/lib/postgresql/9.0/main/ slave:/var/lib/postgresql/9.0/main/ --exclude postmaster.pid
$ psql -c "SELECT pg_stop_backup()"


4. Include hot_standby on the slave
Added to postgresql.conf:
hot_standby = on

5. Create a replication configuration on the slave.
To this end, the file recovery.conf(need to create), lying in /var/lib/postgresql/9.0/main, write the following:
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.1 port=5432 user=postgres'
trigger_file = '/var/lib/postgresql/9.0/main/trigger'
restore_command = 'cp /var/lib/postgresql/9.0/main/archive/%f "%p"'

Separately talk about trigger_file. By default it should not be. It is needed in order that in case facepa you could(creating this file) to stop the replication process and make slaves writable.

6. Done!
Launching a slave. If the slave, the command
ps aux | grep receiver
Shows something of the form
postgres 1953 0.0 0.0 101980 4156 ? Ss 19:19 0:00 postgres: wal receiver process streaming 2/B40001D0
(read — there is a postgres process with a description of wal receiver) — we can assume that it is.

7. Monitoring
Now a little about sad, unlike elephants, native replication is not able to show the lag in human-readable format.
One possible way to monitor replication is to consider the difference in log positions on the master/slave
psql -c "SELECT pg_current_xlog_location()" -h192.168.0.1
--------------------------
0/2000000
(1 row)

psql -c "select pg_last_xlog_replay_location()" -h192.168.0.2
pg_last_xlog_replay_location
------------------------------
0/2000000
(1 row)
From the obtained results is necessary to cut the slash and that which came before it, then convert from HEX in normal number, the result is an abstract value, the critical threshold which will be different for each case.
A more correct way I see the creation of a plate with one field of type timestamp to keep there one single record and every n-seconds(eg 30) to update its value to the current time. Then, subtracting from the entries in the wizard, the contents of the same plate on the slave, we get the time lag.
8. If something terrible has happened
Usually, replication is necessary for two cases: the load distribution and in case with the master something happens. So, if you have rejected the master, then your actions will be like:
a) Create the slave trigger-file(described in the 5th paragraph). Slave appears on the record, stopping the replication, you can transfer it to the clients.
b) Next, when the machine is serving master, will be back — we wrap the process around — making of the original wizard replica — master running on the 5th paragraph. When they recover — deleted on the slave trigger file — everything should be back to normal.

PS This text is a free translation wiki page.

Article based on information from habrahabr.ru