PGCM 认证考完了,聊聊上机真题和面试那些事

四季读书网 2026-10-23 00:15:49 8 0

落笔相逢，我是军哥！

考 PGCM 那天，我在考场坐了整整八个小时。

上午九点开始，中午扒口饭接着干，下午六点半交卷。四个科目四份实验题，安全管控、优化、高可用、分布式，全是在真实环境上敲命令，没有一个选择题。考完出来手腕是酸的，脑子是空的，但心里有种奇怪的踏实，这个证不是背出来的，是敲出来的。

这个系列写到这篇就收尾了，最后不讲新技术，讲讲备考经验、上机考试的真实题目长什么样，以及面试里反复出现的 PG 问题。技术原理前面二十一篇都讲过，这篇换个角度，把考场上的原题、命令和坑一层层摆出来，你会不断看到熟悉的影子，这也说明一件事，这个认证的考纲和真实工作高度重合。

考试的基本盘

PGCM 高级认证的上机考试分四个科目，PG 安全管控、PG 优化、PG 高可用、PG 分布式，每科一个代号。总时长八小时，上午三个小时下午五个小时，中间休息一个半小时。环境是现成的 Linux 机器，源码包放在 /home/pg12/resource/ 目录里，剩下全靠手。

题目风格一句话概括，全是生产里的日常活，但每个要求都抠得很细。密码统一设成 1qaz@WSX 并写进 .pgpass 实现免密，端口、路径全是指定值，不是你顺手用什么就填什么。审错题目的代价是整题没分，我读题花的时间比很多人想象的多。

安全模块，第一季的内容全覆盖

翻开安全题我差点笑出来，全是老朋友。

编译安装和免密

头一道是源码编译 PG12，安装目录指定 /home/pg12/soft，要求开启 SSL 支持。configure 这么写。

./configure --prefix=/home/pg12/soft --with-opensslmake -j4 && make install

别忘了进 contrib 目录把后面要用的扩展也编出来，sslinfo、pgcrypto、passwordcheck 都在那儿，考场上现找会慌。

cd contrib && make && make install

PGDATA 指定 /home/pg12/data，初始化开光 checksum，端口改成 5666。

initdb -k -D /home/pg12/data -U postgres# postgresql.conf 里改port = 5666

密码设成 1qaz@WSX 之后写 .pgpass 免密。格式是五段，主机、端口、库、用户、密码，拿冒号拼起来。

localhost:5666:*:postgres:1qaz@WSX

写完顺手 chmod 600 .pgpass。这步忘了，文件权限不对，PG 会当它不存在，免密静默失效，你还以为是密码错了。

题目还要求配上 SSL 并建 sslinfo 扩展，server.crt 和 server.key 放进 PGDATA，postgresql.conf 里 ssl 打开，扩展一条 create extension sslinfo 就完事。密码复杂度检查用的是 contrib 里的 passwordcheck，挂进 shared_preload_libraries 重启生效，之后再有人想把密码设成 123456，直接挡在门外。

用户、表空间、库的老三样

建 appuser，给 createdb 和 login，密码带有效期，2022年12月1日之前有效。

create user appuser createdb login password '1qaz@WSX' valid until '2022-12-01';

valid until 这个子句平时用得少，考场上现翻语法手就慢了。

建表空间 exam 指向 /exam，目录提前 mkdir 好并把属主给 pg12，不然建表空间直接报权限错。然后建 appdb，owner 是 appuser，落在 exam 表空间里。

create tablespace exam owner appuser location '/exam';create database appdb owner appuser tablespace exam;

接着是那套「新库初始化标准动作」，回收 public schema 的 create 权限，用 appuser 建同名 schema，在 schema 里建 app(id int) 表，再把 appdb 的 connect 权限从 public 收回来，题目要求其他非超级用户连不进来，就靠这一手。

revoke create on schema public from public;revoke connect on database appdb from public;

readonlyuser 这道明牌题

readonlyuser 要求对 appuser schema 下所有表有只读权限，包括将来新建的表。明牌考 default privileges。grant select on all tables 只管存量，alter default privileges 才管增量。

