Tuesday, 26 November 2013

Configure Hot Standby with Streaming Replication on Postgresql 9.1 and above

NOTE: Please note that this does NOT work on Windows! Backups only work on Unix machines.

NOTE: We need at least postgres 9.1 to make this back up work.

NOTE: You must be admin to restart the server. And you must be user postgres to make edits to the configuration files. All edits assume your base directory is /var/lib/pgsql/

Hot Standby is the name for the capability to run queries on a database that is currently performing archive recovery. Log Shipping replication allows you to create one or more standby nodes that are replicas of the primary node (or master node). Standby nodes can then be used for read-only query access.

To enable hot standby, make the following changes to the postgres.conf on the master:
wal_level = hot_standby
checkpoint_segments = 8
max_wal_senders = 3
wal_keep_segments = 8
Please note that the segment size of the write ahead log (wal) is a fixed 16MB. Should the server undergo huge stress, it could be that the streaming process cannot keep up with WAL, in which case, some data might be lost. To prevent this, increase the size of checkpoint_segments and wal_keep_segments.

Add the following changes to pg_hba.conf on the master:
host     replication     postgres         trust
Where is the slave. The above line is saying that we would like the postgres user on host to gain replication access to this master server.

Save these changes and restart the server. It is now time to configure the slave server.

First stop the postgres server on the slave and create a backup of the existing server. I do this by moving the entire data directory to old.
mv 9.2 9.2.old
Now that we have an empty data directory, copy all the contents from the master database with the pg_basebackup command. Note: this only works on versions of postgres greater than or equal to 9.1
pg_basebackup -D 9.2/data --host= -v -P -xlog -U postgres 
Where is your master server. This in essence copies the entire database over to the slave. Now we need to make some edits. If your access for the master and the slave are the same, you do not need to edit pg_hba.conf. Edit postgresql.conf:
hot_standby = on
This tells postgres that the server should act as a standby server.

Now we need to create a new file, called recovery.conf. In essence this file contains the information of the master server:
standby_mode = 'on'
primary_conninfo = 'host= port=5432 user=postgres password=postgres'
Now start the slave server. You must verify that the server is in recovery; this can be done by opening an sql pane and executing the query:
select pg_is_in_recovery();

No comments:

Post a Comment