Postgresql streaming replication part 1: remote wal archive

I finally got streaming replication to work!

There were a few things that I needed to set up before it worked right… I will be working under the assumption that you already have SSL connections and authentication set up correctly.

First the background. You are probably going to want your WAL archive on another machine, so the first thing to do is be sure that your server can send the files over there. I decided to use SCP to move the files, and I wrote a little script that just uses SCP to move a file to the right spot.

#! /bin/bash
#remote_wal.sh
scp $1 postgres@[destination]:/var/lib/postgresql/wal-archive/$2

This will probably fail because you probably didn’t set up SSH key authentication between hosts, or because you didn’t make the directory on the archive machine.
I like to disable password authentication, and instead rely only on SSH keys to connect. I also like to enable a default location for certificates for users that don’t normally have them.

#in /etc/ssh/sshd_config on the WAL archive machine make sure the following is set
PubkeyAuthentication yes
UsePam no

#in /etc/ssh/ssh_config on the master server make sure the following is set
IdentityFile ~/.ssh/[key_name]

you can follow instructions here to make keys: http://www.ece.uci.edu/~chou/ssh-key.html
now put the SSH private key in /var/lib/postgresql/.ssh/[key_name] on the server
and put the SSH public key in /var/lib/postgresql/.ssh/authorized_keys on the WAL archive.

also mkdir /var/lib/postgresql/wal-archive

Now make sure the remote_wal.sh script works by running it with a couple of test files from the master server as the postgres user.

With that set up, you are ready to set up postgresql.conf

wal_level = hot_standby
archive_mode = on
archive_command = '/etc/postgresql/9.1/main/remote_wal.sh %p %f'

max_wal_senders = 5
wal_keep_segments = 32
synchronous_standby_names = '*'

hot_standby = on

Now, if you do a ‘sudo service postgresql restart’ a couple of times  on the master, you should see some wal-archives appear on the wal-archive machine. (watch ls -la /var/lib/postgresql/wal-archive/)
If it doesn’t work, check the log on the master at /var/log/postgresql/postgresql-9.1-main.log to see why.
(The first time I tried it, the script was working fine by hand, but the transfer would fail when the server would try to use it because I had a typo in the path to the script…)