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:
http://www.ece.uci.edu/~chou/ssh-key.html
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:
#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!!!
Recent Comments