使用 pg_ctl 启动关闭以及管理 PostgreSQL 数据库

pg_ctl 是一个很好用的Postgresql 数据库命令行管理工具：

• 初始化 PostgreSQL 数据库实例；
• 启动、终止或重启 PostgreSQL 数据库服务；
• 查看 PostgreSQL数据库服务的状态；
• 让数据库实例重新读取配置文件，允许给一个指定的PostgreSQL进程发送信号；
• 控制 standby 服务器为可读写；
• 在 Windows平台下允许为数据库实例注册或取消一个系统服务。

命令格式：

postgres@ubuntu2204:~$ pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Usage:
  pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]
  pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-p PATH] [-c]
  pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
  pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-c]
  pg_ctl reload     [-D DATADIR] [-s]
  pg_ctl status     [-D DATADIR]
  pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]
  pg_ctl logrotate  [-D DATADIR] [-s]
  pg_ctl kill       SIGNALNAME PID

Common options:
  -D, --pgdata=DATADIR   location of the database storage area
  -s, --silent           only print errors, no informational messages
  -t, --timeout=SECS     seconds to wait when using -w option
  -V, --version          output version information, then exit
  -w, --wait             wait until operation completes (default)
  -W, --no-wait          do not wait until operation completes
  -?, --help             show this help, then exit
If the -D option is omitted, the environment variable PGDATA is used.

Options for start or restart:
  -c, --core-files       allow postgres to produce core files
  -l, --log=FILENAME     write (or append) server log to FILENAME
  -o, --options=OPTIONS  command line options to pass to postgres
                         (PostgreSQL server executable) or initdb
  -p PATH-TO-POSTGRES    normally not necessary

Options for stop or restart:
  -m, --mode=MODE        MODE can be "smart", "fast", or "immediate"

Shutdown modes are:
  smart       quit after all clients have disconnected
  fast        quit directly, with proper shutdown (default)
  immediate   quit without complete shutdown; will lead to recovery on restart

Allowed signal names for kill:
  ABRT HUP INT KILL QUIT TERM USR1 USR2

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

初始化实例

初始化 PostgreSQL 数据库实例的命令：

# 先切换到用户
root@ubuntu2204:~# su - postgres 

# 初始化数据库方法1
postgres@ubuntu2204:~$ initdb -A md5 -D $PGDATA -E utf8 --locale=C -U postgres -W
-A #指定local connections默认的身份验证方法
-D #指定数据目录
-E #指定字符集
--locale=C #指定语言环境
-U  #指定数据库superuser用户名
-W  #指定数据库superuser的用户密码

# 初始化数据库方法2
pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]

# pg_ctl initdb 命令创建了一个新的 PostgreSQL 数据库实例，参数说明：
-s         # 只打印错误和警告信息，不打印提示性信息。
-D DATADIR # 指定数据库实例的数据目录。如果没有指定DATADIR,使用环境变量PGDATA指定的路径
-o options # 为直接传递给initdb命令的参数

创建新的数据库实例数据

# 创建数据目录
postgres@ubuntu2204:~$ mkdir pgsql1
postgres@ubuntu2204:~$ ls -dl pgsql1
drwxrwxr-x 2 postgres postgres 4096 Jul  6 07:39 pgsql1

# 初始化数据库
postgres@ubuntu2204:~$ pg_ctl init -D pgsql1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory pgsql1 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /apps/pgsql/bin/pg_ctl -D pgsql1 -l logfile start

