Unable to setup hot-standy for Postgresql 9.0.2 "seeded"

Hi Everyone,

I am trying to setup a ‘hot-standby’ Postgresql 9.0.2 server on CentOS 5.5/x86_64.   The steps below worked fine for the first pair of servers I setup (DB was about 20GB in size).  The second pair of servers is causing me a problem to get the “hot-standby” server seeded.   Primary server is setup for continuous archiving.  WAL files are being stored in NFS server.  According to the documentation we need to create a baseline backup we can use to seed the standby server.  I have s simple script designed to do that:

-bash-3.2$ cat make_backup.sh

#!/bin/bash

#

##############

echo "Start of backup for 'seeding' hot-standby server...."

date

psql -c "select pg_start_backup('hotstandby');"

echo " ... tarring the /var/lib/pgsql/9.0/data area..."

date

tar -jcf /tmp/pgsql90-backup.tar /var/lib/pgsql/9.0/data/

echo " Finished tar, stop backup mode "

date

psql -c 'select pg_stop_backup();'

exit

This process runs without an error (takes about 5 hours to create a 6.8 GB compressed tar-ball from an original postgres DB which is about 70 GB.

On target standby server make sure the same version of Postgresql is installed (9.0.2 64-bit).   Start with empty /var/lib/pgsql/9.0/data directory and unpack the above tar-ball from /.    Once it finishes create an updated postgresql.conf which has ‘hot_standby = on’ and a recovery.conf:

standby_mode = 'on'

primary_conninfo = 'host=10.10.1.44 port=5432 user=hsuser password=xxxxxxxxxxx'

restore_command = 'cp /atlas/data08/postgres/archive/%f %p'

trigger_file = '/var/lib/pgsql/9.0/data/stop_recovery_head01'

archive_cleanup_command = '/usr/pgsql-9.0/bin/pg_archivecleanup /atlas/data08/postgres/archive %r'

I move the /var/lib/pgsql/9.0/pg_xlog to /var/lib/pgsql/9.0/pg_xlog.orig and create an “empty” ‘pg_xlog’ directory owned by ‘postgres’ (FYI, tried without doing this as well, just leaving the copied pg_xlog in place…same error below happens).

Starting postgresql-9.0 seems to work but dies right way.  The log file looks like:

[root@head01 /]# cat /var/lib/pgsql/9.0/data/pg_log/postgresql-2011-01-29_203713.log

LOG:  database system was interrupted; last known up at 2011-01-29 12:41:40 EST

LOG:  creating missing WAL directory "pg_xlog/archive_status"

LOG:  entering standby mode

LOG:  restored log file "000000010000004800000042" from archive

LOG:  restored log file "00000001000000480000003F" from archive

LOG:  file "pg_multixact/offsets/0016" doesn't exist, reading as zeroes

LOG:  file "pg_multixact/members/002D" doesn't exist, reading as zeroes

LOG:  redo starts at 48/3F000020

FATAL:  could not access status of transaction 65255669

DETAIL:  Could not read from file "pg_subtrans/03E3" at offset 188416: Success.

CONTEXT:  xlog redo xid assignment xtop 65255667: subxacts: 65255669 65255757 65255758 65255782 65255784 65255785 65255786 65255787 65255788 65255789 65255790 65255799 65255804 65255805 65255818 65255819 65255824 65255825 65255890 65255891 65255892 65255893 65255909 65255910 65255911 65255912 65255936 65255937 65256085 65256086 65256101 65256105 65256106 65256107 65256109 65256111 65256121 65256126 65256159 65256160 65256164 65256167 65256168 65256227 65256228 65256243 65256244 65256284 65256299 65256300 65256379 65256407 65256450 65256451 65256452 65256477 65256478 65256507 65256553 65256554 65256599 65256600 65256707 65256709

LOG:  startup process (PID 3308618) exited with exit code 1

LOG:  terminating any other active server processes

Not sure what is wrong.  Problem has occurred with three different backup attempts.  It seems like it assumes the pg_subtrans contains more records than it really does.  I would have thought the make_backup.sh script would have prevented such a problem.

Any suggestions for how to get this working?

Shawn McKee/University of Michigan ATLAS Group