This article is licensed under a 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:

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: 
    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.
    1. On the master server, connect to the database and run the pg_start_backup command: 
      SELECT pg_start_backup('file_backup');
    2. Copy the files over to the slave server. We will do it the dumb way, with a simple tar / copy method. 
      cd /var/lib/postgresql/9.0
tar cjpf master_backup.tar.bz2 main
    3. 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: 
      SELECT pg_stop_backup();
    4. On the slave server as the root user, stop PostgreSQL and replace the cluster with the contents of the master database backup: 
      /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: 
    /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