客服微信
考试科目：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
二、实验2.1 smart
窗口1：打开一个链接
[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 2 [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
PostgreSQL PGCA考试为理论考试，需通过PostgreSQL PGCA-E-090 考试才能拿到“PostgreSQL PGCA证书”。
以下是PostgreSQL官网的考试费，可以添加云贝教育的课程顾问老师微信19941464235或者19906632509，咨询有优惠。
PostgreSQL证书如下 合格考生公布：考后不超过 5 个工作日 “开源软件联盟 PostgreSQL 分会”公众号发布 证书寄送：考后统一寄送至培训机构）
云贝教育有专业的PostgreSQL PGCA认证培训课程，想快速通过考试的同学可以扫码联系老师咨询（点击文字转跳至PostgreSQL PGCA大纲）
