1

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.

  • 1
    hot_standby can be on even when acting as a master. What you need to check is SELECT pg_is_in_recovery(). If true, then it's running as a read-replica. – Craig Ringer Aug 25 '15 at 1:51
  • Did you try to create the "trigger_file" which is defined in recovery.conf ? – Sahap Asci Aug 25 '15 at 6:52
1

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 .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.