Обсуждение: Postgresql replication assistance
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?
Thank you,
Gustav
######################################
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
Gustav Potgieter wrote: > Hope you can assist and that I am posting to the right forum. Sending the question twice with the same wording does not make it clearer... > 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? If I understand you correctly, there are three computers involved: An original master, an original slave that "is taken out of recovery" (promoted?), and a new slave that is set up using the original slave as master. Right? That should work if you do it right. You quote from a number of logs, but to me it is not clear which log belongs to which server. Let's say the original master is A, the original slave and new master is B and the new slave is C. It would be interesting to see B's log from when replication is set up and starts working, B's log from the time it is promoted to master, and C's log from the time when you try to set it up as B's slave. Do you share WAL archives between the machines? If yes, how? How exactly did you promote B? Yours, Laurenz Albe