Postgresql streaming replication part 2: the standby machine

Building on part 2, we have set up the master to connect and store files on the wal-archive machine. Setting up the standby machine will be much like setting up the master… in fact you want to set it up exactly the same way… If it is to be able to take over, it should essentially be a clone of the master except for the certificates that should be generated specifically for it, plus a few extra things because it also acts as a client…

here is a quick list of things we will be setting up:

configure master to allow replication client
configure standby to connect to wal archive
configure standby to make a backup of the master
configure standby to stay in sync with the master

You will need to connect to the database on the master and create a user with the replication property

$sudo su postgres
postgres@server:~$ psql
postgres=# CREATE USER [replication user] REPLICATION;

On the master add the following to /etc/postgresql/9.1/main/pg_hba.conf

hostssl replication [replication user] all cert map=[alias]

Then add to /etc/postgresql/9.1/main/pg_ident.conf

[alias] [CN of the standby machines server.crt] [replication user]

On the standby machine, set up /var/lib/postgresql/.ssh/ with a public and private key to allow connection to the wal-archive. (add the public key to the wal-archive’s authorized_keys).
Here are some instructions on creating ssh keys:

On the standby, make a /var/lib/postgresql/.postgresql/ folder, and put the standby’s ssl server certificates here named postgresql.key, and postgresql.crt. also move the root.crt here as well.(don’t forget to ‘chmod 0600 postgresql.key’)
On the standby stop the postgresql process and delete the contents of /var/lib/postgresql/9.1/main completely… we will be using a backup from the master to fill this directory.

copy the files from /etc/postgresql/9.1/main/ on the master to the same location on the standby.

now run the following commands:

export PGSSLMODE='verify-full'
pg_basebackup -x -U [replication user] -h [master server] -D /var/lib/postgresql/9.1/main/

Edit: the “-x” is extremely important
This will set up the system as a mirror of the master(including keys!) but we still have to tell the standby where to get new data from, and set up the standby server keys…

make the file /var/lib/postgresql/9.1/main/recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=[master server] user=[replication user]'
trigger_file = '/var/lib/postgresql/trigger'
restore_command = 'scp postgres@[wal-archive]:/var/lib/postgresql/wal-archive/%f "%p"'

delete the copied master server keys in /var/lib/postgresql/9.1/main/server.key and server.crt, and substitute the key and crt from the .postgresql folder you made earlier

now start the server, and look at the logs in /var/log/postgresql/postgresql-9.1-main.log
you should see some lines with text such as:

entering standby mode
streaming replication successfully connected to primary
database system is ready to accept read only connections

Any machine that could connect remotely to the master should be able to connect to the standby in read only mode, so now test the database by connecting to the standby, and doing a query.
Make a change to the database on the master, and run the query again to see that the changes have propagated to the standby server!

You have streaming replication!!!

Leave a Reply