postgres@ubuntu2204:~$ ll  pgsql1/
total 128
drwx------ 19 postgres postgres  4096 Jul  6 07:41 ./
drwxr-x---  3 postgres postgres  4096 Jul  6 07:39 ../
drwx------  5 postgres postgres  4096 Jul  6 07:41 base/
drwx------  2 postgres postgres  4096 Jul  6 07:41 global/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_commit_ts/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_dynshmem/
-rw-------  1 postgres postgres  4789 Jul  6 07:41 pg_hba.conf
-rw-------  1 postgres postgres  1636 Jul  6 07:41 pg_ident.conf
drwx------  4 postgres postgres  4096 Jul  6 07:41 pg_logical/
drwx------  4 postgres postgres  4096 Jul  6 07:41 pg_multixact/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_notify/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_replslot/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_serial/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_snapshots/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_stat/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_stat_tmp/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_subtrans/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_tblspc/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_twophase/
-rw-------  1 postgres postgres     3 Jul  6 07:41 PG_VERSION
drwx------  3 postgres postgres  4096 Jul  6 07:41 pg_wal/
drwx------  2 postgres postgres  4096 Jul  6 07:41 pg_xact/
-rw-------  1 postgres postgres    88 Jul  6 07:41 postgresql.auto.conf
-rw-------  1 postgres postgres 29443 Jul  6 07:41 postgresql.conf

查看服务状态

查询数据库实例状态的命令：

pg_ctl status     [-D DATADIR]

-D datadir   # 指定数据库实例的数据目录

查看第一个实例和新建实例的运行状态：

postgres@ubuntu2204:~$ pg_ctl status -D /pgsql/data
pg_ctl: server is running (PID: 1307)
/apps/pgsql/bin/postgres

postgres@ubuntu2204:~$ pg_ctl status -D /home/postgres/pgsql1
pg_ctl: no server running

启动服务

启动 PostgreSQL 服务的命令：

pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s] [-o OPTIONS] [-p PATH] [-c]

# 参数说明：
start        # 启动数据库实例
-w           # 等待启动完成
-t           # 等待启动完成的等待秒数，默认为60秒
-s           # 只打印错误和警告信息,不打印提示性信息
-D datadir   # 指定数据库实例的数据目录
-l           # 服务器日志输出附加在 “filename” 文件上，如果该文件不存在则创建它
-o options   # 声明要直接传递给 postgres 的选项，具体可见 postgres 命令的帮助
-p path      # 指定 postgres 可执行文件的位置。默认情况下 postgres 可执行文件来自和 pg_ctl 相同的目录，不必使用该选项。除非要进行一些不同寻常的操作，或者产生了 postgres 执行文件找不到的错误
-c           # 提高服务器的软限制 (ulimit -c)，尝试允许数据库实例在有异常时产生一个 coredump 文件,以便于问题定位和故障分析

启动新的数据库实例：

postgres@ubuntu2204:~$ pg_ctl start -D /home/postgres/pgsql1
waiting for server to start....2024-07-06 07:53:18.060 UTC [1952] LOG:  starting PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2024-07-06 07:53:18.061 UTC [1952] LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
2024-07-06 07:53:18.061 UTC [1952] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2024-07-06 07:53:18.061 UTC [1952] WARNING:  could not create listen socket for "localhost"
2024-07-06 07:53:18.061 UTC [1952] FATAL:  could not create any TCP/IP sockets
2024-07-06 07:53:18.062 UTC [1952] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

启动失败，查看报错发现是端口被第一个数据库实例进程占用了，我们这里修改新的数据库实例配置文件：

postgres@ubuntu2204:~$ vim /home/postgres/pgsql1/postgresql.conf 
# 修改配置文件中的 port 端口号

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5433                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/tmp'       # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)

再次启动服务：

postgres@ubuntu2204:~$ pg_ctl start -D /home/postgres/pgsql1
waiting for server to start....2024-07-06 07:57:07.114 UTC [2013] LOG:  starting PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2024-07-06 07:57:07.114 UTC [2013] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2024-07-06 07:57:07.115 UTC [2013] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2024-07-06 07:57:07.117 UTC [2016] LOG:  database system was shut down at 2024-07-06 07:41:18 UTC
2024-07-06 07:57:07.118 UTC [2013] LOG:  database system is ready to accept connections
 done