grant connect on database appdb to readonlyuser;grant usage on schema appuser to readonlyuser;grant select on all tables in schema appuser to readonlyuser;alter default privileges for user appuser in schema appuser  grant select on tables to readonlyuser;

盯住第四行的 for user appuser。default privileges 绑的是「谁建的表」，不是「谁能看」。主语写错，以后 appuser 建的新表 readonlyuser 照样看不见。第二季那篇文章讲的就是这个坑，考场上它真的在等我。

日志参数全家桶

改集群配置这段要求特别多，一条条来。日志路径 /pglog，csvlog 格式，每天一个新文件，不限单文件大小，只保留最近一天。

log_destination = 'csvlog'logging_collector = onlog_directory = '/pglog'log_filename = 'postgresql-%Y-%m-%d.log'log_rotation_age = '1d'log_rotation_size = 0log_truncate_on_rotation = on

log_filename 把时分秒去掉只留日期，这里头有讲究。文件名按天重复，log_truncate_on_rotation 开着，第二天轮转到同名文件直接截断覆盖，磁盘上永远只留最近一天。文件名里带上时分秒的话，每天都生成新文件，旧文件没人删，「保留一天」这条就落不了地。

日志内容要带毫秒时间戳、用户名、库名、进程号，顺序还不能乱。

log_line_prefix = '%m %u %d %p'

%m 是带毫秒的时间戳。题目特地写了不要 unix 时间戳，那是在防你填 %t。

剩下的几项，监听地址改成 *，全页写确认开着，密码加密算法切 scram。

listen_addresses = '*'full_page_writes = onpassword_encryption = 'scram-sha-256'

最多允许 100 个非超级用户连接这条，值得细品。max_connections 算的是总连接数，superuser_reserved_connections 默认给超级用户留 3 个，所以这里我给的是 103，普通用户顶到 100 就再也进不来，超级用户还有兜底通道。只填 100 的话，真出事那天超级用户可能连不进去救火。

改完 password_encryption 有个大坑。已存在的用户，密码哈希还是旧的 md5，必须逐个 alter user 重设密码，新哈希才按 scram 生成。不重设，pg_hba 全场切 scram 之后老用户集体连不上，考场上这一卡就是十几分钟。

pg_hba.conf 三行，顺序就是分数

要求翻译成配置语言一共三条。本地连接走 scram，任意网段开放但禁用 hostssl 类型，禁止 postgres 从网络连入。

local   all   postgres                scram-sha-256host    all   postgres  0.0.0.0/0     rejecthost    all   all       0.0.0.0/0     scram-sha-256

reject 那一行必须压在放行行上面。pg_hba 从上往下匹配，命中即停，顺序写反，postgres 会先撞上第三行的放行规则，reject 永远轮不到，「禁止超级用户网络登录」形同虚设。禁用 hostssl 的意思是开放行只能用 host，审题分就藏在这种词里。

RLS 两条策略

跑完题目给的 RLSData.sql，在 passwd 表上启用行级安全。

alter table passwd enable row level security;

两条策略。一条管 select，appuser1 只能看到 user_name 是 userapp1 的行。

create policy appuser1_select_passwd on passwd  for select to appuser1  using (user_name = 'userapp1');

一条管 insert，允许 appuser1 插进题目指定的那行 readonlyuser 数据。

create policy appuser1_insert_passwd on passwd  for insert to appuser1  with check (user_name = 'readonlyuser');

using 管「能看见什么」，with check 管「能写进什么」，考题用两条策略把这个区别直接摆在你面前。insert 那条只写 using 不写 with check，插入照样被拦，for insert 的策略认的是 with check。

加密和审计收尾

pgcrypto 建 test_user 表，id 序列、user_name varchar(32)、password text，插两行，密码用 crypt 加 gen_salt 的 md5 算法加密。

create extension pgcrypto;create table test_user(id serial, user_name varchar(32), password text);insert into test_user(user_name, password) values  ('appuser1', crypt('123456', gen_salt('md5'))),  ('appuser2', crypt('123456', gen_salt('md5')));

pgaudit 要求对象级审计，盯 appdb 里 appuser.app 表的 select 和 delete。先把 pgaudit 挂进 shared_preload_libraries 重启，建扩展，建审计角色 appauditor，再把要审计的权限授给它。

