Postgresql – pg_ctl promote is not working

postgresql

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.

Best Answer

I think I have found the answer: this only works with streaming replication.

My original setup used WAL replication. That was working, except for the promotion. I even tried adding a trigger file, and that didn't help.

Then I changed my setup to use asynchronous streaming replication, and pg_ctl promote worked! When I looked in the log on the secondary server, I saw 

received promote request
redo done at 86/8A000028
last completed transaction was at log time 2015-09-14 16:15:46.316812-04
selected new timeline ID: 2
archive recovery complete
autovacuum launcher started
database system is ready to accept connections

and when I ran "select pg_is_in_recovery()" on that server, it returned false.

Also, I see that recovery.conf has been renamed to recovery.done .

Related Question