Asides

Two step recipe – upgrading from Postgres 8.4 to 9.3 and then implementing 9.3 hot_standby replication

Upgrade and existing postgresql database from Postgres 8.4 to 9.3 and then implementing a 9.3 hot_standby replication server so all backups and slow select queries can run from it.

The setup: two servers,   the current primary database server (will continue to be the primary database server when using 9.3,  but we will call it the master in the replication)
First get and install postgres 9.3 using the postgres apt repositories

master and standby> vi /etc/apt/sources.list
   - deb http://apt.postgresql.org/pub/repos/apt/ UNAME-pgdg main
#UNAME EXAMPLES: precise, squeeze, etch, etc
master and standby> apt-get update
master> apt-get install postgresql-9.3 postgresql-client-9.3 nfs-kernel-server nfs-client
standby> apt-get install postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3 nfs-kernel-server nfs-client

Next create some shared directorys via nfs for file based archiving

standby> mkdir -p /data/dbsync/primaryarchive master> mkdir -p /data/dbsync-archiveto master> vi /etc/exports
   - /var/lib/postgresql/9.3/main 192.168.*.*(ro,sync,no_subtree_check,no_root_squash)
standby> vi /etc/exports
   - /data/dbsync/primaryarchive 192.168.*.*(rw,sync,no_subtree_che
master> vi /etc/fstab
   - SECONDARYSERVERIP:/data/dbsync/primaryarchive /data/dbsync-archiveto nfs ro 0 1
standby>mkdir -p /mnt/livedb
standby> mount PRIMARYSERVERIP:/var/lib/postgresql/8.4/main/ /mnt/livedb
master> mount /data/dbsync-archiveto

Now,  configure postgres on the master to allow replication and restart,   put it on port 5433 so there are no conflictw with 8.4

master> vi /etc/postgresql/9.3/main/pg_hba.conf - host replication all SECONDARYSERVERIP trust
master> vi /etc/postgresql/9.3/main/postgresql.conf
  - wal_level=hot_standby
  - archive_mode = on
  - port = 5433
  - archive_command = 'test -f /data/dbsync-archiveto/archiveable && cp %p /data/dbsync-archiveto/%f'
master> /etc/init.d/postgresql restart

Configure postgres on the standby to allow it to run as a hot_standby

standby> vi /etc/postgresql/9.3/main/postgresql.conf
  -restore_command = a018/usr/lib/postgresql/9.3/bin/pg_standby -d -t /tmp/pgsql.trigger.5432 /data/dbsync/primaryarchive %f %p %r 2>>/var/log/postgresql/standby.log
  -recovery_end_command = a018rm -f /tmp/pgsql.trigger.5432
  - wal_level=hot_standby
  - hot_standby = on
standby> /etc/init.d/postgresql stop

Now lets get a base backup on the standby

standby> mv /var/lib/postgresql/9.3/main /var/lib/postgresql/9.3/main.old
standby>cd /var/lib/postgres/9.3; mv main main.old;
standby> pg_basebackup -D main -R -h192.168.120.201 -p5433 -x -Upostgres
standby> chown postgres.postgres main/ -R
standby> /etc/init.d/postgres start

Thats it!!,  you should not have a working replication server

primary> create table tmp as select now();
secondary> select * from tmp;

#check the progress several ways. postregres log,  which files and recovery are running and by being able to connect and see updates from the master,  on the secondary

standby> tail /var/log/postgresql/postgresql-9.3-main.log
standby> grep 'database system is ready to accept read only connections'
standby> ps ax|grep post
 - postgres: wal receiver process streaming 3/43000000
master> psql -Upostgres -c 'select pg_switch_xlog()'
and the log file would switch in the recovery file
standby> ps ax|grep post
- postgres: startup process recovering 000000010000000300000037
That was all to make sure that the replication is working on 9.3,   now that I am comfortable with it working,  I am going to turn off the replication,  copy the data from 8.4 to 9.3 and recreate the replication
First lets stop the postgresql daemon on the standby server so the VERY heavy load from copying the db from 8.4 to 9.3 is not duplication

standby> /etc/init.d/postgresql stop

Next,  copy the database from 8.4 to 9.3,   I have heard there may be some problems for conversion of some objects between 8.4 and 9.3 but not for me,  this went great.

master> pg_dump -C -Upostgres mydatabase| psql -Upostgres -p5433

Once that is successful,   lets switch ports on the 9.3 and 8.4 servers so 9.3 can take over

master>vi /etc/postgresql/9.3/main/postgresql.conf
  - port = 5432
master>vi /etc/postgresql/8.4/main/postgresql.conf
  - port = 5433
master> /etc/init.d/postgres reload
Last step, get a base backup and start again.
standby> mv /var/lib/postgresql/9.3/main /var/lib/postgresql/9.3/main.old 
standby>cd /var/lib/postgres/9.3; mv main main.old;
standby> pg_basebackup -D main -R -h192.168.120.201 -x -Upostgres
standby> chown postgres.postgres main/ -R
standby> /etc/init.d/postgres start
standby> rm /var/lib/postgres/9.3/main.old* -rf

Now..... to figure out what to do with the archivedir method we are currently using.....  It seems that it is just building up  when do we use it?