Postgresql 9.2 Replication on CentOS 6

gistfile1.md

Master Server

In /var/lib/pgsql/9.2/data/postgresql.conf add

wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments

Allow host-based authentication in /var/lib/pgsql/9.2/data/pg_hba.conf with the following:

host    replication    postgres    128.x.x.x/32    trust

Restart server

service postgresql-9.2 restart

Data Snapshot

You need to get a snapshot of the data to the slave:

su - postgres
psql -c "SELECT pg_start_backup('replbackup');"
tar cfP /tmp/db_file_backup.tar /var/lib/pgsql/9.2/data
psql -c "SELECT pg_stop_backup();"

Now move it to the slave server:

scp /tmp/db_file_backup.tar user@slave_server:/tmp/

Slave Setup

Stop Postgres (if it's running) and make a backup of the data directory and restore the snapshot (and remove the pid file from the master server):

service postgresql-9.2 stop
mv /var/lib/pgsql/9.2/data /var/lib/psql/9.2/data.old
tar xvfP /tmp/db_file_backup.tar
rm -f /var/lib/pgsql/9.2/data/postmaster.pid

Slave configuration

Edit /var/lib/pgsql/9.2/data/postgresql.confg by adding the following:

hot_standby = on

Recovery

You need to create a recovery.conf to start getting logs from the master and update the permissions.

cp /usr/pgsql-9.2/share/recovery.conf.sample /var/lib/pgsql/9.2/data/recovery.conf
chown postgres.postgres /var/lib/psql/9.2/data/recovery.conf

Then add the following options:

standby_mode = on
primary_conninfo = 'host=master_server port=5432"

Now start up Postgres:

service postgresql-9.2 start

Testing

Insert a record in to a table on the Master and then query for that value on the slave.

[user@server ~]# psql -hmaster -Uuser -ddatabase
masterdb=# CREATE TABLE replication_test(a varchar(20));
masterdb=# INSERT INTO replication_test VALUES('replicated data');
masterdb=# \q

[user@server ~]# psql -hslave -Uuser -ddatabase
slavedb=# SELECT * from replication_test;
          a
---------------------
replicated data
(1 row)

slavedb=#