Below mentioned issue might be current behavior of PostgreSQL, but I am not
able to find that in documentation so I have done brief analysis as well.
Issue: cascade standby server raise errors as “FATAL: the database system
is starting up” when trying to connect a client. Cascaded standby server is
started on database which is received from Standby node using pg_basebackup
tool.
Steps to reproduce:
M host is primary, S host is standby and CS host is cascaded standby.
1. Set up postgresql-9.2beta2 on all hosts.
2. Execute command initdb on host M to create fresh database.
3. Modify the configure file postgresql.conf on host M like this：
listen_addresses = 'M'
port = 15210
wal_level = hot_standby
max_wal_senders = 4
hot_standby = on
4. modify the configure file pg_hba.conf on host M like this：
host replication repl M/24 md5
5. Start the server on host M as primary.
6. Connect one client to primary server and create a user ‘repl’
create user repl superuser password '123';
7. Use the command pg_basebackup on the host S to retrieve database
of primary host
pg_basebackup -D /opt/t38917/data -F p -x fetch -c fast
-l repl_backup -P -v -h M -p 15210 -U repl -W
8. Copy one recovery.conf.sample from share folder of package to
database folder of the host S. Then rename this file to recovery.conf
9. Modify the file recovery.conf on host S as below:
standby_mode = on
primary_conninfo = 'host=M port=15210 user=repl password=123'
10. Modify the file postgresql.conf on host S as follow:
listen_addresses = 'S'
11. Start the server on host S as standby server.
12. Use the command pg_basebackup on the host CS to retrieve database
of standby host
pg_basebackup -D /opt/t38917/data -F p -x fetch -c fast -l
repl_backup -P -v -h M -p 15210 -U repl -W
13. Modify the file recovery.conf on host CS as below:
standby_mode = on
primary_conninfo = 'host=S port=15210 user=repl
password=123'
14. Modify the file postgresql.conf on host S as follow:
listen_addresses = 'CS'
15. Start the server on host CS as Cascaded standby server node.
16. Try to connect a client to host CS but it gives error as:
FATAL: the database system is starting up
Observation based on code analysis:
In the above scenario it is observed that Start-up process has read all data
(in our defect scenario minRecoveryPoint is 5016220) till the position
5016220 and then it goes and check for recovery consistency by following
condition in function CheckRecoveryConsistency:
if (!reachedConsistency &&
XLByteLE(minRecoveryPoint, EndRecPtr) &&
XLogRecPtrIsInvalid(ControlFile->backupStartPoint))
At this point first two conditions are true but last condition is not true
because still redo has not been applied and hence backupStartPoint has not
been reset. So it does not signal postmaster regarding consistent stage.
After this it goes and applies the redo and then reset backupStartPoint and
then it goes to read next set of record. Since all records have been already
read, so it starts waiting for the new record from the Standby node. But
since there is no new record from Standby node coming so it keeps waiting
for that and it does not get chance to recheck the recovery consistent
level. And hence client connection does not get allowed.
Suggestions?
With Regards,
Amit Kapila.
