Providing SQL access for administrative tasks seems to be a
good thing, see ALTER SYSTEM and pg_reload_conf().
In that vein, I propose a function pg_promote() to promote
physical standby servers.
If there are no fundamental objections, I'll add it to the
next commitfest.
Yours,
Laurenz Albe
From dd18d6eb38168db4d3d8c99a74d06b39e719092e Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.a...@cybertec.at>
Date: Thu, 20 Sep 2018 07:52:28 +0200
Subject: [PATCH] Add pg_promote() to promote standby servers
---
doc/src/sgml/func.sgml | 20 +++++++++++
doc/src/sgml/high-availability.sgml | 2 +-
doc/src/sgml/recovery-config.sgml | 3 +-
src/backend/access/transam/xlogfuncs.c | 48 ++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 4 +++
5 files changed, 75 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4331bebc96..7beeaeacde 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18596,6 +18596,9 @@ SELECT set_config('log_statement_stats', 'off', false);
<indexterm>
<primary>pg_terminate_backend</primary>
</indexterm>
+ <indexterm>
+ <primary>pg_promote</primary>
+ </indexterm>
<indexterm>
<primary>signal</primary>
@@ -18655,6 +18658,16 @@ SELECT set_config('log_statement_stats', 'off', false);
however only superusers can terminate superuser backends.
</entry>
</row>
+ <row>
+ <entry>
+ <literal><function>pg_promote()</function></literal>
+ </entry>
+ <entry><type>boolean</type></entry>
+ <entry>Promote a physical standby server. This function can only be
+ called by superusers and will only have an effect when run on
+ a standby server.
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -18692,6 +18705,13 @@ SELECT set_config('log_statement_stats', 'off', false);
subprocess.
</para>
+ <para>
+ <function>pg_promote()</function> sends a signal to the server that causes it
+ to leave standby mode. Since sending signals is by nature asynchronous,
+ successful execution of the function does not guarantee that the server has
+ already been promoted.
+ </para>
+
</sect2>
<sect2 id="functions-admin-backup">
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index 8cb77f85ec..6014817d9e 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -1472,7 +1472,7 @@ synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
<para>
To trigger failover of a log-shipping standby server,
- run <command>pg_ctl promote</command> or create a trigger
+ run <command>pg_ctl promote</command>, call <function>pg_promote()</function>, or create a trigger
file with the file name and path specified by the <varname>trigger_file</varname>
setting in <filename>recovery.conf</filename>. If you're planning to use
<command>pg_ctl promote</command> to fail over, <varname>trigger_file</varname> is
diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml
index 92825fdf19..d06cd0b08e 100644
--- a/doc/src/sgml/recovery-config.sgml
+++ b/doc/src/sgml/recovery-config.sgml
@@ -439,7 +439,8 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows
<para>
Specifies a trigger file whose presence ends recovery in the
standby. Even if this value is not set, you can still promote
- the standby using <command>pg_ctl promote</command>.
+ the standby using <command>pg_ctl promote</command> or calling
+ <function>pg_promote()</function>.
This setting has no effect if <varname>standby_mode</varname> is <literal>off</literal>.
</para>
</listitem>
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 9731742978..9c55ef619b 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -35,6 +35,7 @@
#include "storage/fd.h"
#include "storage/ipc.h"
+#include <unistd.h>
/*
* Store label file and tablespace map during non-exclusive backups.
@@ -697,3 +698,50 @@ pg_backup_start_time(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(xtime);
}
+
+/*
+ * Promote a standby server.
+ *
+ * A result of "true" means that promotion has been initiated.
+ */
+Datum
+pg_promote(PG_FUNCTION_ARGS)
+{
+ FILE *promote_file;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to promote standby servers")));
+
+ if (!RecoveryInProgress() || !StandbyMode)
+ PG_RETURN_BOOL(false);
+
+ /* create the promote signal file */
+ promote_file = AllocateFile("promote", "w");
+ if (!promote_file)
+ {
+ ereport(WARNING,
+ (errmsg("could not create promote file: %m")));
+ PG_RETURN_BOOL(false);
+ }
+
+ if (FreeFile(promote_file))
+ {
+ /* probably unreachable, but it is better to be safe */
+ ereport(WARNING,
+ (errmsg("could not write promote file: %m")));
+ PG_RETURN_BOOL(false);
+ }
+
+ /* signal the postmaster */
+ if (kill(PostmasterPid, SIGUSR1) != 0)
+ {
+ ereport(WARNING,
+ (errmsg("failed to send signal to postmaster: %m")));
+ (void) unlink("promote");
+ PG_RETURN_BOOL(false);
+ }
+
+ PG_RETURN_BOOL(true);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 860571440a..a26e5216da 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5997,6 +5997,10 @@
proname => 'pg_backup_start_time', provolatile => 's',
prorettype => 'timestamptz', proargtypes => '',
prosrc => 'pg_backup_start_time' },
+{ oid => '3436', descr => 'promote standby server',
+ proname => 'pg_promote', provolatile => 'v',
+ prorettype => 'bool', proargtypes => '',
+ prosrc => 'pg_promote' },
{ oid => '2848', descr => 'switch to new wal file',
proname => 'pg_switch_wal', provolatile => 'v', prorettype => 'pg_lsn',
proargtypes => '', prosrc => 'pg_switch_wal' },
--
2.17.1