create extension pgaudit;create user appauditor;alter system set pgaudit.role = 'appauditor';grant select, delete on appuser.app to appauditor;

pgaudit 对象审计的思路是反着来的，不声明「审计谁」，把权限授给审计角色，角色拥有什么权限，什么操作就进审计日志。grant 落下的那一瞬间，审计范围就定死了。

这题容易挂的点

.pgpass 忘了 chmod 600，免密静默失效，还不报错。
改完 scram 不重置老用户密码，哈希还是 md5，全场连接报错。
default privileges 的 for user 写成 readonlyuser，方向反了。
pg_hba 的 reject 写在放行行下面，永不命中。
log_filename 带时分秒，「只保留一天」实现不了。
valid until 的日期写法手生，建用户卡壳。

优化模块，第二季的考点密度

优化部分一样是熟人局，但有几处数值得算准。

磁盘预读改 8192KB，用 blockdev。注意它的单位是 512 字节的扇区，8192KB 得换算成 16384。

blockdev --setra 16384 /dev/sdablockdev --getra /dev/sda

直接敲 8192 的人不少，单位坑，getra 验证一下就露馅。

装 pg_stat_statements 查慢 SQL，挂 shared_preload_libraries 重启，建扩展，老流程。已知实例里 SQL 最大 2KB，要求语句文本不被截断，考的是 track_activity_query_size。这个参数默认 1024，单位是字节，2KB 的 SQL 超了，超出的部分直接截掉，pg_stat_activity 里看到的就是半截话。改成 2048 起步，改完要重启，reload 不生效，这在 PG15 之前是写死的限制。

超过 5 秒的 SQL 连执行计划一起落日志，auto_explain 上场。

shared_preload_libraries = 'pg_stat_statements,auto_explain'auto_explain.log_min_duration = '5s'auto_explain.log_analyze = onauto_explain.log_buffers = onauto_explain.log_nested_statements = on

只设 log_min_duration 不把模块挂进 shared_preload_libraries，参数设了个寂寞，日志里什么都不会多。题目里「对历史时段运行慢的SQL进行分析」这句话就是提示，事后分析得靠日志，光靠 pg_stat_statements 的视图，重启就清零了。

SSD 场景调参，random_page_cost 从默认的 4 降到 1.1。SSD 随机读和顺序读代价几乎一样，还按机械盘的四倍代价估，优化器会放着索引不走去全表扫。顺手把 effective_io_concurrency 拉到 200 上下，位图扫描的预读请求才能并发发出去，机械盘时代这个值给个 2 就不错了。

2048MB 内存配 shared_buffers，四分之一原则，给 512MB。这个经验值背后的账第二季配置篇算过，太小浪费内存，太大跟操作系统的 page cache 双重缓冲互相拖累。

PgBouncer，端口和路径全是送分题也是送命题

装到 /home/pg12/pgbouncer，端口 5766，连接名叫 appdb，日志和 pid 都放同目录。ini 的关键几行。

[databases]appdb = host=127.0.0.1 port=5666 dbname=appdb[pgbouncer]listen_addr = 0.0.0.0listen_port = 5766auth_type = md5auth_file = /home/pg12/pgbouncer/userlist.txtlogfile = /home/pg12/pgbouncer/pgbouncer.logpidfile = /home/pg12/pgbouncer/pgbouncer.pidadmin_users = postgres

userlist.txt 里按用户名加密码的格式登记 appuser。psql 走 5766 免密连池子，.pgpass 里加一行，题目特地提示主机名写局域网 IP 别写 localhost。

192.168.x.x:5766:appdb:appuser:1qaz@WSX

考场上写 localhost 本机当然通，但判分是从连接身份和地址去验的，这一条就是细节分。

pg_trgm 那道 SQL 优化题

\copy 把 t_hash.csv 灌进 appuser.t_hash，然后让 like '%89f34%' 这种前后模糊的查询走索引。btree 对前模糊无能为力，pg_trgm 加 GIN 是标准答案。

create extension pg_trgm;create index t_hash_md5_gin on appuser.t_hash using gin (md5 gin_trgm_ops);

