I'm working on setting up a two-node PostgreSQL 9.4 environment. Right now I have test instances running on port 5532 on two identical Centos servers. node1 is correctly sending logs to a shared directory, and node2 is a hot_standby, and it's correctly reading and processing those logs.
I want to test failover. Here are the steps I followed:
- issue a checkpoint on node1 (run psql as postgres user, type "checkpoint;")
- stop the instance on node1 (systemctl stop postgresql-9.4-test.service)
- promote the instance on node2 (pg_ctl -D path_to_directory promote)
All of these steps seems to work. But when I try to create a table on node2, I get "ERROR: cannot execute CREATE TABLE AS in a read-only transaction" , and if I look at the settings for node2, it tells me that hot_standby is on.
The only change that I can see in node2's directory is that there's now a zero-length file called "promote".
What am I missing? I don't see anything in the documentation about any other steps to follow in order to promote a standby server.
Edited to add: select pg_is_in_recovery() returns true.
I didn't create a trigger file because that's not supposed to be required if you're using pg_ctl promote.
hot_standby
can be on even when acting as a master. What you need to check isSELECT pg_is_in_recovery()
. If true, then it's running as a read-replica.