Contents

pg_promote()

Function to promote a physical standby server

pg_promote() is a PostgreSQL function to promote a physical standby to primary, or more precisely take the standby out of recovery mode and into read-write mode.

pg_promote() was added in PostgreSQL 12.

Usage

pg_promote ( wait boolean DEFAULT true, wait_seconds integer DEFAULT 60 ) → boolean

pg_promote() provides an alternative, SQL-based method of executing pg_ctl promote.

Change history

Examples

Execution example for pg_promote():

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

postgres=# SELECT pg_promote();
 pg_promote
------------
 t
(1 row)

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

Corresponding log output:

[2018-11-27 22:33:47 UTC] psql postgres postgres LOG:  00000: statement: SELECT pg_promote();
[2018-11-27 22:33:47 UTC] psql postgres postgres LOCATION:  exec_simple_query, postgres.c:1045
[2018-11-27 22:33:47 UTC]    LOG:  00000: received promote request
[2018-11-27 22:33:47 UTC]    LOCATION:  CheckForStandbyTrigger, xlog.c:12189
[2018-11-27 22:33:47 UTC]    FATAL:  57P01: terminating walreceiver process due to administrator command
[2018-11-27 22:33:47 UTC]    LOCATION:  ProcessWalRcvInterrupts, walreceiver.c:169
[2018-11-27 22:33:47 UTC]    LOG:  00000: invalid record length at 0/30005E8: wanted 24, got 0
[2018-11-27 22:33:47 UTC]    LOCATION:  ReadRecord, xlog.c:4258
[2018-11-27 22:33:47 UTC]    LOG:  00000: redo done at 0/30005B0
[2018-11-27 22:33:47 UTC]    LOCATION:  StartupXLOG, xlog.c:7286
[2018-11-27 22:33:47 UTC]    LOG:  00000: last completed transaction was at log time 2018-11-27 22:33:31.648668+09
[2018-11-27 22:33:47 UTC]    LOCATION:  StartupXLOG, xlog.c:7291
[2018-11-27 22:33:47 UTC]    LOG:  00000: selected new timeline ID: 2
[2018-11-27 22:33:47 UTC]    LOCATION:  StartupXLOG, xlog.c:7416
[2018-11-27 22:33:47 UTC]    LOG:  00000: archive recovery complete
[2018-11-27 22:33:47 UTC]    LOCATION:  exitArchiveRecovery, xlog.c:5557
[2018-11-27 22:33:47 UTC]    LOG:  00000: checkpoint starting: force
[2018-11-27 22:33:47 UTC]    LOCATION:  LogCheckpointStart, xlog.c:8381
[2018-11-27 22:33:47 UTC]    LOG:  00000: database system is ready to accept connections
[2018-11-27 22:33:47 UTC]    LOCATION:  reaper, postmaster.c:2944
[2018-11-27 22:33:47 UTC]    LOG:  00000: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.301 s, sync=0.000 s, total=0.302 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16385 kB, estimate=16385 kB
[2018-11-27 22:33:47 UTC]    LOCATION:  LogCheckpointEnd, xlog.c:8463

Categories

Replication, System function

See also

pg_ctl