没建扩展直接建索引，PG 会当面泼冷水。

ERROR:  operator class "gin_trgm_ops" does not exist for access method "gin"

这个报错第二季的文章里原样演示过。考场上它一蹦出来，你就知道该补哪一步。剩下的还有并行查询，给 t_hash 设表级参数 parallel_workers 为 2，alter table 一行的事。

这题容易挂的点

blockdev 单位是 512 字节扇区，8192KB 要乘 2。
track_activity_query_size 改完不重启，截断依旧。
auto_explain 没挂 shared_preload_libraries，参数白设。
.pgpass 里 PgBouncer 那行主机名写了 localhost，判分口径对不上。
建 GIN 索引前忘了 create extension pg_trgm，报错现场很尴尬。

Repmgr，从编译到切换的完整命令链

高可用题就是部署加切换演练的全流程。两台机器用 pg12 用户编译 repmgr 5.1.0，稳的路子是把它放进 PG12 源码树的 contrib 里编，pg_config 指对，make install 完事。

postgresql.conf 按主备要求调，wal_level 开 replica，max_wal_senders 和 max_replication_slots 给够，hot_standby 打开，shared_preload_libraries 挂上 repmgr。pg_hba 放行复制连接和 repmgr 库的连接。然后建 repmgr 用户，superuser 加 replication 属性，再建 repmgr 库。

create user repmgr superuser replication login password '1qaz@WSX';create database repmgr owner repmgr;

repmgr.conf 放在 /home/pg12/conf，日志 repmgr.log 也放这儿，题目点名的必配项一个不能少。

cluster = 'main'node_id = 1node_name = 'node1'conninfo = 'host=192.168.x.11 port=5666 user=repmgr dbname=repmgr'data_directory = '/home/pg12/data'log_file = '/home/pg12/conf/repmgr.log'failover = automaticpromote_command = 'repmgr standby promote -f /home/pg12/conf/repmgr.conf --log-to-file'follow_command = 'repmgr standby follow -f /home/pg12/conf/repmgr.conf --log-to-file -W'monitoring_history = yes

之后是那条滚瓜烂熟的命令链。主节点注册，repmgr primary register。备节点克隆，repmgr -h 主库IP -U repmgr -d repmgr standby clone。备库拉起来，standby register。中间穿插 ssh 互信，ssh-keygen 加 ssh-copy-id，switchover 和 clone 都靠免密互通撑着，卡在这一步的人考场上一抓一把。

手动切换，备机上跑 repmgr standby switchover，跟着提示走。切完到题目真正的戏肉，在新主的 psql 里敲一下 checkpoint，再跑 repmgr cluster show，Timeline 列得是 2。

Timeline 这个判分点，值得掰开讲

很多人背过「切换一次 Timeline 加一」，但没想过为什么。WAL 段文件名的前八位十六进制就是时间线号，000000010000000000000005 这条 WAL 属于时间线 1。备库被 promote 的那一瞬间，它和旧主的历史就分叉了，旧主可能还在自己的时间线 1 上继续写，两边同名的 WAL 文件内容却对不上，放任不管，归档目录里就是一笔糊涂账。

PG 的解法干脆利落，每次 promote 开一条新时间线，新主写的 WAL 从 00000002 开头，跟旧历史物理隔离。恢复的时候顺着 timeline 历史文件，每一次分叉的谱系都能理清。所以 Timeline 从 1 变 2，就是一次 promote 的活证据。

自动切换再演一遍。改 repmgr.conf 挂好 failover 参数，起 repmgrd，确认进程 running，手工停掉主库，repmgrd 探测到主库失联，把备库提升。老主回队不能直接 start，它的数据和新时间线已经分叉，得 repmgr node rejoin 配合 pg_rewind 把它掰回新主的时间线，掰完注册成新备。再在新主上 checkpoint，cluster show 的 Timeline 变成 3。

两次切换，两条新时间线，数字完全对得上。这道判分题背后考的是一件事，你到底懂不懂 WAL 的历史是怎么分叉又被记录的。背流程能拿到操作分，懂时间线才能解释每一个数字。

