Eventually postgresql comes out with its inbuilt replication solution in 9.0 beta. Setting up of replication is quite simple. We will look at setting up a simple master-slave replication between two servers of postgresql.
The way replication works in postgres is that the master keeps on writing “write ahead log” files (also known as wal files). The slave can be configured to run either in recovery mode or hot standby. The difference being that in recovery mode the wal files require to be shipped using something like a cron. And the slave applies the wal files to its database. The problem here is that it is not smooth and the delay between master and slave can be big. The logic of hot standby is that the slave connects to the master and fetches the log files from master. And it applies those log files on its database. Here the lag between master and slave is not huge since the slave tries to keep up with the master. This is also known as streaming replication because the wal files are continuously shipped to the slave and applied there. There could be multiple slaves fetching wal files from master and applying to their own database. As opposed to mysql the slave here is readonly – it cannot be modified. It can be used only as read-only slave for select queries.
Lets look at deploying master-slave replication between two servers
Master server : 172.16.3.173
Slave server : 172.16.3.211
Install postgresql 9.0 on both servers. Simple steps for installation are
$ make install
$ adduser postgres
Initialize the data directory on master. No need to initialize it on slave – since we will be copying the master directory to slave for starting replication.
$ mkdir /usr/local/pgsql/data
$ chown -R postgres.postgres /usr/local/pgsql/data
$ su – postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
In case you are trying to create a slave of existing postgres database, you can simply skip these steps and install postgres on a slave machine – and not initialize the data directory on the slave machine.
Change config on master so that any slave is authorized connect to the master. Add the following line to pg_hba.conf of master.
$ vim data/pg_hba.conf
host replication postgres 172.16.3.211/22 trust
This says that 172.16.3.211 (the slave) is allowed to connect to master for replication without any password. Configure this for all slaves (if you plan to have multiple slaves).
Change parameters on the master postgresql for streaming replication.
$ vim data/postgresql.conf
# allow all ip addresses to connect to the master.
listen_addresses = '*'
# set wal_level for hot standby replication.
wal_level = hot_standby
# enable archiving to an archive directory accessible from the standby. Not required for streaming replication. But set it for "just in case" scenarios
archive_mode = on
archive_command = 'cp "%p" /path/to/archive_directory/"%f"'
# set the number of concurrent slave connections
max_wal_senders = 5
# set the minimum no of WAL segments to be retained in the pg_xlog directory from where the slave can pick up the WAL segments.
# This prevents removal of WAL segments before they are shipped the slave.
wal_keep_segments = 32
Start postgres on primary/master server.
Create a backup of master and ship it to the slave server.
$ ./bin/psql -c “SELECT pg_start_backup(‘postgres’,true)”
$ rsync -a /usr/local/pgsql/data email@example.com:/usr/local/pgsql/data
$ ./bin/psql -c “SELECT pg_stop_backup()”
Clean the data directory in slave and set replication parameters.
$ rm /usr/local/pgsql/data/postmaster.pid
Here the postgresql.conf is same as master, so just set the parameters required by slave.
$ vim /usr/local/pgsql/data/postgresql.conf
# disable archiving
archive_mode = off
# enable read-only queries on slave - during replication/recovery
hot_standby = on
Setup the parameters in recovery.conf required for streaming replication
$ vim recovery.conf
# shell command for copying log files back from the archive. Should not be required.
restore_command = 'cp -i /home/postgres/archive/%f "%p" </dev/null'
# enable standby
standby_mode = 'on'
# connection information for connecting to master
primary_conninfo = 'host=172.16.3.173 port=5432 user=postgres'
# stop streaming and enable the server to work in read/write mode, if this file is found.
# server keeps on polling for the trigger file and stops replication once this file is found. The server can then be used in read/write mode.
trigger_file = '/usr/local/pgsql9.0/data/stop.replication'
Start postgres on slave. It will start streaming replication between master and slave.
To check replication see if sender process is active on master
$ ps -ef | grep sender
postgres 12332 12158 0 10:13 ? 00:00:00 postgres: wal sender process postgres 172.16.3.211(41497) streaming 0/90149F0
postgres 16233 9474 0 11:36 pts/2 00:00:00 grep sender
On slave check if receiver process is working
$ ps -ef | grep receiver
postgres 27952 27945 0 10:12 ? 00:00:00 postgres: wal receiver process streaming 0/9014974
postgres 30354 23492 0 11:36 pts/0 00:00:00 grep receiver
That is it. Run a few DML/DDL queries on master and check if they are being replicated on slave. The lag between master and slave is not noticable.