Postgresql – About “pg_ctl promote” of PostgreSQL9.1

postgresql

Today I test the new feature about "replication features" of PostgreSQL9.1,
First I have installed the Hot-standby of PostgreSQL9.1 successfully. It is not a problem。
What I want to test is that I want to use command "pg_ctl start promote " to active standby node
instead of creating a trigger file 。 Yes, after I execute the command, the standby node then becomes
primary and allow write/read operations。 But the initial primary node can not switchover to the
standby role automatically. I am not sure If PostgreSQL9.1 is able to use "pg_ctl promote" do switchover
between primary and standby node 。Can any body explain this ?

Best Answer

There is no 'demote' option for pg_ctl - if the primary was cold when the standby was promoted perhaps it could be converted to a standby manually, but otherwise it will need a new base backup from the new primary.

The normal reason for switchover would be if the primary has failed so this would not be necessary.

Related Solutions

Postgres 9.4 Replication Slots – Failover Master Issues

You cannot just jump back to the old master, as it is said here:

Once failover to the standby occurs, there is only a single server in operation. This is known as a degenerate state. The former standby is now the primary, but the former primary is down and might stay down. To return to normal operation, a standby server must be recreated, either on the former primary system when it comes up, or on a third, possibly new, system.

For this, you need a recovery.conf on the old master. This should be nearly the same as the present one, only the connection has to point elsewhere.

Postgresql – pg_ctl promote is not working

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 .

Best Answer

Related Solutions

Postgres 9.4 Replication Slots – Failover Master Issues

Postgresql – pg_ctl promote is not working

Related Question