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]