Failover on Postgresql 9.1 and above

The previous post allowed you to create a server in HOT standby mode. As mentioned earlier, you can only use this to do readonly queries. Now let us say that the primary server goes down, following are the steps you'll need to promote the standby server and make it your primary.

Open a shell on the standby server with the postgres user and type:

pg_ctl promote -D /var/lib/pgsql/9.2/data/

And that is it! Your standby server has now become a primary instance, ie, you can do inserts, deletes and updates now.

Please note that if the standby server fails, we quickly need to bring that back as well. To detect failure, on a daily basis, you should do a count on the audit log table on both primary and standby and make sure they are equal. If the standby fails, it is best to kill that database and redo the steps from the previous post.

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        192.168.2.37/32         trust

Where 192.168.2.37 is the slave. The above line is saying that we would like the postgres user on host 192.168.2.37 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=10.1.1.7 -v -P -xlog -U postgres 

Where 10.1.1.7 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=10.1.1.7 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();