server started

postgres@ubuntu2204:~$ pg_ctl status -D /home/postgres/pgsql1
pg_ctl: server is running (PID: 2013)
/apps/pgsql/bin/postgres "-D" "/home/postgres/pgsql1"

停止服务

停止PostgreSQL 数据库的命令：

pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]

#参数说明：
-W # 不等待数据库停下来，命令就返回。
-m # 指定停止的模式。模式说明：http://www.lijiach.com/2355.html
# 其它未说明的参数,其含义与启动数据库命令中的参数相同。

停止第二个数据库实例：

postgres@ubuntu2204:~$ pg_ctl stop -D /home/postgres/pgsql1
2024-07-06 08:02:22.824 UTC [2013] LOG:  received fast shutdown request
waiting for server to shut down....2024-07-06 08:02:22.826 UTC [2013] LOG:  aborting any active transactions
2024-07-06 08:02:22.826 UTC [2013] LOG:  background worker "logical replication launcher" (PID 2019) exited with exit code 1
2024-07-06 08:02:22.826 UTC [2014] LOG:  shutting down
2024-07-06 08:02:22.827 UTC [2014] LOG:  checkpoint starting: shutdown immediate
2024-07-06 08:02:22.828 UTC [2014] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.002 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=224 kB
2024-07-06 08:02:22.830 UTC [2013] LOG:  database system is shut down
 done
server stopped

postgres@ubuntu2204:~$ pg_ctl status -D /home/postgres/pgsql1
pg_ctl: no server running

重启服务

重启 PostgreSQL 数据库的命令：

pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s] [-o OPTIONS] [-c]

# 此命令中的参数与启动或停止命令中的参数含义相同

重启第二个实例：

postgres@ubuntu2204:~$ pg_ctl restart -D /home/postgres/pgsql1
waiting for server to shut down....2024-07-06 08:05:06.178 UTC [2049] LOG:  received fast shutdown request
2024-07-06 08:05:06.179 UTC [2049] LOG:  aborting any active transactions
2024-07-06 08:05:06.179 UTC [2049] LOG:  background worker "logical replication launcher" (PID 2055) exited with exit code 1
2024-07-06 08:05:06.179 UTC [2050] LOG:  shutting down
2024-07-06 08:05:06.180 UTC [2050] LOG:  checkpoint starting: shutdown immediate
2024-07-06 08:05:06.185 UTC [2050] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.006 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
2024-07-06 08:05:06.187 UTC [2049] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2024-07-06 08:05:06.287 UTC [2061] LOG:  starting PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2024-07-06 08:05:06.287 UTC [2061] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2024-07-06 08:05:06.288 UTC [2061] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2024-07-06 08:05:06.292 UTC [2064] LOG:  database system was shut down at 2024-07-06 08:05:06 UTC
2024-07-06 08:05:06.295 UTC [2061] LOG:  database system is ready to accept connections
 done
server started

加载配置

在配置文件中改变参数后，需要使用上面这条命令使参数生效，有些参数只能重新启动服务才能生效：比如修改端口号，需要重启服务才生效。

# 修改配置文件 postgresql.conf 后，让修改生效的方法有两种

# 1、在操作系统使用下面命令
pg_ctl reload [-s] [-D datadir]

# 2、在 psql 中使用如下命令
postgres=# select pg_reload_conf();

# 加载配置操作只针对一些配置的修改生效，有些配置需要重新启动服务才能生效。

第二个实例加载配置

postgres@ubuntu2204:~$ pg_ctl reload -D /home/postgres/pgsql1
2024-07-06 08:08:22.028 UTC [2061] LOG:  received SIGHUP, reloading configuration files
server signaled

promote 模式

在流复制架构中，在 standby 主机执行 promote 提升操作，恢复正常的读写操作：

pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]

备用服务器在指定数据目录中运行提升模式命令，结束备用模式并开始读写操作。