前言PostgreSQL 的运维逻辑与 MySQL、Oracle 有明显差异。很多刚接触 PostgreSQL 的 DBA看到数据库连接数升高、表空间增长、SQL 卡顿或者主从延迟时仍然沿用其他数据库的处理习惯先看 CPU、再看磁盘、最后考虑重启。但 PostgreSQL 的很多生产问题实际上都与长事务、MVCC 垃圾版本、锁等待、统计信息失真、WAL 堆积、复制槽未消费以及 Autovacuum 工作不充分有关。下面整理 100 条 PostgreSQL DBA 日常使用频率较高的命令覆盖实例信息、连接会话、事务锁、SQL 性能、表与索引、Vacuum、WAL、主从复制、备份恢复和权限管理等核心场景。本文主要面向 PostgreSQL 14—18。不同版本的统计视图字段可能存在差异执行前应先确认数据库版本。当前 PostgreSQL 官方文档的 Current 版本为 PostgreSQL 18。一、实例与基础信息1. 查看 PostgreSQL 版本SELECT version();返回 PostgreSQL 版本、编译器、操作系统架构等信息。也可以只查看版本号SHOW server_version;2. 查看服务器版本号SELECT current_setting(server_version);在脚本中使用current_setting()通常比解析version()的返回文本更方便。3. 查看当前数据库SELECT current_database();4. 查看当前用户SELECT current_user;同时查看当前用户和会话用户SELECT current_user, session_user;session_user表示最初建立连接的用户current_user可能因SET ROLE等操作发生变化。5. 查看数据库服务器地址和端口SELECT inet_server_addr(), inet_server_port();在 VIP、负载均衡、读写分离和多实例环境中可以用它确认当前连接到了哪台数据库。6. 查看客户端地址和端口SELECT inet_client_addr(), inet_client_port();7. 查看数据库启动时间SELECT pg_postmaster_start_time();计算实例已经运行了多长时间SELECT now() - pg_postmaster_start_time() AS uptime;8. 查看当前时间和时区SELECT now(), current_timestamp, current_setting(TimeZone);9. 查看数据目录SHOW data_directory;也可以查询SELECT current_setting(data_directory);10. 查看配置文件路径SHOW config_file;同时查看主要配置文件SELECT current_setting(config_file) AS config_file, current_setting(hba_file) AS hba_file, current_setting(ident_file) AS ident_file;分别对应postgresql.confpg_hba.confpg_ident.conf二、数据库、模式与对象11. 查看所有数据库在psql中执行\lSQL 方式SELECT datname, datdba::regrole AS owner, encoding, datcollate, datctype, datallowconn FROM pg_database ORDER BY datname;12. 查看当前数据库大小SELECT pg_size_pretty(pg_database_size(current_database()));13. 查看所有数据库大小SELECT datname, pg_size_pretty(pg_database_size(datname)) AS database_size FROM pg_database WHERE datallowconn ORDER BY pg_database_size(datname) DESC;14. 查看当前数据库中的模式在psql中\dnSQL 方式SELECT schema_name, schema_owner FROM information_schema.schemata ORDER BY schema_name;15. 查看当前搜索路径SHOW search_path;search_path会影响未指定 Schema 的对象解析顺序。16. 查看指定模式中的表在psql中\dt public.*SQL 方式SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname public ORDER BY tablename;17. 查看表结构在psql中\d public.table_name查看更完整的信息\d public.table_name\d可以显示字段、索引、约束、访问方法、表大小和存储参数等信息。18. 查看视图\dv查看物化视图\dmSQL 方式SELECT schemaname, viewname, viewowner FROM pg_views ORDER BY schemaname, viewname;19. 查看函数和存储过程\df查看更详细的信息\dfSQL 查询SELECT n.nspname AS schema_name, p.proname AS routine_name, pg_get_function_identity_arguments(p.oid) AS arguments, p.prokind FROM pg_proc p JOIN pg_namespace n ON n.oid p.pronamespace WHERE n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, p.proname;20. 查看扩展\dxSQL 方式SELECT extname, extversion, extnamespace::regnamespace AS schema_name FROM pg_extension ORDER BY extname;三、连接与会话管理21. 查看当前活动会话SELECT pid, usename, datname, client_addr, application_name, state, backend_start, query_start, wait_event_type, wait_event, query FROM pg_stat_activity ORDER BY query_start NULLS LAST;pg_stat_activity是 PostgreSQL 会话排查的核心视图。22. 查看当前连接数SELECT count(*) AS current_connections FROM pg_stat_activity;23. 按数据库统计连接数SELECT datname, count(*) AS connection_count FROM pg_stat_activity GROUP BY datname ORDER BY connection_count DESC;24. 按用户统计连接数SELECT usename, count(*) AS connection_count FROM pg_stat_activity GROUP BY usename ORDER BY connection_count DESC;25. 按客户端地址统计连接数SELECT client_addr, count(*) AS connection_count FROM pg_stat_activity GROUP BY client_addr ORDER BY connection_count DESC;26. 查看最大连接数SHOW max_connections;查看为超级用户预留的连接数SHOW superuser_reserved_connections;27. 查看连接使用率SELECT count(*) AS current_connections, current_setting(max_connections)::int AS max_connections, round( count(*) * 100.0 / current_setting(max_connections)::int, 2 ) AS usage_percent FROM pg_stat_activity;28. 查看正在执行的 SQLSELECT pid, usename, datname, client_addr, now() - query_start AS running_time, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state active AND pid pg_backend_pid() ORDER BY query_start;29. 查看执行超过 60 秒的 SQLSELECT pid, usename, datname, client_addr, now() - query_start AS running_time, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state active AND query_start now() - interval 60 seconds ORDER BY query_start;30. 查看空闲连接SELECT pid, usename, datname, client_addr, state, now() - state_change AS idle_time, query FROM pg_stat_activity WHERE state idle ORDER BY state_change;31. 查看空闲事务SELECT pid, usename, datname, client_addr, xact_start, now() - xact_start AS transaction_time, state, query FROM pg_stat_activity WHERE state idle in transaction ORDER BY xact_start;idle in transaction是 PostgreSQL 运维中必须重点关注的状态。会话虽然没有执行 SQL但事务仍未结束可能持有锁、阻止 Vacuum 清理垃圾版本并导致表膨胀。32. 取消正在执行的 SQLSELECT pg_cancel_backend(12345);pg_cancel_backend()只取消当前 SQL一般不会断开数据库连接。33. 终止数据库会话SELECT pg_terminate_backend(12345);终止连接后该会话中的未提交事务会被回滚。34. 批量取消长时间 SQLSELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state active AND query_start now() - interval 30 minutes AND pid pg_backend_pid();生产环境不要直接执行。建议先将查询结果中的会话逐个确认再决定是否取消。35. 查看自己的后台进程 PIDSELECT pg_backend_pid();四、事务、锁等待与阻塞36. 查看长事务SELECT pid, usename, datname, client_addr, xact_start, now() - xact_start AS transaction_age, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;37. 查看超过 10 分钟的事务SELECT pid, usename, datname, client_addr, now() - xact_start AS transaction_age, state, query FROM pg_stat_activity WHERE xact_start now() - interval 10 minutes ORDER BY xact_start;38. 查看当前锁SELECT pid, locktype, relation::regclass AS relation, mode, granted, waitstart FROM pg_locks ORDER BY granted, pid;39. 查看正在等待的锁SELECT pid, locktype, relation::regclass AS relation, page, tuple, transactionid, mode, waitstart FROM pg_locks WHERE NOT granted ORDER BY waitstart;40. 查看被谁阻塞SELECT pid, pg_blocking_pids(pid) AS blocking_pids, wait_event_type, wait_event, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0;41. 查看完整阻塞关系SELECT blocked.pid AS blocked_pid, blocked.usename AS blocked_user, now() - blocked.query_start AS blocked_duration, blocked.query AS blocked_query, blocker.pid AS blocker_pid, blocker.usename AS blocker_user, now() - blocker.query_start AS blocker_duration, blocker.state AS blocker_state, blocker.query AS blocker_query FROM pg_stat_activity blocked CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bpid JOIN pg_stat_activity blocker ON blocker.pid bpid ORDER BY blocked.query_start;这条 SQL 可以直接建立等待会话与阻塞会话之间的关系。42. 查看阻塞其他会话的进程SELECT DISTINCT blocker.pid, blocker.usename, blocker.datname, blocker.client_addr, blocker.state, blocker.xact_start, blocker.query_start, blocker.query FROM pg_stat_activity blocked CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bpid JOIN pg_stat_activity blocker ON blocker.pid bpid;43. 终止阻塞源会话SELECT pg_terminate_backend(12345);终止前必须确认是否存在未提交事务回滚需要多长时间是否为关键业务连接是否会触发应用重试风暴是否还有更上游的阻塞源。44. 查看预备事务SELECT * FROM pg_prepared_xacts;两阶段提交环境中长期未完成的预备事务可能持续持有锁。45. 查看数据库死锁数量SELECT datname, deadlocks FROM pg_stat_database ORDER BY deadlocks DESC;这里记录的是数据库启动以来或统计信息重置以来累计检测到的死锁数量。五、SQL 性能与执行计划46. 查看估算执行计划EXPLAIN SELECT * FROM public.table_name WHERE id 100;EXPLAIN不会真正执行 SQL。47. 查看实际执行计划EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM public.table_name WHERE id 100;它会实际执行 SQL并显示实际耗时实际返回行数执行循环次数Shared Buffer 命中磁盘读取临时文件读写。对于UPDATE、DELETE和INSERT执行EXPLAIN ANALYZE会真正修改数据。生产环境中应放在事务中验证并在确认后回滚。BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE public.table_name SET status 1 WHERE id 100; ROLLBACK;48. 查看更完整的执行计划EXPLAIN ( ANALYZE, BUFFERS, WAL, VERBOSE, SETTINGS, SUMMARY ) SELECT * FROM public.table_name WHERE id 100;49. 查看 JSON 格式执行计划EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM public.table_name WHERE id 100;JSON 格式更适合执行计划平台、自动化分析工具和程序解析。50. 安装 pg_stat_statements首先需要在配置文件中加入shared_preload_libraries pg_stat_statements重启数据库后在目标数据库创建扩展CREATE EXTENSION IF NOT EXISTS pg_stat_statements;pg_stat_statements用于聚合记录 SQL 的执行次数、总耗时、平均耗时、返回行数和 I/O 等统计数据。该扩展需要在具体数据库中创建。51. 查看总耗时最高的 SQLSELECT queryid, calls, round(total_exec_time::numeric, 2) AS total_exec_ms, round(mean_exec_time::numeric, 2) AS avg_exec_ms, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;52. 查看平均耗时最高的 SQLSELECT queryid, calls, round(mean_exec_time::numeric, 2) AS avg_exec_ms, round(max_exec_time::numeric, 2) AS max_exec_ms, rows, query FROM pg_stat_statements WHERE calls 10 ORDER BY mean_exec_time DESC LIMIT 20;53. 查看执行次数最多的 SQLSELECT queryid, calls, round(total_exec_time::numeric, 2) AS total_exec_ms, round(mean_exec_time::numeric, 2) AS avg_exec_ms, query FROM pg_stat_statements ORDER BY calls DESC LIMIT 20;54. 查看读取数据块最多的 SQLSELECT queryid, calls, shared_blks_read, shared_blks_hit, temp_blks_read, temp_blks_written, query FROM pg_stat_statements ORDER BY shared_blks_read DESC LIMIT 20;55. 查看临时文件消耗最高的 SQLSELECT queryid, calls, temp_blks_read, temp_blks_written, round(total_exec_time::numeric, 2) AS total_exec_ms, query FROM pg_stat_statements WHERE temp_blks_written 0 ORDER BY temp_blks_written DESC LIMIT 20;56. 查看 WAL 生成量最高的 SQLSELECT queryid, calls, wal_records, wal_fpi, pg_size_pretty(wal_bytes::bigint) AS wal_size, query FROM pg_stat_statements ORDER BY wal_bytes DESC LIMIT 20;适合分析批量更新、大事务以及 WAL 异常增长问题。57. 重置 pg_stat_statementsSELECT pg_stat_statements_reset();重置前应确认是否还需要保留原有 SQL 性能基线。58. 查看数据库缓存命中率SELECT datname, blks_read, blks_hit, round( blks_hit * 100.0 / NULLIF(blks_hit blks_read, 0), 2 ) AS cache_hit_percent FROM pg_stat_database WHERE datname IS NOT NULL ORDER BY cache_hit_percent;缓存命中率高并不代表 SQL 一定正常还要结合执行计划、物理 I/O 延迟、工作集大小和访问模式判断。59. 查看表扫描情况SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch FROM pg_stat_user_tables ORDER BY seq_tup_read DESC LIMIT 20;60. 查看统计信息最近更新时间SELECT schemaname, relname, last_analyze, last_autoanalyze, analyze_count, autoanalyze_count FROM pg_stat_user_tables ORDER BY greatest(last_analyze, last_autoanalyze) NULLS FIRST;六、表、索引与空间分析61. 查看表总大小SELECT pg_size_pretty( pg_total_relation_size(public.table_name) ) AS total_size;总大小包括表数据索引TOAST 数据TOAST 索引。62. 分别查看表和索引大小SELECT pg_size_pretty( pg_relation_size(public.table_name) ) AS table_size, pg_size_pretty( pg_indexes_size(public.table_name) ) AS index_size, pg_size_pretty( pg_total_relation_size(public.table_name) ) AS total_size;63. 查看最大的表SELECT schemaname, relname, pg_size_pretty( pg_total_relation_size(relid) ) AS total_size FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;64. 查看索引在psql中\di public.*SQL 方式SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname public ORDER BY tablename, indexname;65. 查看表的所有索引定义SELECT indexname, indexdef FROM pg_indexes WHERE schemaname public AND tablename table_name;66. 查看索引使用情况SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty( pg_relation_size(indexrelid) ) AS index_size FROM pg_stat_user_indexes ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;67. 查看未使用索引SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan, pg_size_pretty( pg_relation_size(indexrelid) ) AS index_size FROM pg_stat_user_indexes WHERE idx_scan 0 ORDER BY pg_relation_size(indexrelid) DESC;不能因为idx_scan 0就直接删除索引需要同时确认统计信息是否刚重置实例是否刚重启是否为唯一约束索引是否用于低频但关键的月末或年末任务是否被外键关联查询使用是否作为备用执行计划存在。68. 查看重复索引定义SELECT indrelid::regclass AS table_name, array_agg(indexrelid::regclass) AS indexes, pg_get_indexdef(indexrelid) AS index_definition FROM pg_index GROUP BY indrelid, indkey, indclass, indcollation, indexprs, indpred, pg_get_indexdef(indexrelid) HAVING count(*) 1;实际判断重复索引时应重点比较索引列、顺序、排序方式、表达式和过滤条件不能只比较索引名称。69. 查看无效索引SELECT n.nspname AS schema_name, t.relname AS table_name, i.relname AS index_name FROM pg_index x JOIN pg_class i ON i.oid x.indexrelid JOIN pg_class t ON t.oid x.indrelid JOIN pg_namespace n ON n.oid t.relnamespace WHERE NOT x.indisvalid ORDER BY n.nspname, t.relname;并发创建或重建索引失败后可能留下无效索引。70. 在线创建索引CREATE INDEX CONCURRENTLY idx_table_name_col ON public.table_name(col_name);CONCURRENTLY可以降低创建索引期间对业务 DML 的阻塞但执行时间通常更长资源消耗也可能更高而且不能在显式事务块中执行。71. 在线重建索引REINDEX INDEX CONCURRENTLY public.idx_table_name_col;普通REINDEX默认需要较强的表锁。在支持的版本中生产环境通常优先评估REINDEX CONCURRENTLY。72. 查看表的行数估算SELECT relname, reltuples::bigint AS estimated_rows FROM pg_class WHERE oid public.table_name::regclass;这是统计信息中的估算值并不是精确行数。精确统计需要执行SELECT count(*) FROM public.table_name;对于超大表count(*)可能执行很久并产生大量 I/O。73. 查看表的活跃与死亡元组SELECT schemaname, relname, n_live_tup, n_dead_tup, round( n_dead_tup * 100.0 / NULLIF(n_live_tup n_dead_tup, 0), 2 ) AS dead_tuple_percent FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;74. 查看表膨胀相关指标SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count, pg_size_pretty( pg_total_relation_size(relid) ) AS total_size FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;n_dead_tup只是估算值不能单独作为表膨胀比例。准确判断还需结合pgstattuple、表文件大小、历史数据量和业务更新模型。75. 查看 TOAST 表大小SELECT c.oid::regclass AS table_name, c.reltoastrelid::regclass AS toast_table, pg_size_pretty( pg_total_relation_size(c.reltoastrelid) ) AS toast_size FROM pg_class c WHERE c.oid public.table_name::regclass;七、Vacuum、Autovacuum 与统计信息PostgreSQL 采用 MVCC。更新和删除通常不会立即覆盖原有行版本而是产生可清理的死亡元组。因此Vacuum 不是可有可无的“优化动作”而是 PostgreSQL 日常运行机制的重要组成部分。官方文档也明确指出PostgreSQL 数据库需要定期执行 Vacuum大部分环境由 Autovacuum 自动完成。76. 手工执行 VacuumVACUUM public.table_name;普通VACUUM清理可回收的死亡元组使空间可以被后续数据复用通常不会把表文件空间归还给操作系统。77. 执行 Vacuum AnalyzeVACUUM (ANALYZE) public.table_name;也可以写成VACUUM ANALYZE public.table_name;它会先执行 Vacuum再收集优化器统计信息。78. 显示 Vacuum 详细输出VACUUM (VERBOSE, ANALYZE) public.table_name;79. 执行 Vacuum FullVACUUM FULL public.table_name;VACUUM FULL会重写整张表将可释放空间归还给操作系统但需要强锁并可能产生较高的 I/O 和额外临时空间需求。生产大表上不能把它当作常规维护命令。80. 单独收集统计信息ANALYZE public.table_name;指定字段ANALYZE public.table_name(col1, col2);81. 提高字段统计信息目标值ALTER TABLE public.table_name ALTER COLUMN col_name SET STATISTICS 1000;然后重新收集ANALYZE public.table_name;适用于数据分布倾斜、默认统计信息粒度不足导致优化器行数估算明显失真的字段。82. 查看 Autovacuum 配置SELECT name, setting, unit, source FROM pg_settings WHERE name LIKE autovacuum% ORDER BY name;83. 查看正在执行的 VacuumSELECT pid, datname, relid::regclass AS table_name, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, num_dead_item_ids FROM pg_stat_progress_vacuum;PostgreSQL 能够为VACUUM、ANALYZE、CREATE INDEX、CLUSTER、COPY和基础备份等操作提供进度视图。84. 查看 Autovacuum WorkerSELECT pid, datname, usename, backend_type, query_start, wait_event_type, wait_event, query FROM pg_stat_activity WHERE backend_type autovacuum worker;85. 查看事务年龄和冻结风险SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY age(datfrozenxid) DESC;查看表级冻结年龄SELECT n.nspname AS schema_name, c.relname AS table_name, age(c.relfrozenxid) AS xid_age FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind IN (r, m) ORDER BY age(c.relfrozenxid) DESC LIMIT 20;事务 ID 年龄过高可能导致数据库进入防止事务 ID 回卷的保护状态是 PostgreSQL DBA 必须监控的指标。八、WAL、检查点与归档86. 查看当前 WAL 位置SELECT pg_current_wal_lsn();87. 查看 WAL 文件名SELECT pg_walfile_name(pg_current_wal_lsn());88. 计算两个 WAL 位置的差值SELECT pg_size_pretty( pg_wal_lsn_diff( 0/5000000::pg_lsn, 0/4000000::pg_lsn ) );89. 查看 WAL 配置SELECT name, setting, unit, source FROM pg_settings WHERE name IN ( wal_level, max_wal_size, min_wal_size, wal_buffers, wal_compression, checkpoint_timeout, checkpoint_completion_target, archive_mode, archive_command ) ORDER BY name;90. 查看 WAL 统计信息SELECT * FROM pg_stat_wal;常见字段包括wal_recordswal_fpiwal_byteswal_buffers_fullwal_writewal_sync91. 查看归档状态SELECT * FROM pg_stat_archiver;重点关注archived_countfailed_countlast_archived_wallast_archived_timelast_failed_wallast_failed_time归档持续失败可能导致pg_wal目录不断增长。92. 手工切换 WALSELECT pg_switch_wal();通常用于测试归档链路触发当前 WAL 文件归档备份流程恢复验证。不应在高频循环中随意执行。九、流复制与复制槽93. 查看当前节点是否处于恢复状态SELECT pg_is_in_recovery();返回false通常为主库true通常为物理备库。94. 查看主库复制状态SELECT pid, usename, application_name, client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;官方文档说明主库可以通过pg_stat_replication查看 WAL Sender备库可以通过pg_stat_wal_receiver查看 WAL Receiver。95. 计算各备库复制延迟SELECT application_name, client_addr, state, sync_state, pg_size_pretty( pg_wal_lsn_diff( pg_current_wal_lsn(), replay_lsn ) ) AS replay_lag_bytes, replay_lag FROM pg_stat_replication ORDER BY pg_wal_lsn_diff( pg_current_wal_lsn(), replay_lsn ) DESC;replay_lag是时间维度LSN 差值是 WAL 字节维度两者应该结合分析。96. 查看备库 WAL 接收状态SELECT * FROM pg_stat_wal_receiver;97. 查看备库回放位置和延迟时间SELECT pg_last_wal_receive_lsn() AS receive_lsn, pg_last_wal_replay_lsn() AS replay_lsn, pg_size_pretty( pg_wal_lsn_diff( pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn() ) ) AS receive_replay_gap, now() - pg_last_xact_replay_timestamp() AS replay_delay;需要注意当主库长时间没有事务提交时时间差值可能持续增大并不一定代表复制正在延迟。98. 查看复制槽SELECT slot_name, slot_type, database, active, active_pid, restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size FROM pg_replication_slots;复制槽能够防止主库过早删除消费者尚未使用的 WAL。但如果复制槽长期不消费主库可能持续保留 WAL最终导致磁盘空间耗尽。查看复制槽保留的 WAL 大小SELECT slot_name, slot_type, active, pg_size_pretty( pg_wal_lsn_diff( pg_current_wal_lsn(), restart_lsn ) ) AS retained_wal FROM pg_replication_slots WHERE restart_lsn IS NOT NULL ORDER BY pg_wal_lsn_diff( pg_current_wal_lsn(), restart_lsn ) DESC;十、备份、恢复与权限管理99. 使用 pg_dump 进行逻辑备份备份单个数据库pg_dump \ -h 127.0.0.1 \ -p 5432 \ -U backup_user \ -F c \ -f appdb_$(date %F).dump \ appdb其中-F c使用 Custom 格式-f指定输出文件Custom 格式支持通过pg_restore选择对象并行恢复。并行备份需要使用 Directory 格式pg_dump \ -h 127.0.0.1 \ -p 5432 \ -U backup_user \ -F d \ -j 8 \ -f appdb_dir \ appdb恢复 Custom 格式备份createdb \ -h 127.0.0.1 \ -p 5432 \ -U postgres \ appdb_restorepg_restore \ -h 127.0.0.1 \ -p 5432 \ -U postgres \ -d appdb_restore \ -j 8 \ appdb_2026-07-17.dump备份全局对象pg_dumpall \ -h 127.0.0.1 \ -p 5432 \ -U postgres \ --globals-only \ globals_$(date %F).sqlPostgreSQL 官方将备份方式概括为 SQL Dump、文件系统级备份和连续归档三类。物理基础备份pg_basebackup \ -h 10.0.0.10 \ -p 5432 \ -U repl_user \ -D /backup/base_$(date %F) \ -Fp \ -Xs \ -P \ -Rpg_basebackup可以对运行中的 PostgreSQL 集群创建基础备份可用于时间点恢复也可作为流复制备库的初始数据。验证基础备份pg_verifybackup /backup/base_2026-07-17pg_verifybackup会根据pg_basebackup生成的备份清单验证基础备份完整性。100. 用户、角色与权限管理查看所有角色\duSQL 方式SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolconnlimit FROM pg_roles ORDER BY rolname;创建登录用户CREATE ROLE app_user LOGIN PASSWORD StrongPassword;创建只读角色CREATE ROLE app_readonly NOLOGIN;允许连接数据库GRANT CONNECT ON DATABASE appdb TO app_readonly;授权使用 SchemaGRANT USAGE ON SCHEMA public TO app_readonly;授权读取现有表GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;授权读取现有序列GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;配置以后新建表的默认权限ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;将只读角色授予具体用户GRANT app_readonly TO app_user;查看表权限\dp public.table_name查看用户成员关系SELECT member.rolname AS member_name, role.rolname AS granted_role FROM pg_auth_members m JOIN pg_roles role ON role.oid m.roleid JOIN pg_roles member ON member.oid m.member ORDER BY member.rolname, role.rolname;修改密码ALTER ROLE app_user PASSWORD NewStrongPassword;禁止登录ALTER ROLE app_user NOLOGIN;限制连接数量ALTER ROLE app_user CONNECTION LIMIT 20;删除用户DROP ROLE app_user;删除前需要确认该用户是否拥有对象或仍被授予权限。补充PostgreSQL DBA 常用的 psql 命令除了 SQLDBA 还需要熟悉psql自带的反斜杠命令。\l查看数据库。\c appdb切换数据库。\dn查看 Schema。\dt查看表。\d public.table_name查看表详细结构。\di查看索引。\dv查看视图。\dm查看物化视图。\df查看函数。\du查看角色。\dx查看扩展。\x切换扩展显示模式查看宽表结果时非常实用。\timing on显示 SQL 执行时间。\watch 2每两秒重复执行上一条 SQL适合实时观察连接数、复制延迟、Vacuum 进度等指标。\o output.txt将查询结果输出到文件。\copy public.table_name TO /tmp/table.csv CSV HEADER通过客户端导出 CSV。\q退出psql。PostgreSQL 故障排查的正确顺序真正有价值的不是把这 100 条命令全部背下来而是知道什么时候使用哪一类命令。当 PostgreSQL 业务出现卡顿时可以按照下面的顺序排查。第一步检查连接和正在执行的 SQL重点查看SELECT * FROM pg_stat_activity;确认是否存在连接数暴增长时间运行 SQL大量空闲连接idle in transaction相同 SQL 集中并发执行明显异常的等待事件。第二步检查长事务和锁等待重点查看SELECT * FROM pg_locks WHERE NOT granted;以及SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0;很多 PostgreSQL 卡顿问题并不是 SQL 本身执行慢而是 SQL 在等待另外一个长事务释放锁。第三步检查 SQL 执行计划和历史负载当前 SQL 使用EXPLAIN (ANALYZE, BUFFERS) SELECT ...;历史 SQL 使用SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC;既要看单次执行很慢的 SQL也要看单次不慢但执行次数极高的 SQL。第四步检查死亡元组和 AutovacuumSELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;如果存在大量死亡元组还要继续判断是否有长事务阻止清理Autovacuum 是否被关闭Autovacuum 参数是否过于保守表级 Autovacuum 参数是否合理是否存在持续高频更新是否出现事务 ID 冻结风险。第五步检查 WAL 和复制主库检查SELECT * FROM pg_stat_replication;备库检查SELECT * FROM pg_stat_wal_receiver;复制槽检查SELECT * FROM pg_replication_slots;如果pg_wal目录持续增长除了检查归档失败还必须检查失效或长期不消费的复制槽。第六步再考虑参数和系统资源只有确认连接、SQL、锁、事务、Vacuum、WAL 和复制状态后才应该进一步检查shared_bufferswork_memmaintenance_work_memeffective_cache_sizemax_connectionscheckpoint_timeoutmax_wal_sizeautovacuum_max_workersautovacuum_vacuum_scale_factor参数调整不能替代 SQL 优化也不能解决长事务、锁等待和应用连接管理问题。总结PostgreSQL DBA 与其他数据库 DBA 最大的区别之一是必须真正理解 MVCC、Vacuum、WAL 和事务可见性机制。看到表空间增长不能立即执行VACUUM FULL看到查询慢不能只想着加索引看到备库延迟也不能只盯着时间字段。很多现象背后可能是一个长期未提交事务、一条数据分布估算错误的 SQL、一个停止消费的复制槽或者一次没有及时完成的 Autovacuum。这 100 条命令覆盖了 PostgreSQL 日常运维的大部分基础入口但命令只是工具。一个成熟 DBA 的核心能力仍然是根据会话、锁、事务、执行计划、统计信息和 WAL 之间的关系建立完整的故障因果链。