Table of contents

Set up PostgreSQL replication

This how-to guide explains how to set-up PostgreSQL replication.

New servers can be created using the govuk_postgresql::server::primary and govuk_postgresql::server::standby classes. This will prepare them, but replication has to be initiated manually.

Syncing a standby

To sync (or indeed resync) a standby from a primary run sudo pg_resync_slave.

By default, you should not be prompted for a password. If you are prompted, then this password is stored in the govuk-secrets repo under the govuk::node::s_postgresql_primary::standby_password key and can be read with:

bundle exec rake 'eyaml:modify[edit,staging]'

Where staging is the environment the server is in. This will sync data from the primary and restart (resulting in some downtime) the service on the standby with the new data and replication config.

For example (the notice is OK):

vagrant@postgresql-standby-1:~$ sudo pg_resync_slave
Password:
36237/36237 kB (100%), 1/1 tablespace
NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
pg_basebackup: base backup completed
 * Stopping PostgreSQL 9.3 database server                                 [ OK ]
 * Starting PostgreSQL 9.3 database server                                 [ OK ]

References

These tutorials give an overview of what’s happening. Please don’t follow them verbatim though, because much of the process has been automated by Puppet.

This page was last reviewed . It needs to be reviewed again by the page owner #2ndline.