search by tags

for the user

adventures into the land of the command line

setting up replication on postgres

a couple of posts back, we set up replication in mysql. let’s configure asynchronous replication in postgres using a write-ahead log and a hot standby. the primary and standby both need to be able to access some directory that contains a WAL or write-ahead log. postgres has two main configuration files that will allow us to change key parameters for replication. both files are located in the installation’s data directory

/var/lib/pgsql/9.2/data/postgresql.conf # main config file
/var/lib/pgsql/9.2/data/pg_hba.conf # access control config

primary server configuration

edit the postgresql.conf file and modify these parameters to be

$ vim /var/lib/pgsql/9.2/data/postgresql.conf

# addresses for the database to listen to
listen_addresses = 'localhost,IP_address_of_THIS_host'

# Enable read-only queries on a standby server by changing the
# wal_level to "hot_standby"
wal_level = 'hot_standby'

# Enable WAL archiving on the primary to an archive directory
# accessible from the standby. This can be set only at the startup
archive_mode = on
archive_command = 'cd .'

# set the maximum number of concurrent connections from the standby
# servers
max_wal_senders = 3

# To prevent the primary server from removing the WAL segments
# required for the standby server before shipping them, set the
# minimum number of segments retained in the pg_xlog directory. At
# least wal_keep_segments should be larger than the number of
# segments generated between the beginning of enable WAL
# online-backup and the startup of streaming replication. If you   
# archiving to an archive directory accessible from the standby, this
# may not be necessary.
wal_keep_segments = 32

# determines the maximum number of concurrent connections to the database server
max_connections = 1500

#if hot_standby is turned on in postgresql.conf and there is a recovery.conf file present, the server will run in Hot Standby mode, which means if the primary breaks, the standby will reconfigure itself to become the primary
hot_standby = on

# disable synchronous replication. one caveat of running a hot standby system in synchroous mode is that if the standby crashes, the primary will continue to wait for a response on every transaction. this is bad. the primary won't wait for the standby in asynchronous mode.
synchronous_commit = off

edit and update the pg_hba.conf file to allow the standby to connect to the primary

$ vim /var/lib/pgsql/9.2/data/pg_hba.conf

#host   database      user  address/mask              auth method
host    replication   repl  IP_address_of_standby/32  md5

start the primary database server

$ /etc/init.d/postgresql start

# or

$ su -l postgres -c /usr/pgsql-9.2/bin/pg_ctl start –D /var/lib/pgsql/9.2/data

standby server configuration

configure the standby, much the same as the primary, but with a few differences

in the postgresql.conf file

$ vim /var/lib/pgsql/9.2/data/postgresql.conf

listen_addresses = 'localhost,IP_address_of_THIS_host'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 3
wal_keep_segments = 32
max_connections = 1500
hot_standby = on
synchronous_commit = off

configure a recovery file on the standby in the data directory containing the below

$ vim /var/lib/pgsql/9.2/data/recovery.conf

# tell this server to be the standby
standby_mode = 'on'

# details of the primary, so that the standby knows where to connect
primary_conninfo = 'host={PRIMARY-DB_IP} port=5432 user=repl'

# if the primary goes down, create the file referenced below on the file system and the standby will reconfigure itself to act as the primary
trigger_file = '/var/lib/pgsql/9.2/trigger/failover_triggerfile'

edit the pg_hba.conf file to allow the primary server to connect to this one in case we need to turn the standby into the primary later on down the road

$ vim /var/lib/pgsql/9.2/data/pg_hba.conf

#host   database      user  address/mask              auth method
host    replication   repl  IP_address_of_primary/32  md5

taking a db dump from the primary and copying it to the standby

using “postgres” user, login to the standby database server and ensure the service is stopped by either

$ /etc/init.d/postgresql stop

# or

$ su -l postgres -c /usr/pgsql-9.2/bin/pg_ctl stop –D /var/lib/pgsql/9.2/data

login as user “postgres” into primary server and enable the hot-backup mode

$ psql –U postgres -c "SELECT pg_start_backup('backup', true)"

copy the primary’s data directory to the standby server by syncing the data directory of the two servers. you can use the following command to sync the data directories of the servers. replace the {Standby-IP} with IP of the standby server.

$ cd /var/lib/pgsql/9.2
$ rsync -av --exclude pg_xlog --exclude postgresq*.conf --exclude pg_hba.conf –-exclude recovery.conf data/* {Standby-IP}:/var/lib/pgsql/9.2/data

stop the backup back on the primary after the sync has finished

$ psql –U postgres -c "SELECT pg_stop_backup()"

start the standby database server

$ /etc/init.d/postgresql start

# or

$ su -l postgres -c /usr/pgsql-9.2/bin/pg_ctl start –D /var/lib/pgsql/9.2/data

test replication is working

log into the primary as postgres and create some stuff

$ psql -U postgres

postgres#= CREATE TABLE rep_test (test varchar(40));
postgres#= INSERT INTO rep_test VALUES ('data one');
postgres#= INSERT INTO rep_test VALUES ('some more words');
postgres#= INSERT INTO rep_test VALUES ('lalala');
postgres#= INSERT INTO rep_test VALUES ('hello there');
postgres#= INSERT INTO rep_test VALUES ('blahblah');

log into the standby as postgres and see if the data has been replicated

$ psql -U postgres

postgres#= SELECT * FROM rep_test;

 data one
 some more words
 hello there
(5 rows)

try to insert more data into the table on the standby

postgres#= INSERT INTO rep_test VALUES ('oops');

ERROR:  cannot execute INSERT in a read-only transaction

checking if replication has broken at some point later

you can always try the method above, but a couple of other ways are

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
----------------+-----------+---------------+----------------+----------------+----------------- | streaming | 23/F5F57088   | 23/F5F57088    | 23/F5F57088    | 23/F5F57088

on the primary, show the database is up to:

$ psql -U postgres

postgres=# SELECT pg_xlog_location_diff(pg_current_xlog_location(), '0/0') AS offset;

on the slave, check the same:

$ psql -U postgres

psql> SELECT pg_xlog_location_diff(pg_last_xlog_receive_location(), '0/0') AS receive, pg_xlog_location_diff(pg_last_xlog_replay_location(), '0/0') AS replay;

if we get the same number in the above two queries then we can assume that the replication is working

try it a few times as the numbers will be updating as things are added to the databases, keep an eye on the standby’s number to see if it is increasing along with the primary’s.

if the standby’s number doesnt change and/or is very far behind the master, then replication may be broken

phew. another long and arduous trial conquered!