I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue. After restarting Postgres on the slave server the pgstartup log file and the daily log file under the pg_log directory reads with no errors. However, when I try to enter into the database using the psql command, I get the error FATAL: the database system is starting up. The
FATAL: the database system is starting up.
The recovery.conf file also does not turn to recovery.done. I have extensively researched this error and consistently find the same response: the database has not been cleanly shut down before I tried to restart Postgres. The only ways I have restarted Postgres is through the service postgresql-9.1 restartservice postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart/etc/init.d/postgresql-9.1 restart commands. After I receive this error, I kill all processes and again try to restart the database and still receive the same error. I am at a loss of where to go from here and how to fix this issue. Below is the exact process that I have done to complete the hot backup.
pg_hba.conf:, added the line "host replication postgres IPAddressOfSlaveServer trust":
host replication postgres IPAddressOfSlaveServer trust
postgresql.conf: wal_level is set to hot_standby, max_wal_senders = 5, listen_address = '*', port = 5432, max_wal_senders = 5, wal_keep_segments = 32
wal_level = hot_standby
max_wal_senders = 5
listen_address = '*'
port = 5432
max_wal_senders = 5
wal_keep_segments = 32
postgresql.conf: hot_standby = on
hot_standby = on
recovery.conf: standby_mode = on, primary_conninfo = host=IPAddressOfMasterServer port = 5432 user = postgres, restore_command = 'cp /var/lib/pgsql/9.1/data/pg_xlog/%f "%p"'
standby_mode = on
primary_conninfo = host=IPAddressOfMasterServer
port = 5432
user = postgres
restore_command = 'cp /var/lib/pgsql/9.1/data/pg_xlog/%f "%p"'
I change to the postgres user on the master server and run the commands
psql -c "Select pg_start_backup('label',true);";
rsync -a -v -e ssh /var/lib/pgsql/9.1/data slave:/var/lib/pgsql/9.1/data --exclude postmaster.pid
pgsql -c "select pg_stop_backup();";
psql -c "Select pg_start_backup('label',true);";
rsync -a -v -e ssh /var/lib/pgsql/9.1/data slave:/var/lib/pgsql/9.1/data \
--exclude postmaster.pid
pgsql -c "select pg_stop_backup();";
I restart the slave server and the start up does not fail. The pgstartup.log reads
Success. You can now start the database server using: /usr/pgsql-9.1/bin/postgres -D /var/lib/pgsql/9.1/data or /usr/pgsql/9.1/bin/pg_ctl -D /var/lib/pgsql/9.1/data -l logfile start
Success. You can now start the database server using:
/usr/pgsql-9.1/bin/postgres -D /var/lib/pgsql/9.1/data
or
/usr/pgsql/9.1/bin/pg_ctl -D /var/lib/pgsql/9.1/data -l logfile start
the current day log file, postgresql-Thu.log, reads:
Log: shutting down
Log: Database system is shut down
Log: database system was shut down in recovery at 2012-4-10
Log: entering standby mode
Log: restored log file "logFileName" from archive
Log: consistent recovery state reached at 0/BF0000B0
Log: redo starts at 0/BF000020
Log: restored log file "logFileName" from archive
Log: unexpected pageaddr 0/85000000 in log file 0, segment 192, offset 0
Log: unexpected pageaddr 0/85000000 in log file 0, segment 192, offset 0
Log: streaming replication successfully connected to primary
Log: shutting down
Log: Database system is shut down
Log: database system was shut down in recovery at 2012-4-10
Log: entering standby mode
Log: restored log file "logFileName" from archive
Log: consistent recovery state reached at 0/BF0000B0
Log: redo starts at 0/BF000020
Log: restored log file "logFileName" from archive
Log: unexpected pageaddr 0/85000000 in log file 0, segment 192, offset 0
Log: unexpected pageaddr 0/85000000 in log file 0, segment 192, offset 0
Log: streaming replication successfully connected to primary
