5 Minutes to Binary Replication

The official documentation on how to set up binary replication (or "hot standby") for version 9.0 are kind of daunting. So I thought I'd give you the 5 minute replication setup version: if you have a very simple setup and you can afford to shut down the master database, getting replication running can be simple indeed.

5 Minutes to Simple Replication

This is the easiest way to set up replication between a master and standby. It requires shutting down the master; other methods are detailed later in this guide.

Prerequisites

You must have the right setup to make this work:

2 servers with similar operating systems (e.g both Linux 64-bit).

The same release of PostgreSQL 9.0 installed on both servers.

PostgreSQL superuser shell access on both servers.

Knowledge of how to start, stop and reload Postgres.

PostgreSQL 9.0 running on Server1.

A database created and loaded on Server1.

A postgres user or root user who has network

This 6-step guide, and all of the examples in this tutorial, assume that you have a master server at 192.168.0.1 and a standby server at 192.168.0.2 and that your database and its configuration files are installed at /var/lib/postgresql/data. Replace those with whatever your actual server addresses and directories are.

2. Edit pg_hba.conf on the master in order to let the standby connect.
host replication all 192.168.0.2/32 trust

3. Edit recovery.conf and postgresql.conf on the standby to start up replication and hot standby. First, in postgresql.conf, change this line:
hot_standby = on

Then create a file in the same directory as postgresql.conf, called recovery.conf, with the following lines:
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.1'

4. Shutdown the master and copy the files. You want to copy most but not all files between the two servers, excluding the configuration files and the pg_xlog directory. An example rsync script would be:
rsync -av --excude pg_xlog --exclude postgresql.conf data/* 192.168.0.2:/var/lib/postgresql/data/

5. Start the standby first, so that they can't get out of sync.

6. Start the master.

And you're replicating!

Now, there's a lot more to it, especially if you need to add standbys with the master running, or fail over, or keep standbys from getting behind, or load balance, and similar topics. For that reason, I've started a tutorial on the PostgreSQL Wiki. Will continue working on it as I have time.

Disclaimer: Blog contents express the viewpoints of their independent authors and
are not reviewed for correctness or accuracy by
Toolbox for IT. Any opinions, comments, solutions or other commentary
expressed by blog authors are not endorsed or recommended by
Toolbox for IT
or any vendor. If you feel a blog entry is inappropriate,
click here to notify
Toolbox for IT.