至于为什么要 checkpoint，repmgr 展示的时间线信息有它自己的刷新节奏，手动 checkpoint 是逼着状态立刻落盘刷新，判分脚本紧跟着读，差这一下就是 1 和 2 的区别。

这题容易挂的点

repmgr 用户没给 superuser，克隆和注册接连报错。
ssh 互信没配或者方向反了，switchover 半路卡死。
切完忘在新主 checkpoint，Timeline 不刷新，明明切成功了却拿不到分。
老主回队直接 pg_ctl start，时间线分叉，rejoin 失败，只能 pg_rewind 或者重新克隆。

Citus，单机三实例的分布式

分布式题是单机多实例，一个 CN 两个 Worker，全用 pg12 用户管。三个实例三套 PGDATA 三个端口，CN 是 1921 配 /home/pg12/data_cn，Worker01 是 7000 配 data_worker01，Worker02 是 8000 配 data_worker02，超级用户密码统一 1qaz@WSX。

citus 源码编译对着 PG12 的 pg_config 来，装完三个实例的 shared_preload_libraries 都挂上 citus，重启，每个实例的 postgres 库里 create extension citus。监听地址都改 *，pg_hba 三节点互相放行，CN 要能连 Worker，Worker 之间有些操作也要互通。

连上 CN，把两个 Worker 加进来，单机部署写本机地址就行。

select * from master_add_node('127.0.0.1', 7000);select * from master_add_node('127.0.0.1', 8000);select * from master_get_active_worker_nodes();

建测试表 shiptrack，gid 序列主键，tracktime 是 timestamp without time zone，gid 做分布键，32 个分片每个一份副本。

create table shiptrack(gid serial primary key, tracktime timestamp without time zone);set citus.shard_count = 32;set citus.shard_replication_factor = 1;select create_distributed_table('shiptrack', 'gid');

shard 数要在建分布式表之前定好，表一旦分布出去，分片数就木已成舟。分布键同理，选错了想换，等于把整张表拆了重建。这套「先想清楚再动手」的顺序感，第四季实战篇反复强调，考场上一字不差地还回来了。

这题容易挂的点

shared_preload_libraries 忘了挂 citus，create extension 直接报错。
三个实例端口写串，1921、7000、8000 各有归属。
pg_hba 只放了客户端没放节点间互联，master_add_node 失败。
没设 shard_count 就 create_distributed_table，分片数对不上题目要求。

考完我最大的感受是，这个考试没有偏题怪题，它考的就是一个 PG DBA 的日常手艺，编得了源码、调得动参数、写得出 pg_hba、玩得转 RLS、看得懂执行计划、搭得起主备和分布式。手上功夫到了，题目自己会矮下去。

面试里反复出现的那些问题

这些年我面试别人和被面试攒下来的高频题，这里挑五个出场率最高的，把面试官怎么问、浅的怎么答、深的怎么答，都摆出来。差距不在概念，在概念底下还压着几层。

WAL 和 full_page_writes

几乎每个 PG 面试都从这里开场。面试官问得很朴素，WAL 是干什么的，full_page_writes 又解决什么问题。

浅回答是这样的，WAL 是预写日志，先记日志再改数据，崩了能恢复，full_page_writes 是个可靠性参数，建议开着。话都没错，可听完你还是不知道这参数防的到底是什么。

深回答会往下走一层。PG 的页是 8KB，磁盘和文件系统可不敢保证 8KB 一次写完，断电那一刻页可能只落了一半，半新半旧，这就是撕裂页。WAL 里记的是增量改动，增量只能重放在一个好页上，撕了的页重放等于在废墟上盖楼。所以 checkpoint 之后每个页第一次被修改，PG 会把整个页塞进 WAL，恢复时先铺整页再重放增量。这就是 full_page_writes 干的事。面试官再追一句什么时候能关，能接住「存储层保证原子写」这个话头的，就是真懂。第一季第七篇那个断电故事，能把这道题讲出层次。

vacuum 和表膨胀

面试官问，表为什么会膨胀，vacuum 到底在干什么。

浅回答，update 和 delete 产生死元组，vacuum 负责清理，清得不及时表就膨胀。一句话题库答案。

