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.
It provides an alternative, SQL-based method of executing pg_ctl promote.
pg_promote() was introduced in PostgreSQL 12.
Change history
- PostgreSQL 12
- added (commit 10074651)
Examples
Execution example:
postgres=# SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) Time: 0.772 ms postgres=# SELECT pg_promote(); pg_promote ------------ t (1 row) Time: 100.843 ms postgres=# SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row) Time: 0.418 ms
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
References
- PostgreSQL documentation: Recovery Control Functions