pg9.6进程架构

进程架构

1.1 进程父子关系

PostgreSQL 的进程架构由多个后端进程组成，其父进程是 postmaster。进程 ID 记录在 {PGDATA}/postmaster.pid 文件中。当实例成功关闭时，该文件将被删除。数据库客户端与 postmaster 进程正在侦听的端口建立连接。

图1 进程父子关系

在下面的示例中，进程 ID 2680 是 postmaster 进程。很明显，所有其他进程是 postmaster 的子进程。 Postmaster 进程接收来自客户端的连接并对其进行身份验证。然后postmaster启动postgres进程作为子进程执行SQL

示例1 处理父子关系

$ ps -ef | grep postgres | grep -v grep

postgres 2680 1 0 10:25 ? 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data

postgres 2681 2680 0 10:25 ? 00:00:00 postgres: logger process

postgres 2683 2680 0 10:25 ? 00:00:00 postgres: checkpointer process

postgres 2684 2680 0 10:25 ? 00:00:00 postgres: writer process

postgres 2685 2680 0 10:25 ? 00:00:00 postgres: wal writer process

postgres 2686 2680 0 10:25 ? 00:00:00 postgres: autovacuum launcher process

postgres 2687 2680 0 10:25 ? 00:00:00 postgres: stats collector process

1.2 进程名称

PostgreSQL 实例如上所述配置了多个进程。通过将参数 update_process_title 指定为“on”（默认值：“on”），部分进程名称会发生变化。 ps 命令中引用的每个进程的名称如下表所示。

表 1 进程名称

进程	进程名称
postmaster	{INSTALL}/bin/postgres -D
logger	postgres: logger process
checkpointer	postgres: checkpointer process
writer	postgres: writer process
wal writer	postgres: wal writer process
autovacuum launcher	postgres: autovacuum launcher process
autovacuum worker	postgres: autovacuum worker process
archiver	postgres: archiver process last was
stats collector	postgres: stats collector process
postgres (local)	postgres: {PGUSER} {PGDATABASE} [local]
postgres (remote)	postgres: {PGUSER} {PGDATABASE} {TCP/IP (PORT)}
wal sender	postgres: wal sender process {PGUSER} {TCP/IP (PORT)} streaming
	postgres: wal sender process {PGUSER} {TCP/IP (PORT)} sending backup "{BACKUP_LABEL}"
wal receiver	postgres: wal receiver process streaming
startup process	postgres: startup process recovering
bgworker	postgres: bgworker:
parallel worker	postgres: bgworker: parallel worker for PID

如果指定参数 cluster_name，这是 PostgreSQL 9.5 的新功能，则输出已指定为进程名称一部分的字符串。以下是将“cluster1”指定为参数 cluster_name 时的示例

示例2 cluster_name 参数

$ ps –ef | grep postgres

postgres 12364 1 0 06:14 pts/0 00:00:00 /usr/local/pgsql/bin/postgres -D data

postgres 12365 12364 0 06:14 ? 00:00:00 postgres: cluster1: logger process

postgres 12367 12364 0 06:14 ? 00:00:00 postgres: cluster1: checkpointer process

postgres 12368 12364 0 06:14 ? 00:00:00 postgres: cluster1: writer process

postgres 12369 12364 0 06:14 ? 00:00:00 postgres: cluster1: wal writer process

postgres 12370 12364 0 06:14 ? 00:00:00 postgres: cluster1: autovacuum launcher process

从这个例子可以看出，在postmaster的进程名中并没有输出cluster name进程。参数 cluster_name 中可以指定的字符仅限于 ASCII 字符串 (0x20 ~ 0x7E)。其他代码在转换为问号（？）后输出。

1.3 过程与信号

您可以通过向后端进程发送特定信号来执行操作，后端进程配置实例。这里验证了对几个信号的响应动作。

SIGKILL 信号

当 postmaster 进程收到一个杀死信号。这次没有删除 postmaster.pid 文件。实例重启后虽然有如下日志记录，但实例正常启动。

示例3 异常终止后重启日志

LOG: database system was interrupted; last known up at 2017-02-11 11:12:03 JST

LOG: database system was not properly shut down; automatic recovery in progress

LOG: redo starts at 0/155E118

FATAL: the database system is starting up

FATAL: the database system is starting up

LOG: invalid record length at 0/5A5C050: wanted 24, got 0

LOG: redo done at 0/5A5C018

LOG: last completed transaction was at log time 2017-02-11 12:25:15.443492+09

LOG: MultiXact member wraparound protections are now enabled

LOG: autovacuum launcher started

LOG: database system is ready to accept connections

当postgres进程异常终止（包括接收到KILL信号），以及适当的进程，它将重置从客户端连接的所有会话。实例上运行的所有事务都被回滚，成为接收错误信号后的所有 SQL 语句。要安全地停止 postgres 进程，执行 pg_cancel_backend 函数（发送 SIGINT 信号）或 pg_terminate_backend 函数（发送 SIGTERM 信号）。