深回答会把因果链补齐。PG 的 update 骨子里是 delete 加 insert，旧版本得留着，因为可能还有老事务要看它，这是 MVCC 欠下的账。autovacuum 回收死元组只是把空间标记成可复用，物理文件不缩，所以高频更新的表越跑越胖。再往下，一个挂了三天没提交的长事务，能让它之后全库产生的死元组都没人敢动，vacuum 干着急。能主动提到事务年龄和 freeze 的，说明他不光知道膨胀怎么来，还知道更凶险的回卷长什么样。

执行计划怎么看

面试官贴一段执行计划，问你怎么看。

浅回答，看 cost，cost 小就快，有 seq scan 就加索引。

深回答先纠一个普遍误会，cost 是 PG 自己估出来的代价单位，跟毫秒没有任何关系，真相在 explain analyze 的 actual time 和 rows 里。会看的人扫计划有个固定动作，找估算 rows 和实际 rows 差得最离谱的那一步，差出几十上百倍，后面所有糟糕的决策都从这一步滚出来。再问估行不准怎么办，浅的还在说加索引，深的会先查统计信息，analyze 跑一遍，不行就调大列的 statistics target，实在不行上动态采样。先修估算再谈索引，这个顺序是踩过坑的人才有的。

前后模糊的 like 怎么走索引

面试官问，like '%xxx%' 这种查询能走索引吗。

浅回答，走不了，前模糊只能全表扫。聊到这儿就死了。

深回答会拐个弯。btree 确实没戏，它按字符串顺序排，前面带通配符就没了方向。但换条路，pg_trgm 把字符串切成三元组，GIN 索引存这些三元组，前后模糊变成三元组的集合匹配，索引就能走了。顺手再补一句代价，GIN 写入慢、体积大，更新频繁的表要掂量。这道就是优化模块的原题，考过 PGCM 的人等于送分。

主备切换怎么防脑裂

面试官问，自动故障切换怎么保证不出双主。

浅回答，用 repmgr 或者 Patroni 做自动切换。工具名背得挺熟，原理一个字没有。

深回答会先讲怕什么。自动切换最怕网络分区，两边都觉得自己该当主，写入一分叉就是灾难。repmgr 的路子是 witness 节点见证加多数派可见，看不到多数派的节点没资格提升。Patroni 更彻底，把主的身份锁交给 etcd 这类共识组件，锁在谁手里谁是主，锁丢了自动降级。再问脑裂之后怎么收场，能讲出老主回队要 pg_rewind 掰回新时间线、掰不动就重新克隆的，这是真切换过的人。考场上 Timeline 从 1 到 3 的那道判分题，跟这道面试题是同一个知识点。

发现一个规律没有，这些面试题就是这个系列二十一篇文章的目录。这不是巧合，面试问的正是一个 DBA 日常真正要处理的事。

清单好列，面试里真正难的是分辨真会假会。我的做法是不问概念问场景。问 WAL 我不问它是什么，我问断电之后库起不来，你第一步干什么。问执行计划我不问 cost 是什么，我贴一段计划，问哪一行最可疑。背过题库的人概念张口就来，场景一问就开始绕。真处理过故障的人顺序是反的，先说怎么查，概念顺口带出来。还有一个信号我屡试不爽，候选人主动说这个坑我踩过，我就顺着细节往下挖三层，挖到底还不露怯的，就是我要的人。

面试官爱问什么

为什么考纲和面试题高度重合。考试和面试考的都是 DBA 的日常手艺，编源码、调参数、写 pg_hba、搭主备，这篇的真题对照前面二十一篇的目录就能看出来，备考和准备面试本质是同一件事。
default privileges 为什么总考。grant select on all tables 只管存量，alter default privileges 才管增量，这个坑在考场上、生产上、面试里是三栖常客，错一次记一辈子。
Timeline 是什么，为什么切换后加一。每次 promote 开一条新时间线，WAL 靠它区分历史，repmgr cluster show 里 Timeline 从 1 变 2 变 3，就是切换次数的活记录。
被问到不会的问题怎么办。别硬编，说清你会怎么查、怎么验证，面试官看的是排查路径，不是百科全书的容量。