Hot Standby Replication

It pretty much goes without saying that you should back up your data. PostgreSQL gives you several good options. It's fairly straightforward to use pg_dump in a script to do a daily backup and copy it somewhere. Since 9.0, Postgres has had hot_standby, which gives you streaming replication to a database which can be queried, but not written to, and can automatically take over as master if it detects a trigger file.

You Should Already Have

• A master PostgreSQL server (9.1+ is required for pg_basebackup to work. Tested on 9.3) with a database that you want to back up
• A standby PostgreSQL server that is the same version as the master. It should have an empty pgdata directory.

We'll also need rsync, which can be installed from ports:

    # cd /usr/ports/net/rsync
    # make all install clean

Setting Up Replication

We're going to set up hot_standby on our standby. PostgreSQL requires that Write-Ahead Logging (WAL) is enabled. The logs generated by the master need to be moved to the standby server, where the PostgreSQL server there can use them to recreate the database.

One of the best ways to do the actual transfer of the files is by using rsync.

Creating SSH Keys For Rsync

rsync needs to be able to make a connection from the master to the standby server without having to prompt you for input, like a password would require. Using rsync over SSH facilitates secure logins using Pre-Shared Keys. Make sure to leave the password for the key blank.

On the master:

# su pgsql
$ mkdir /usr/local/pgsql/.ssh
$ ssh-keygen

Copy id_rsa.pub to /usr/local/pgsql/.ssh/master.id_rsa.pub on the standby server using a convenient method of your choosing.

On the standby:

# su pgsql
$ cd ~/.ssh
$ cat master.id_rsa.pub >> authorized_keys

We will also need a place to stage the files before they're committed to the database:

$ mkdir /usr/local/pgsql/WAL

On the master:

Verify that you can use rsync to transfer a small text file between the servers:

# su pgsql
$ rsync -W -a FILENAME pgsql@STANDBY_HOSTNAME:/usr/local/pgsql/WAL/

Edit postgresql.conf so that hot_standby = on, and restart the server:

# service postgresql restart

On the standby:

In order to get hot_standby running, we need to use pg_basebackup (restoring from a pg_dump won't work).

# su pgsql
$ pg_basebackup --pgdata=/usr/local/pgsql/data --host=MASTER_HOSTNAME --port=5432 --username=pgsql --xlog-method=stream --format=plain --progress --verbose

Create a file in /usr/local/pgsql called recovery.conf with the following contents:

standby_mode = on
primary_conninfo = 'host=MASTER_HOSTNAME port=5432 user=pgsql sslmode=verify-full'
restore_command = 'cp /usr/local/pgsql/WAL_Archive/%f "%p"'
archive_cleanup_command = 'pg_archivecleanup /usr/local/pgsql/WAL_Archive %r'
recovery_target_timeline = 'latest'

Create a symbolic link to that file in your pgdata directory:

$ cd /usr/local/pgsql/data
$ ln -s ../recovery.conf .

Start the server, and you should be replicating!

# service postgresql start

To verify that replication is working correctly, check your logs for a message like:

started streaming WAL from primary at 0/3A000000 on timeline 1

You should also run some queries to make sure that the standby database it updating.