Setup Binary replication on PostgreSQL

On this post I’ll explain how to setup a master & slave replication using PostgreSQL 9.1. Well recently I had to work to migrate one database from one server to a new one, so the point of this replication was to make a migration with a minimal downtime of the system, but may be useful on some other situations as well like:

• Have a complete replica of your database production in case of a disaster.
• Balance read only queries.

So always the slave will have the role of read only database of course and we can promote as master in case of a disaster recovery or simply move the services to a new server. The replication type that I use is the Hot standby (see the different types here), so basically it consist in copy the transaction logs from the master database to the slave server where will be applied immediately after it receives the log.This transaction logs generated by postgresql are located under the directory $PGDATA/pg_xlog/ and consists of binary snapshots with all the changes made on the database. By default are setup in segments of 16MB, but we can increase it if our database is changed with a big frequency.

Servers used on this post:

• Master Host: 10.2.0.54
• Slave Host: 10.2.0.55

Slave server steps

•  Stop PostgreSQL daemon:

# service postgresql stop

•  Edit postgresql config file /var/lib/postgresql/9.1/main/recovery.conf as postgres user:

standby_mode = on
trigger_file = '/tmp/trigger.postgresql'
primary_conninfo = 'host=10.2.0.54 port=5432 user=postgres'

Master server steps

• Edit /etc/postgresql/9.1/main/postgresql.conf:

wal_level = hot_standby
max_wal_senders = 5
checkpoint_segments = 64
wal_keep_segments = 128

• Edit /etc/postgresql/9.1/main/pg_hba.conf:

host replication all 10.2.0.55/32 trust

• Restart postgresql:

# /etc/init.d/postgresql-9.1 restart

• Begin backup to the slave:

# su - postgres
$ psql
# select pg_start_backup('clone',true);

• rsync postgresql data:

# rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude pg_hba.conf --exclude pg_hba.conf --exclude postmaster.opts --exclude postmaster.pid /var/lib/postgresql/9.1/main/ [email protected]:/var/lib/postgresql/9.1/main/

• When rsync finish, stop backup command label:

postgres=# select pg_stop_backup();

• When finish the stop backup, copy quickly the WAL files:

# rsync -av /var/lib/postgresql/9.1/main/pg_xlog [email protected]:/var/lib/postgresql/9.1/main/pg_xlog

Slave server steps

•  Start postgresql:

# service postgresql start

Checking the status of the replication process

Master server

• Check sender process:

# ps -ef | grep sender
postgres 3721 27150 0 Nov29 ? 00:00:03 postgres: wal sender process postgres 10.2.0.55(37390) streaming 2/1A01B330

• Check from postgresql:

# su - postgres
$ psql
postgres=# select client_addr, state, sent_location, write_location,
flush_location, replay_location from pg_stat_replication;
client_addr | state | sent_location | write_location | flush_location | replay_location
-------------+-----------+---------------+----------------+----------------+-----------------
10.2.0.55 | streaming | 2/1A01B3C0 | 2/1A01B3C0 | 2/1A01B3C0 | 2/1A01B3C0
(1 row)

Slave server

• Check receiver process:

# ps -ef | grep receiver
postgres 1067 1063 0 Nov29 ? 00:00:42 postgres: wal receiver process streaming 2/1A01B3C0

Promote slave server to be a Master

• Create trigger file to stop replication stream and initialize postgresql in normal mode. The name of the file has to be the same of setup on the steps before on the recovery.conf file:

# touch /tmp/trigger.postgresql

– Source: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial