背景
如何通過SQL接口直接關閉資料庫，或者重新開機資料庫？
關閉和重新開機資料庫是一個風險較大的操作，如果能通過SQL來關閉，重新開機資料庫，當然是很難想象的，因為SQL通常是使用接口，而不是管理接口。當然并不是資料庫做不到通過SQL管理資料庫，而是這确實是風險較大且并不是資料庫核心的能努力。
但是為了友善管理，資料庫還是提供了很多管理函數（通過SQL調用）。例如：
https://www.postgresql.org/docs/11/functions-info.html那麼能不能通過SQL接口來關閉，或者重新開機資料庫呢？（通常我們需要登陸到資料庫所在的作業系統，執行pg_ctl來實作）
關閉資料庫的底層實作
實際上關閉資料庫是往postgres master程序（資料庫啟動時的父程序）發送信号，程序在收到信号後會進行相應的操作。可以通過看postmaster.c代碼或通過man postgres得到這個資訊：
man postgres
To terminate the postgres server normally, the signals SIGTERM, SIGINT, or SIGQUIT can be used.
The first will wait for all clients to terminate before quitting,
the second will forcefully disconnect all clients,
and the third will quit immediately without proper shutdown, resulting in a recovery run during restart. 如何獲得postmaster程序pid呢？
直接讀postmaster.pid檔案即可得到：
postgres=# select * from pg_read_file('postmaster.pid');
pg_read_file
----------------------------
30503 +
/data01/digoal/pg_root8001+
1549031862 +
8001 +
. +
0.0.0.0 +
8001001 39288833 +
ready +
(1 row) 30503 為postmaster程序的PID。
關閉資料庫就是往這個PID發送信号(SIGTERM 正常關閉, SIGINT 快速關閉, or SIGQUIT 暴力關閉)。
發送信号給資料庫程序
src/backend/utils/adt/misc.c
1、發送給postmaster程序SIGHUP信号，用于reload conf。
/*
* Signal to reload the database configuration
*
* Permission checking for this function is managed through the normal
* GRANT system.
*/
Datum
pg_reload_conf(PG_FUNCTION_ARGS)
{
if (kill(PostmasterPid, SIGHUP))
{
ereport(WARNING,
(errmsg("failed to send signal to postmaster: %m")));
PG_RETURN_BOOL(false);
}
PG_RETURN_BOOL(true);
} 2、發送給普通程序，用于cancel query或terminate session
/*
* Signal to terminate a backend process. This is allowed if you are a member
* of the role whose process is being terminated.
*
* Note that only superusers can signal superuser-owned processes.
*/
Datum
pg_terminate_backend(PG_FUNCTION_ARGS)
{
int r = pg_signal_backend(PG_GETARG_INT32(0), SIGTERM);
if (r == SIGNAL_BACKEND_NOSUPERUSER)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg("must be a superuser to terminate superuser process"))));
if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg("must be a member of the role whose process is being terminated or member of pg_signal_backend"))));
PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
} /*
* Signal to cancel a backend process. This is allowed if you are a member of
* the role whose process is being canceled.
*
* Note that only superusers can signal superuser-owned processes.
*/
Datum
pg_cancel_backend(PG_FUNCTION_ARGS)
{
int r = pg_signal_backend(PG_GETARG_INT32(0), SIGINT);
if (r == SIGNAL_BACKEND_NOSUPERUSER)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg("must be a superuser to cancel superuser query"))));
if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg("must be a member of the role whose query is being canceled or member of pg_signal_backend"))));
PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
} src/backend/utils/adt/misc.c
/*
* Send a signal to another backend.
*
* The signal is delivered if the user is either a superuser or the same
* role as the backend being signaled. For "dangerous" signals, an explicit
* check for superuser needs to be done prior to calling this function.
*
* Returns 0 on success, 1 on general failure, 2 on normal permission error
* and 3 if the caller needs to be a superuser.
*
* In the event of a general failure (return code 1), a warning message will
* be emitted. For permission errors, doing that is the responsibility of
* the caller.
*/
#define SIGNAL_BACKEND_SUCCESS 0
#define SIGNAL_BACKEND_ERROR 1
#define SIGNAL_BACKEND_NOPERMISSION 2
#define SIGNAL_BACKEND_NOSUPERUSER 3
static int
pg_signal_backend(int pid, int sig)
{
。。。
if (proc == NULL)
{
/*
* This is just a warning so a loop-through-resultset will not abort
* if one backend terminated on its own during the run.
*/
ereport(WARNING,
(errmsg("PID %d is not a PostgreSQL server process", pid)));
return SIGNAL_BACKEND_ERROR;
}
。。。 PG内部并沒有開放一個SQL接口來停庫，是以我們需要自己寫一個
vi pg_fast_stop.c
#include <signal.h>
#include "fmgr.h"
#include "postgres.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(pg_fast_stop);
Datum
pg_fast_stop(PG_FUNCTION_ARGS)
{
if (kill(PostmasterPid, SIGINT))
{
ereport(WARNING,
(errmsg("failed to send signal to postmaster: %m")));
PG_RETURN_BOOL(false);
}
PG_RETURN_BOOL(true);
} gcc -O3 -Wall -Wextra -I /home/digoal/postgresql-11.1/src/include -g -fPIC -c ./pg_fast_stop.c -o pg_fast_stop.o
gcc -O3 -Wall -Wextra -I /home/digoal/postgresql-11.1/src/include -g -shared pg_fast_stop.o -o libpg_fast_stop.so
cp libpg_fast_stop.so $PGHOME/lib/ psql
create or replace function pg_fast_stop() returns int as '$libdir/libpg_fast_stop.so', 'pg_fast_stop' language C STRICT; 試用：
postgres=# select pg_fast_stop();
pg_fast_stop
------------
1
(1 row)
資料庫已關機
postgres=# \dt
FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q 如何實作SQL接口重新開機資料庫呢？
因為往POSTMASTER PID發送信号隻能關閉資料庫，無法重新開機資料庫。那麼怎麼實作重新開機呢？
1、
#restart_after_crash = on # reinitialize after backend crash?
利用普通使用者程序被KILL -9來自動重新開機，這個是postmaster守護程序自動執行的重新開機動作。
2、利用plsh存儲過程語言，直接調用pg資料庫作業系統的pg_ctl指令來重新開機。
https://github.com/petere/plsh參考
https://www.postgresql.org/docs/11/functions-admin.htmlPostgreSQL 許願連結
您的願望将傳達給PG kernel hacker、資料庫廠商等, 幫助提高資料庫産品品質和功能, 說不定下一個PG版本就有您提出的功能點. 針對非常好的提議，獎勵限量版PG文化衫、紀念品、貼紙、PG熱門書籍等，獎品豐富，快來許願。
開不開森.
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)