Setting up postgres warm standby

Posted on
postgresql

Setting up postgres warm standby

Mainly just notes,  I haven’t gone through this in too much detail yet…. -MB

Setup two postgres servers with the same version, both with the same data directory layout.
AS ROOT

primary> apt-get install postgresql-server nfs-kernel-server nfs-client
secondary> apt-get install  postgresql-server  nfs-kernel-server nfs-client postgresql-contrib-8.4
secondary> mkdir -p /data/dbsync/primaryarchive
primary> mkdir -p /data/dbsync-archiveto
primary> vi /etc/exports

#to temporarily allow access to the base data for getting a base
/var/lib/postgresql/8.4/main 192.168.*.*(ro,sync,no_subtree_check,no_root_squash)

secondary> vi /etc/exports

#to provide a location on the secondary,  that the primary can write WAL logs to
/data/dbsync/primaryarchive 192.168.*.*(rw,sync,no_subtree_che

primary> vi /etc/fstab

SECONDARYSERVERIP:/data/dbsync/primaryarchive  /data/dbsync-archiveto nfs ro 0 1

primary> mount   /data/dbsync-archiveto
primary> vi /etc/postgresql/8.4/main/postgresql.conf

#to turn on archiving from primary to the secondary
-archive_mode = on
-archive_command ‘cp %p /data/dbsync-archiveto/%f’

secondary>mkdir -p /mnt/livedb
secondary> mount PRIMARYSERVERIP:/var/lib/postgresql/8.4/main/ /mnt/livedb

 

At this point we should have a postgres db running on both primary and secondary in /var/lib/postgresql/8.4/main
we should have a mount on the primary pointing to the secondary and WAL logs being writted to the secondary.  If the mount to the secondary fails the WAL Logs would build up on the primary in /var/lib/postgresql/8.4/main/pg_xlog.
we should have a mount on the secondary pointing to the base install of the primary database so we can copy the base

secondary> /etc/init.d/postgresql stop
secondary> rsync -Cqtar –delete /mnt/livedb/* /var/lib/postgresql/8.4/main/.
secondary> vi /var/lib/postgresql/8.4/main/recovery.conf

While that is going,  setup the configuration on secondary to be monitoring the archive directory on primary and processing the archive entries.

secondary> vi /etc/postgresql/8.4/main/postgresql.conf

restore_command = ‘/usr/lib/postgresql/8.4/bin/pg_standby -d -t /tmp/pgsql.trigger.5432 /data/dbsync/primaryarchive %f %p %r 2>>/var/log/postgresql/standby.log’
recovery_end_command = ‘rm -f /tmp/pgsql.trigger.5432’

 

 

 

———————

(the above is ideal,  but sometimes NFS mount issues cause problems,  here are some shortcuts I have used….)

– use the mount from primary to secondary to do the initial base transfer.

  1. On the secondary stop postgres (/etc/init.d/postgresql stop)
  2. mv /var/lib/postgresql/8.4/main to data/dbsync/primaryarchive ( /data/dbsync/primaryarchive/main)
  3. from the primary start a backup and sync to the secondary
  4. primary> psql -Upostgres -c “select pg_start_backup(‘backmeup’)” && time rsync –delete -Ctar /var/lib/postgresql/8.4/main/* /data/dbsync-archiveto/main/. –progress –exclude=’*pg_xlog/*’ && psql -Upostgres -c  “select pg_stop_backup()”
  5. mv main back to the running location  (mv /data/dbsync/primary/main /var/lib/postgresql/8.4/.)
  6. make sure all files are property owned by postgres.postgres (chown postgres.postgres -R /var/lib/postgresql/8.4/main)
  7. vi /var/lib/postgresql/8.4/main/recovery.conf
  8. restore_command = ‘/usr/lib/postgresql/8.4/bin/pg_standby -d -t /tmp/pgsql.trigger.5432 /data/dbsync/primaryarchive %f %p %r 2>>/var/log/postgresql/standby.log’
    recovery_end_command = ‘rm -f /tmp/pgsql.trigger.5432’
  9. /etc/init.d/postgresql start