云贝教育 |【PostgreSQL PGCA题目解析3】关闭数据库时，使用pg_ctl -m参数指定数据库的关闭方式，比较常用的关闭方式是哪种

考试科目：PGCA-E-090

考试题数：40 道单项选择题、10 道多项选择题（每题 2 分）

通过分数：60%

考试时间：60min

本文为云贝教育刘峰（微信：yunbee_DBA）原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。 

关闭数据库时，使用pg_ctl -m参数指定数据库的关闭方式，比较常用的关闭方式是哪种？

A.smart

B.fast

C.immediate

D.abort

参考答案：B

解析：

一、解析

通过查看pg_ctl -m帮助手册

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

• smart : 等同于oracle的normal

• fast : 等同于oracle的immediate

• immediate :等同于oracle的abort

二、实验

[postgres@ora19c02 ~]$ psql -d testdb
psql (15.4)
Type "help" for help.

testdb=#

窗口2：执行关闭命令

[postgres@ora19c02 ~]$ pg_ctl stop -m smart
waiting for server to shut down....2023-10-19 16:48:11.635 CST [38852] LOG: received smart shutdown request
.........

这里窗口2无法完成数据库关闭，因为窗口1此时有活动连接

将窗口1连接的用户退出

testdb=# exit
[postgres@ora19c02 ~]$

此时窗口2立即关闭成功

2023-10-19 16:48:22.673 CST [38852] LOG: background worker "logical replication launcher" (PID 38858) exited with exit code 1
2023-10-19 16:48:22.674 CST [38853] LOG: shutting down
2023-10-19 16:48:22.674 CST [38853] LOG: checkpoint starting: shutdown immediate
2023-10-19 16:48:22.679 CST [38853] LOG: checkpoint complete: wrote 4 buffers(0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.002 s, total=0.006 s; sync files=3, longest=0.001 s, average=0.001 s; distance=0 kB,estimate=0 kB
2023-10-19 16:48:22.683 CST [38852] LOG: database system is shut down
done
server stopped

2.2 fast

窗口1：打开一个连接

[postgres@ora19c02 ~]$ psql -d testdb
psql (15.4)
Type "help" for help.

testdb=#

窗口2：执行关闭命令

[postgres@ora19c02 ~]$ pg_ctl stop -m fast
waiting for server to shut down....2023-10-19 16:49:53.264 CST [38965] LOG: received fast shutdown request
2023-10-19 16:49:53.265 CST [38965] LOG: aborting any active transactions
2023-10-19 16:49:53.266 CST [38976] FATAL: terminating connection due to administrator command
2023-10-19 16:49:53.267 CST [38965] LOG: background worker "logical replication launcher" (PID 38971) exited with exit code 1
2023-10-19 16:49:53.268 CST [38966] LOG: shutting down
2023-10-19 16:49:53.268 CST [38966] LOG: checkpoint starting: shutdown immediate
2023-10-19 16:49:53.273 CST [38966] LOG: checkpoint complete: wrote 3 buffers(0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 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
2023-10-19 16:49:53.279 CST [38965] LOG: database system is shut down
done
server stopped

可以看fast立即就将数据库关闭，且执行了检查点操作



2.3 immeidate

窗口1：打开一个连接,同时开启事务执行SQL

[postgres@ora19c02 ~]$ psql -d testdb
psql (15.4)
Type "help" for help.

testdb=# begin;
BEGIN
testdb=*# update t1 set id=111 where id=1;
UPDATE 1

窗口2：执行关闭命令

[postgres@ora19c02 ~]$ pg_ctl stop -m immediate
waiting for server to shut down....2023-10-19 16:53:27.476 CST [39150] LOG: received immediate shutdown request
2023-10-19 16:53:27.482 CST [39150] LOG: database system is shut down
done
server stopped

这里看到数据库直接关闭了，没有执行检查点操作

这里在启动过程中，观察到了实例恢复

[postgres@ora19c02 ~]$ pg_ctl start
waiting for server to start....2023-10-19 16:54:31.175 CST [39270] LOG: starting PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623(Red Hat 4.8.5-44), 64-bit
2023-10-19 16:54:31.176 CST [39270] LOG: listening on IPv6 address "::1", port5432
2023-10-19 16:54:31.176 CST [39270] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-10-19 16:54:31.177 CST [39270] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-10-19 16:54:31.182 CST [39273] LOG: database system was interrupted; lastknown up at 2023-10-19 16:52:38 CST
2023-10-19 16:54:31.197 CST [39273] LOG: database system was not properly shutdown; automatic recovery in progress
2023-10-19 16:54:31.199 CST [39273] LOG: redo starts at 0/20002E8
2023-10-19 16:54:31.199 CST [39273] LOG: invalid record length at 0/2000440: wanted 24, got 0
2023-10-19 16:54:31.200 CST [39273] LOG: redo done at 0/2000408 system usage:CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2023-10-19 16:54:31.203 CST [39271] LOG: checkpoint starting: end-of-recoveryimmediate wait
2023-10-19 16:54:31.209 CST [39271] LOG: checkpoint complete: wrote 4 buffers(0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.007 s; sync files=3, longest=0.001 s, average=0.001 s; distance=0 kB,estimate=0 kB
2023-10-19 16:54:31.212 CST [39270] LOG: database system is ready to accept connections
done
server started