Hi All,
Hope you can assist and that I am posting to the right forum.
We currently have multiple Postgresql 9 instances running with warm standby, and the replication work wonderfully.
The problem is the following, we take the slave database out of recovery and it works perfectly, but when we create a new database and setup streaming replication to point to this database it is read only and never "starts up".
"I created a test database and it did replicate across, but I could only see it once I removed recovery.conf and re-started obviously not in recovery anymore, I attached the logs at the bottom FYI"
Is this even possible, to re-create a streaming database again from an ex slave database with a base backup and simply re-creating recovery.conf before startup?
######################################
LOGS ON SLAVE
LOG: entering standby mode
LOG: record with zero length at 1D/3C000634
LOG: streaming replication successfully connected to primary
LOG: redo starts at 1D/3C000634
FATAL: the database system is starting up
######################################
recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=emr-db-1 port=5432 user=postgres password=postgres'
trigger_file = '/tmp/pgsql.trigger'
######################################
and postgresql.conf
# - Standby Servers -
hot_standby = on # "on" allows queries during recovery
# (change requires restart)
######################################
Solaris 10# postmaster --version
postgres (PostgreSQL) 9.0.9
######################################
Solaris 10# uname -a
SunOS database-machine-1 4 5.10 Generic_142910-17 i86pc i386 i86pc
#####################################
The database did not recover when we created a trigger file and I had to manually remove recovery.conf and restart the database before it started up and I was able to live queries again, obviously not in recovery anymore.
LOG: trigger file found: /tmp/pgsql.trigger
FATAL: terminating walreceiver process due to administrator command
LOG: redo done at 1D/47002BC8
LOG: last completed transaction was at log time 2012-09-10 18:33:04.734928+02
FATAL: WAL ends before consistent recovery point
LOG: startup process (PID 28326) exited with exit code 1
LOG: terminating any other active server processes
LOG: database system was interrupted while in recovery at log time 2012-09-10 18:32:21 SAST
HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
LOG: entering standby mode
LOG: redo starts at 1D/45000020
LOG: invalid record length at 1D/47002C04
LOG: streaming replication successfully connected to primary
FATAL: the database system is starting up