Set up a database server

This page describes how to set up a database server.

We will set up a psql server for ZINC and Drupal and a MySQL server for DOCK Blaster / SEA.

Replication

Note: Trigger files and restore logs could (should) be on a shared file system

   VERSION=9.2
   MASTER=10.1.2.3
   SLAVE=10.1.2.4
   USER=replication
   PASS=
   BASE=/var/lib/pgsql/$VERSION
   ARCHIVE=/srv/archive/pgsql/$VERSION
   REPLOG=$ARCHIVE/wal/

Command Order

1) On Master

   pgsql "SELECT pg_start_backup('copy-to-$SLAVE', true);"
   rsync -av --exclude postmaster.pid --exclude postgresql.conf $BASE root@$SLAVE:$BASE
   rsync -av $REPLOG $SLAVE:$REPLOG

2) On Slave

Make sure $BASE/recovery.config exists (below)

   service postgresql-$VERSION start

3) On Master

   pgsql -c "SELECT pg_stop_backup();"
   pgsql -c "SELECT pg_current_xlog_location();"

4) On Slave

   pgsql -c "SELECT pg_last_xlog_receive_location();"
   pgsql -c "SELECT pg_last_xlog_replay_location();"

Configuration File Requirements

$MASTER:$BASE/postgresql.conf

   max_wal_senders = 5
   wal_keep_segments = 32
   archive_mode = on
   archive_command = 'cp %p $REPLOG/%f'
   wal_level = hot_standby

$SLAVE:$BASE/postgresql.conf

   wal_level = hot_standby
   max_wal_senders = 5
   wal_keep_segments = 32
   archive_mode = off
   hot_standby = on

$SLAVE:$BASE/recover.conf

   standby_mode          = 'on'
   primary_conninfo      = 'host=$MASTER port=5432 user=$USER password=$PASS'
   trigger_file          = '$BASE.trigger'
   restore_command       = 'cp $REPLOG/%f %p'