postgresql

PostgreSQL 服务端启动、配置与运维

补充说明

postgresql 是 PostgreSQL 服务端，采用多进程模型：postmaster 监听连接并为每个连接 fork 后端进程，共享缓冲区通过共享内存访问，WAL 保证崩溃恢复和复制。它默认使用 MVCC，更新产生新版本，旧版本由 VACUUM 回收，因此长事务和长时间空闲事务会阻塞清理。

它适合强事务、复杂查询、JSONB、地理/向量扩展和读写分离场景。与 MySQL 相比，PostgreSQL 没有 undo log，回滚和清理依赖 WAL 与 VACUUM；复制默认是物理流复制，逻辑复制按表发布订阅。客户端操作见 psql 页面，连接池见 pgbouncer。

生产上要区分“启动服务”和“启动实例”：systemd 管理服务，pg_ctl 管理数据目录；配置文件是 postgresql.conf 和 pg_hba.conf，改完先 pg_ctl reload 或 SELECT pg_reload_conf()，涉及 shared_buffers、wal_level 等参数则要重启。

常用示例

pg_ctl -D /var/lib/postgresql/data -l /var/log/postgresql/server.log start
pg_ctl -D /var/lib/postgresql/data status
pg_ctl -D /var/lib/postgresql/data reload
pg_ctl -D /var/lib/postgresql/data stop -m fast
pg_isready -h 127.0.0.1 -p 5432
systemctl status postgresql
systemctl reload postgresql
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
psql -h 127.0.0.1 -U postgres -d postgres -c "SELECT version();"
psql -h 127.0.0.1 -U postgres -d postgres -c "SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> 'idle';"
psql -h 127.0.0.1 -U postgres -d postgres -c "SELECT * FROM pg_stat_replication;"
psql -h 127.0.0.1 -U postgres -d postgres -c "SELECT pid, pg_blocking_pids(pid) AS blockers, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;"
pg_basebackup -h primary -D /var/lib/postgresql/data -U replicator -Fp -Xs -P -R
pg_dump -Fc -d appdb -f /backup/appdb.dump
pg_restore -d appdb -j 4 /backup/appdb.dump
vacuumdb --analyze-only -d appdb -j 4

常见配置

postgresql.conf 生产基线（按内存和负载调整，不是默认值）：

listen_addresses = '*'
port = 5432
max_connections = 200
shared_buffers = 4GB
work_mem = 16MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
hot_standby = on
synchronous_commit = on
checkpoint_timeout = 15min
max_wal_size = 4GB
autovacuum = on
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05
log_min_duration_statement = 500ms
log_lock_waits = on
deadlock_timeout = 1s

pg_hba.conf 示例：

local   all         all                               peer
host    all         all         127.0.0.1/32          scram-sha-256
host    all         all         10.0.0.0/8            scram-sha-256
host    replication replicator  10.0.0.0/8            scram-sha-256

默认值提醒：shared_buffers=128MB、work_mem=4MB、maintenance_work_mem=64MB、max_connections=100、wal_level=replica、checkpoint_timeout=5min、autovacuum_naptime=1min、autovacuum_vacuum_scale_factor=0.2、autovacuum_analyze_scale_factor=0.1。pg_hba.conf 按第一条匹配生效，改完 reload。

易错点与排错

• connection refused：listen_addresses 默认只监听 localhost、端口不对、服务未启动或防火墙拦截；先 pg_isready、ss -lntp、pg_ctl status。
• no pg_hba.conf entry 或密码认证失败：规则按第一条匹配，local 与 host、CIDR、scram/md5 都可能不符；用 pg_hba_file_rules 核对后 reload。
• too many connections：连接数打满、应用池过大或连接泄漏；查 pg_stat_activity，预留 superuser 连接，并用 pgbouncer 收敛连接。
• idle in transaction 会持有快照、锁和 xmin，阻塞 VACUUM；设置 idle_in_transaction_session_timeout，并修正应用事务边界。
• 表膨胀：长事务、复制槽或 autovacuum 阈值不合理导致死元组堆积；看 n_dead_tup、last_autovacuum 和 pg_stat_progress_vacuum，慎用 VACUUM FULL。
• WAL/磁盘写满：max_wal_size 太小、归档命令失败或失效复制槽持有 WAL；检查 pg_wal、archive_command 和 pg_replication_slots。
• 复制延迟：在主库看 pg_stat_replication 的 sent/write/flush/replay LSN 与 lag，在从库看 pg_stat_wal_receiver；网络、IO 和同步备库都会拖慢。
• 复制中断：时间线不一致、所需 WAL 已被清理或 wal_keep_size 不足；通常需要重新 pg_basebackup 重建备库。
• 锁等待/死锁：用 pg_blocking_pids 找阻塞者，设置 lock_timeout/statement_timeout，统一加锁顺序；死锁日志看 deadlock detected。
• 备份可恢复性：pg_dump 是一致性逻辑备份但不支持 PITR；生产用 pg_basebackup + WAL 归档，且定期演练恢复。
• work_mem 是每个排序/哈希操作的上限，不是每连接；复杂查询和并发会把内存成倍放大，宁可会话级调大。
• 事务 ID 回卷风险：监控 age(datfrozenxid)，避免 autovacuum 被长期阻塞；接近上限时要尽快 freeze，而不是直接重启。

相关命令

• psql — PostgreSQL 命令行客户端
• pgbouncer — PostgreSQL 连接池
• patroni — PostgreSQL 高可用与选主
• postgres-autovacuum — autovacuum 与表膨胀排查
• postgres-cheatsheet — PostgreSQL 常用操作速查

读完自测

1. PostgreSQL 为什么需要 VACUUM，长事务会造成什么影响？
2. pg_hba.conf 的匹配顺序和常见认证失败原因是什么？
3. 物理流复制延迟应该看哪些视图和字段？