Hi List!
On an originally designated hot_standby that had been not so hot for the last 24h i tried to replay the wal_files left over from a (seemingly corrupted) master server [missing clog... ]."/etc/postgresql/9.1/main/postgresql.conf" 584L, 19942C written
root@pgstandby7:/var/lib/postgresql/9.1# /etc/init.d/postgresql restart
Restarting PostgreSQL 9.1 database server: main2013-07-30 06:18:20 UTC LOG: database system was interrupted while in recovery at log time 2013-07-29 09:39:03 UTC
2013-07-30 06:18:20 UTC HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
2013-07-30 06:18:20 UTC LOG: entering standby mode
2013-07-30 06:18:20 UTC LOG: incomplete startup packet
2013-07-30 06:18:20 UTC LOG: restored log file "000000010000027A0000002B" from archive
2013-07-30 06:18:20 UTC LOG: redo starts at 27A/2B000078
2013-07-30 06:18:21 UTC FATAL: the database system is starting up
2013-07-30 06:18:21 UTC FATAL: the database system is starting up
2013-07-30 06:18:21 UTC LOG: restored log file "000000010000027A0000002C" from archive
2013-07-30 06:18:22 UTC FATAL: the database system is starting up
2013-07-30 06:18:22 UTC LOG: restored log file "000000010000027A0000002D" from archive
2013-07-30 06:18:22 UTC FATAL: the database system is starting up
2013-07-30 06:18:22 UTC LOG: restored log file "000000010000027A0000002E" from archive
2013-07-30 06:18:22 UTC LOG: consistent recovery state reached at 27A/2E3F42E8
2013-07-30 06:18:22 UTC PANIC: _bt_restore_page: cannot add item to page
2013-07-30 06:18:22 UTC CONTEXT: xlog redo split_r: rel 1663/16405/797541 left 4743, right 18008, next 9681, level 0, firstright 194
2013-07-30 06:18:22 UTC LOG: startup process (PID 11637) was terminated by signal 6: Aborted
2013-07-30 06:18:22 UTC LOG: terminating any other active server processes
The PostgreSQL server failed to start. Please check the log output: 2013-07-30 06:18:20 UTC LOG: database system was interrupted while in recovery at log time 2013-07-29 09:39:03 UTC 2013-07-30 06:18:20 UTC HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. 2013-07-30 06:18:20 UTC LOG: entering standby mode 2013-07-30 06:18:20 UTC LOG: incomplete startup packet 2013-07-30 06:18:20 UTC LOG: restored log file "000000010000027A0000002B" from archive 2013-07-30 06:18:20 UTC LOG: redo starts at 27A/2B000078 2013-07-30 06:18:21 UTC FATAL: the database system is starting up 2013-07-30 06:18:21 UTC FATAL: the database system is starting up 2013-07-30 06:18:21 UTC LOG: restored log file "000000010000027A0000002C" from archive 2013-07-30 06:18:22 UTC FATAL: the database system is starting up 2013-07-30 06:18:22 UTC LOG: restored log file "000000010000027A0000002D" from archive 2013-07-30 06:18:22 UTC FATAL: the database system is starting up 2013-07-30 06:18:22 UTC LOG: restored log file "000000010000027A0000002E" from archive 2013-07-30 06:18:22 UTC LOG: consistent recovery state reached at 27A/2E3F42E8 2013-07-30 06:18:22 UTC PANIC: _bt_restore_page: cannot add item to page 2013-07-30 06:18:22 UTC CONTEXT: xlog redo split_r: rel 1663/16405/797541 left 4743, right 18008, next 9681, level 0, firstright 194 2013-07-30 06:18:22 UTC LOG: startup process (PID 11637) was terminated by signal 6: Aborted 2013-07-30 06:18:22 UTC LOG: terminating any other active server processes ... failed!
failed!
root@pgstandby7:/var/lib/postgresql/9.1# sha1sum /home/validad-pg-backups/pgmaster/wal_files/000000010000027A0000002E.gz
5e9390c165b0885f165ed2bceafdd88692994b8a /home/validad-pg-backups/pgmaster/wal_files/000000010000027A0000002E.gz
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
#custom_variable_classes = '' # list of custom variable class names
listen_addresses = '*' # what IP address(es) to listen on;
max_connections = 25 # (change requires restart)
timezone = 'Etc/UTC'
shared_buffers = 250MB # min 128kB
maintenance_work_mem = 20MB
checkpoint_completion_target = 0.9
effective_cache_size = 200MB
hot_standby = off # "on" allows queries during recovery
max_standby_archive_delay = 90min # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
max_standby_streaming_delay = 50min # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
data_directory = '/var/lib/postgresql/9.1/not_main' # use data in another directory
root@pgstandby7:/var/lib/postgresql/9.1# cat not_main/recovery.conf
# Note that recovery.conf must be in $PGDATA directory.
# Specifies whether to start the server as a standby. In streaming replication,
# this parameter must to be set to on.
standby_mode = 'on'
# Specifies a connection string which is used for the standby server to connect
# with the primary.
primary_conninfo = 'host=pgmaster7.ipv6.oo.net port=5432 user=pgu_replicate password=passwordpasswordpassword'
# Specifies a command to load archive segments from the WAL archive. If
# wal_keep_segments is a high enough number to retain the WAL segments
# required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base backup.
restore_command = 'cp /home/validad-pg-backups/pgmaster/wal_files/%f.gz /tmp/%f.gz && gunzip /tmp/%f.gz && mv /tmp/%f %p'
# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/var/lib/postgresql/9.1/main/stop_replication_trigger'
ii postgresql-9.1 9.1.9-1 amd64 object-relational SQL database, version 9.1 server
thank you,
klaus
