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_basebackupto 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
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
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
/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/
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
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
$ 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.