PostgreSQL 9.0 replication
==========================
:author: Dan Scott
:copyright: 2011 Laurentian University
:backend: slidy
:data-uri:
:max-width: 45em
:icons:
:duration: 60

This article is licensed under a http://creativecommons.org/licenses/by-sa/3.0/[Creative Commons, Attribution, Share Alike license].

Many PostgreSQL 9.0 replication tutorials set up both log shipping and
streaming replication. One of the advantages of streaming replication is that
it is much simpler to set up than log shipping, so this overview focuses only
on streaming replication.

These instructions assume that your master / slave configuration is as follows;
adjust accordingly to match your environment:

  * PostgreSQL master = 192.168.56.101
  * PostgreSQL slave = 192.168.56.102

To set up streaming replication for Evergreen, perform the following steps:

  1. Slave server needs to have contrib / plperl packages installed, per
     Makefile.install prerequisites. You do not need to create the database
     or schema on the slave.
  2. On the master server, add the following entries to the bottom of
     `postgresql.conf`:
+
  wal_level = hot_standby
  max_wal_senders = 1
  checkpoint_segments = 8    
  wal_keep_segments = 8 
  listen_addresses = 'localhost,192.168.56.101'
+
  3. On the master server, create a new superuser specifically for replication
     purposes by issuing the following command as the `postgres` user:
+
[source,sql]
-----------------------------------------------------------------------
createuser -P -s replicant
-----------------------------------------------------------------------
+
  4. On the master server, modify `pg_hba.conf` to enable the new replication
     user to connect from the slave to the master server for the purposes of
     replication by adding the following connection:
+
-----------------------------------------------------------------------
host    replication   replicant   192.168.56.102/32   md5
-----------------------------------------------------------------------
+
  5. On the master server, restart the PostgreSQL server to make your changes
     take effect.
  6. Take a file-level backup of the database cluster on the master server.
    a. On the master server, connect to the database and run the
       `pg_start_backup` command:
+
[source,sql]
-----------------------------------------------------------------------
SELECT pg_start_backup('file_backup');
-----------------------------------------------------------------------
+
    b. Copy the files over to the slave server. We will do it the dumb way,
       with a simple tar / copy method.
+
[source,bash]
-----------------------------------------------------------------------
cd /var/lib/postgresql/9.0
tar cjpf master_backup.tar.bz2 main
-----------------------------------------------------------------------
+
    c. Once the files have been tarred up, tell the master server that the
       database backup is complete. Connect to the database and run the
       `pg_stop_backup` command:
+
[source,sql]
-----------------------------------------------------------------------
SELECT pg_stop_backup();
-----------------------------------------------------------------------
+
    d. On the slave server as the root user, stop PostgreSQL and replace the
       cluster with the contents of the master database backup:
+
[source,bash]
-----------------------------------------------------------------------
/etc/init.d/postgresql stop
su - postgres
cd /var/lib/postgresql/9.0
rm -fr main
sftp evergreen@192.168.56.102
> cd /var/lib/postgresql/9.0
> get master_backup.tar.bz2
> quit
tar xjf master_backup.tar.bz2
exit
-----------------------------------------------------------------------
+
  7. On the slave server, enable hot standby mode by adding the following
     line to the bottom of `postgresql.conf`:
+
-----------------------------------------------------------------------
hot_standby = 'on'
-----------------------------------------------------------------------
+
  8. On the slave server as the postgres user, tell the slave how to connect
     to the master server by creating the file
     `/var/lib/postgresql/9.0/main/recovery.conf` with the following contents:
+
-----------------------------------------------------------------------
standby_mode = 'on'
primary_conninfo = 'host=192.168.56.101 port=5432 user=replicant password=pass'
-----------------------------------------------------------------------
+
   9. On the slave server as the root user, start PostgreSQL. This command
      will take some time as it recovers from the file-level backup:
+
[source,bash]
-----------------------------------------------------------------------
/etc/init.d/postgresql start
-----------------------------------------------------------------------
+

Check the contents of `/var/log/postgresql/postgresql-9.0-main.log`; if all
has gone well, you should see the messages 'streaming replication successfully
connected to primary' and 'consistent recovery state reached'.

Now do some work on your master server and connect to the slave server to
ensure that the changes are being applied to the slave.

Further reading
---------------
  * http://www.postgresql.org/docs/9.0/interactive/high-availability.html[PostgreSQL documentation: Chapter 25. High Availability, Load Balancing, and Replication]
  * http://brandonkonkle.com/blog/2010/oct/20/postgres-9-streaming-replication-and-django-balanc/[Postgres 9 Streaming Replication and Django-Balancer]
  * http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial[Binary Replication Tutorial]