Multi-slave PostgreSQL replication

I've recently had a rather large problem with an overloaded database server.  I had several machines with read-only access to a PostgreSQL instance but data was inserted via scripts on the local host and updated interactively via a web application. The scenario is fairly simple and a perfect use-case for multi-slave replication -- let the scripts and web application update the master, replicate to <n> slaves and have the servers with read-only access query the slaves.

This is incredibly easy to setup in a virtual environment, all the more so because the basic process applies regardless of the number of accounts that need access to the data, the number of replication slaves, the number of databases hosted on the master server, etc.

In the end I'll need at least two new virtual machines -- one to act as the database master and one to act as the slave. To keep it simple, I'll clone out the FreeBSD virtual machine. On Mac OS you can control-click the virtual machine and select "Clone":

When cloning, it's important to remember to reinitialise the MAC address of the NIC. This can always be done later but it's easiest to make sure that checkbox is selected:

A linked clone is a lot like deploying a new server but booting over the network from the hard drives of another server. It's best here to select "Full clone", this ensures a clean separation between the original and new virtual machines.

Once the new clone is ready you can spin it up and login (I used my 'demo' user from earlier). Since the master and slaves will need the same set of software installed, I can save a LOT of installation time by installing PostgreSQL and its dependencies *now* and then cloning that VM as the slaves. This can be done with

sudo portmaster -t -g databases/postgresql91-server

 Note this will prompt you to install the PostgreSQL-9.1 client, server and their dependencies:

Remember that portmaster will show you both what it plans on installing, as above, and then what it installed:

To set PostgreSQL to start at boot, edit /etc/rc.conf and add the line

postgresql_enable="YES" 

 This is also a convenient time to edit the hostname line and change it to reflect that this is a new virtual machine. I just changed the 101 to 102.

At this point I'm ready to shut down the virtual machine and make two clones that will be the slaves.  To isolate all of the PostgreSQL traffic I'm going to change the network type for the new VM to "internal network".  There is an option to set a network, I typed in "psql_test".

Note that now my virtual machine list has had FBSD_8_3_i386_102, FBSD_8_3_i386_103 and FBSD_8_3_i386_104 added to reflect the new master (102) and the two new slaves (103 and 104).

Start all three new virtual machines and login (again, I used my 'demo' user). Each VM will need an appropriate IP address added in /etc/rc.conf. The format of the entry is

ifconfig_<interface>="<ip_address> netmask <actual_netmask>"

In the case of FBSD_8_3_i386_102, the actual entry looks like this:

ifconfig_em0="10.10.10.102 net-mask 255.255.255.0"

For the other two virtual machines I used 10.10.10.103 and 10.10.10.104. The entire /etc/rc.conf for the master looks like:

Go ahead and reboot all three virtual machines at this point, just to make sure they'll come back up with their correct IP addresses and that each can ping the other two.

PostgreSQL does not create the necessary data directory and configurations at installation. It requires the user to run the PostgreSQL binary with the "initdb" option. To do this, use:

sudo /usr/local/etc/rc.d/postgresql oneinitdb

"oneinitdb" differs from "initdb" in that you don't need the line in /etc/rc.conf with "postgresql_enable" for "oneinitdb" to work. Here is it unnecessary but it is force of habit.

Now for the master configuration. Full PostgreSQL configuration is beyond the scope of this post, I just want to focus on the necessities to get replication working. First edit /usr/local/pgsql/data/postgresql.conf and add the following lines at the end of the file:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 2

This tells PostgreSQL to accept connections on any interface/IP and to allow up to two clients to pull the write-ahead log. max_wal_senders needs to reflect the number of slaves you are deploying.

Start and stop PostgreSQL so it creates the necessary WAL data -- this is a critical step or replication *will not work*. To do this, use:

sudo /usr/local/etc/rc.d/postgresql start

With PostgreSQL running, create a local admin user:

sudo -u pgsql createuser -e -E -P 

I named mine "demo". Test the new user and add a replication user, "rep_user", with a password of "reppass" and then exit the PostgreSQL client:

psql postgres
create user rep_user replication password 'reppass';
\q

To ease permissions for the next step, I'm going to set the password for the pgsql user to something I know. This can be done on each slave or, using the steps below, only on the master.

sudo passwd pgsql

Now stop PostgreSQL:

sudo /usr/local/etc/rc.d/postgresql stop

On each slave, copy the entire PostgreSQL data directory from the master. This allows PostgreSQL to start making updates on each slave using the streamed WAL (write-ahead log).

sudo -u pgsql scp -r pgsql@10.10.10.102:/usr/local/pgsql/data /usr/local/pgsql/

On each slave, edit /usr/local/pgsql/data/postgresql.conf and change the following lines:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 2

To:

wal_level = hot_standby
hot_standby = on

Then, on each slave, add /usr/local/pgsql/data/recovery.conf, with the following two lines (note: from "primary_conninfo" to "rep_pass'" is all one line, in case it wraps):

standby_mode = on
primary_conninfo = 'host=10.10.10.102 port=5432 user=rep_user password=rep_pass'

The master needs to be configured to allow the rep_user to connect from each slave. I added the following lines to /usr/local/pgsql/data/pg_hba.conf on the master:

host replication rep_user 10.10.10.103/32 md5
host replication rep_user 10.10.10.104/32 md5

Now, restart postgresql on the master and each slave with:

sudo /usr/local/etc/rc.d/postgresql start 

To test that replication is now active, on all three systems issue:

psql -l

Then, on the master, create a new database:

createdb demo

And verify on each slave that the new database was created:

psql -l

From here you can add databases, users, etc., as necessary. If you need to add more slaves (or rebuild a slave) after you already have a database server in production, no problem. Stop the PostgreSQL instance on the master, add the relevant IP to pg_hba.conf, configure the new slave as described above, start PostgreSQL on the slave and restart it on the master.