示例4 收到KILL信号后记录

LOG: server process (PID 3416) was terminated by signal 9: Killed

LOG: terminating any other active server processes

LOG: archiver process (PID 3404) exited with exit code 1

WARNING: terminating connection because of crash of another server process

DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT: In a moment you should be able to reconnect to the database and repeat your command.

LOG: all server processes terminated; reinitializing

每个后端进程收到信号时的行为如下。 SIG_IGN 表示信号忽略，并且 SIG_DFL 显示 Linux 进程的默认行为。

postgres进程接收信号时的行为

postgres进程接收信号时的操作如下。

表 2 postgres 进程的行为

Signal	Signal Handler function	Behavior
SIGHUP	SigHupHandler	Reload configuration file
SIGINT	StatementCancelHandler	Destruction of the running transactions (Processing of pg_cancel_backend function)
SIGTERM	die	Destruction of the running transactions and exit process (Processing of pg_terminate_backend function)
SIGQUIT	quickdie or die	Forced termination
SIGALRM	handle_sig_alarm	Timeout occurrence notification
SIGPIPE	SIG_IGN
SIGUSR1	procsignal_sigusr1_handler	Database recovery
SIGUSR2	SIG_IGN
SIGFPE	FloatExceptionHandler	Output ERROR log
SIGCHLD	SIG_DFL

postmaster 进程接收信号时的行为

postmaster进程接收信号时的操作如下：

表 3 postmaster 进程的行为

Signal	Signal Handler function	Behavior
SIGHUP	SIGHUP_handler	Reload configuration file Send SIGHUP signal to the child process
SIGINT	pmdie	FAST shutdown
SIGTERM	pmdie	SMART shutdown
SIGQUIT	pmdie	IMMEDIATE shutdown
SIGALRM	SIG_IGN
SIGPIPE	SIG_IGN
SIGUSR1	sigusr1_handler	Signal reception processing from the child process
SIGUSR2	dummy_handler	Does nothing
SIGCHLD	reaper	Processing at the end of a child process Restart the back-end process
SIGTTIN	SIG_IGN
SIGTTOU	SIG_IGN
SIGXFSZ	SIG_IGN

当你向 postmaster 进程发送 SIGHUP 信号时，postgresql.conf 文件（postgresql.auto.conf 和 pg_*.conf 文件，以及）被重新加载。这与执行 pg_ctl reload 命令的行为相同。输出以下日志。

示例5 重新读取配置文件的日志输出

LOG: received SIGHUP, reloading configuration files

1.4 启动和停止进程

Checkpointer, writer, and stats collector进程始终启动。其他进程的启动/停止时序如下。 postmaster 的子进程定期检查其父进程的存在，postmaster 和他们在检测到 postmaster 进程停止时终止自己的进程。

表 4 启动和停止进程

Process	Start / stop timing
logger	Start-up in the case where parameter logging_collector to "on" (default: "off")
autovacuum launcher	Start-up in the case where parameter autovacuum to "on" (default: "on")
autovacuum worker	Autovacuum launcher process is started with the interval specified by the parameter autovacuum_naptime (default: "1min"); it stops after completing the work
archiver	Start-up in the case of stand-alone or in replication environment of the master instance, parameter archive_mode to "on" or "always" (default:"off"). In slave instance of replication environment, start-up only if the archive_mode to "always"
postgres (local)	Start with the local connection of the client, and stop on disconnect.
postgres (remote)	Start with the remote connection of the client, and stop on disconnect.
wal sender	It starts in the master instance of streaming replication environment. Starts when slave instances come to connect, and stops when slave instances disconnect. Starts during the backup by pg_basebackup command, and stop with the completion of the backup.
wal receiver	Starts in the slave instance of streaming replication environment. When the master instance is stopped, it automatically stops. Restarts when the master instance is restarted.
startup process	Always start in the slave instance of streaming replication environment.
wal writer	It does not start in the slave instance of the replication environment. Except for this situation, it always starts.
bgworker	Behavior is changed according to the specifications of the custom process.
parallel worker	Starting at the time of Parallel Query run, stop at the time of SQL execution is completed.

autovacuum worker进程数

从它的进程名称可以看出，每个数据库都启动了 autovacuum worker 进程。启动进程的最大数量由参数 autovacuum_max_workers 决定（默认值：3）。每个工作进程逐个表执行处理。

postgres进程数

当客户端连接时，Postgres 进程自动启动。 postgres的最大数量受限于参数 max_connections（默认值：100）。没有SUPERUSER权限的一般用户可以连接的连接数是“max_connections ‑ superuser_reserved_connections（默认：3）”的计算结果。以下日志输出超过此限制的连接请求。

示例6 普通用户连接数超标

FATAL: remaining connection slots are reserved for non-replication superuser connections

例子7 超过参数max_connections中指定的连接数

FATAL: sorry, too many clients already

巴特西