On 25th of October 2018, Michael Paquier committed patch:

Add pg_promote function
 
 
This function is able to promote a standby with this new SQL-callable
function.  Execution access can be granted to non-superusers so that
failover tools can observe the principle of least privilege.
 
Catalog version is bumped.
 
Author: Laurenz Albe
 
Discussion: https://postgr.es/m/.camel@cybertec.at

While this is not earth-shattering change, it's definitely nice. It allows us to promote secondary db to standalone (future master) from plain SQL interface, with no access to shell required.

Let's see it in action. I have two instances of PostgreSQL, running on ports:

• 5120 – primary
• 5121 – secondary

We can immediately see that both work, and respond correctly:

=$ psql -X -U depesz -d depesz -p 5120 -c 'select pg_is_in_recovery()'
 pg_is_in_recovery 
-------------------
 f
(1 row)
 
=$ psql -X -U depesz -d depesz -p 5121 -c 'select pg_is_in_recovery()'
 pg_is_in_recovery 
-------------------
 t
(1 row)

Now, I can simply:

=$ psql -X -U depesz -d depesz -p 5121 -c 'select pg_promote()'
 pg_promote 
------------
 t
(1 row)
 
=$ psql -X -U depesz -d depesz -p 5121 -c 'select pg_is_in_recovery()'
 pg_is_in_recovery 
-------------------
 f
(1 row)

Of course it could fail. Replicating such case is not easy, unfortunately, so I can't show you failed promotion. But in any way – pg_promote has two optional arguments:

• boolean, defaults to true – whether the function should wait for promotion to actually happen/finish
• integer, defaults to 60 – how many seconds to wait for the promotion to happen/finish.

The idea is that if within given time promotion will not happen, function call will return false.

This is really nice, thanks to all involved.