psql / pg_dump —— PostgreSQL 交互与备份

分类：08 数据库工具 适用：PostgreSQL 12 ~ 17 一句话：psql 是 PG 的交互终端，pg_dump 是逻辑备份工具，两者是 PG 运维的最低配置。

1. 连接 psql

psql -h 10.0.0.5 -p 5432 -U app -d mydb
psql -h /var/run/postgresql -U postgres -d postgres      # Unix socket
psql "postgresql://app:pass@10.0.0.5:5432/mydb?sslmode=require"
psql -U app -d mydb -c "select version();"
psql -U app -d mydb -f /tmp/patch.sql

免密：~/.pgpass

10.0.0.5:5432:mydb:app:password

chmod 600 ~/.pgpass

或环境变量 PGPASSWORD（不推荐）、PGHOST/PGPORT/PGUSER/PGDATABASE。

2. 元命令（\ 开头）

3. 运维必查

3.1 活动会话

select pid, usename, client_addr, state,
       now() - query_start as duration,
       wait_event_type, wait_event, left(query, 80)
from pg_stat_activity
where state <> 'idle'
order by duration desc;

-- 按状态统计
select state, count(*) from pg_stat_activity group by state;

-- 找长事务
select pid, now() - xact_start as xact_age, state, query
from pg_stat_activity
where xact_start is not null
order by xact_age desc limit 10;

3.2 锁与阻塞

-- 谁在等锁
select
  blocked.pid as blocked_pid,
  blocked.query as blocked_query,
  blocking.pid as blocking_pid,
  blocking.query as blocking_query
from pg_stat_activity blocked
join pg_locks bl on bl.pid = blocked.pid and not bl.granted
join pg_locks kl on kl.locktype = bl.locktype
  and kl.database is not distinct from bl.database
  and kl.relation is not distinct from bl.relation
  and kl.granted
join pg_stat_activity blocking on blocking.pid = kl.pid
where blocked.pid <> blocking.pid;

处理：

select pg_cancel_backend(<pid>);      -- 取消当前查询（温和）
select pg_terminate_backend(<pid>);   -- 断开连接（强制）

3.3 库表大小

select datname, pg_size_pretty(pg_database_size(datname))
from pg_database order by pg_database_size(datname) desc;

select relname,
       pg_size_pretty(pg_total_relation_size(relid)) as total,
       pg_size_pretty(pg_relation_size(relid)) as table_only,
       pg_size_pretty(pg_indexes_size(relid)) as indexes
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc limit 20;

3.4 性能

-- 最耗时的查询（需 pg_stat_statements 扩展）
select calls, round(total_exec_time::numeric,2) as total_ms,
       round(mean_exec_time::numeric,2) as mean_ms,
       left(query, 100)
from pg_stat_statements
order by total_exec_time desc limit 20;

-- 表膨胀/死元组
select relname, n_live_tup, n_dead_tup,
       round(n_dead_tup*100.0/nullif(n_live_tup+n_dead_tup,0), 2) as dead_pct
from pg_stat_user_tables
order by n_dead_tup desc limit 20;

-- 缓存命中率
select sum(blks_hit)*100.0/nullif(sum(blks_hit+blks_read),0) as hit_ratio
from pg_stat_database;

4. 备份：pg_dump

# 自定义格式（推荐，支持并行恢复与选择性恢复）
pg_dump -h host -U app -Fc -f mydb.dump mydb

# 纯 SQL
pg_dump -h host -U app -f mydb.sql mydb

# 目录格式（支持并行）
pg_dump -h host -U app -Fd -j 4 -f /backup/mydb.dir mydb

# 只导结构 / 只导数据
pg_dump -s -f schema.sql mydb
pg_dump -a -f data.sql mydb

# 只导某表
pg_dump -t orders -f orders.sql mydb
pg_dump -t 'public.order*' -f orders.sql mydb

# 排除表
pg_dump -T audit_log -f mydb.sql mydb

# 压缩
pg_dump -Fc -Z 6 -f mydb.dump mydb

pg_dumpall（含角色与所有库）：

pg_dumpall -U postgres -f all.sql
pg_dumpall -U postgres --globals-only -f roles.sql

5. 恢复

# 纯 SQL
psql -U app -d mydb -f mydb.sql

# 自定义格式
pg_restore -U app -d mydb -Fc mydb.dump
pg_restore -U app -d mydb -j 4 mydb.dump          # 并行
pg_restore -U app -d mydb -t orders mydb.dump      # 单表
pg_restore -U app -d mydb --no-owner --no-privileges mydb.dump

# 目录格式
pg_restore -U app -d mydb -j 4 /backup/mydb.dir

# 建空库再恢复（pg_restore 不会自动建库）
createdb -h host -U app mydb_new
pg_restore -h host -U app -d mydb_new -Fc mydb.dump

-j 并行只对 -Fd 和 -Fc 有效，纯 SQL 不支持。

6. 实战场景

6.1 每日备份脚本

#!/bin/bash
set -euo pipefail

export PGHOST=10.0.0.5 PGPORT=5432 PGUSER=backup PGDATABASE=mydb
DIR=/backup/pg
DAY=$(date +%F)
mkdir -p "$DIR"

pg_dump -Fc -Z 6 -f "$DIR/mydb-$DAY.dump"
pg_restore -l "$DIR/mydb-$DAY.dump" > "$DIR/mydb-$DAY.toc"   # 校验可读

find "$DIR" -name '*.dump' -mtime +14 -delete
echo "$(date) ok $(du -h "$DIR/mydb-$DAY.dump" | cut -f1)"

.pgpass 存放密码，chmod 600。

6.2 迁移到新实例

# 1. 导出（含角色）
pg_dumpall -U postgres --globals-only -f /tmp/roles.sql
pg_dump -Fc -f /tmp/mydb.dump mydb

# 2. 目标端建角色与库
psql -U postgres -f /tmp/roles.sql
createdb -U postgres mydb

# 3. 恢复
pg_restore -U postgres -d mydb -j 4 /tmp/mydb.dump

# 4. 校验行数
psql -U postgres -d mydb -c "select count(*) from orders"

6.3 恢复误删的单表

pg_restore -U app -d mydb -t orders --data-only /backup/mydb-2026-11-04.dump

先确认备份里的表结构与当前一致，必要时先 TRUNCATE。

6.4 排查"连接数满"

show max_connections;
select count(*), state from pg_stat_activity group by state;
select usename, client_addr, count(*) from pg_stat_activity group by 1,2 order by 3 desc;

PG 的 max_connections 是硬上限，连接池（PgBouncer）是标准解法。

6.5 检查复制状态

-- 主库
select client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) as lag_bytes
from pg_stat_replication;

-- 从库
select now() - pg_last_xact_replay_timestamp() as replay_delay;

7. 常见坑

1. pg_dump 版本低于服务端：可能报错或遗漏新特性，用与服务端同版本的客户端。
2. pg_dump 会持有 ACCESS SHARE 锁：DDL 会被阻塞，备份期间避免改表结构。
3. pg_restore 不建库：必须先 createdb。
4. -j 对纯 SQL 无效：-Fp 不能用并行。
5. 序列（sequence）当前值：pg_dump 会导，但手工迁移数据时要单独同步，否则主键冲突。
6. 大对象（lo）：默认不导，需 -b（--blobs）。
7. --no-owner 忘了加：目标环境没有同名角色会报错。
8. pg_dumpall 含密码哈希：文件敏感，注意权限。
9. \copy 与 COPY 不同：\copy 在客户端读写文件，COPY 在服务端读写，权限与路径完全不同。
10. psql -c 多条语句：可以用 ; 分隔，但事务行为与 -f 不同。
11. pg_stat_activity 的 query 被截断：track_activity_query_size 默认 1024。
12. pg_terminate_backend 需要权限：普通用户只能杀自己的会话。
13. 时区：timestamptz 按会话时区显示，导出/导入时统一 SET TIME ZONE。
14. 大表 count(*) 很慢：PG 的 MVCC 需要全扫，估算用 pg_class.reltuples。

8. 速查表

psql -h <h> -p 5432 -U <u> -d <db>
psql -c "SQL" / -f file.sql

\l  \c db  \dt  \dt+  \d table  \dn  \du  \df  \x  \timing  \watch 5
\copy (select ...) to '/tmp/x.csv' csv header
\copy table from '/tmp/x.csv' csv header

-- 会话与锁
select * from pg_stat_activity where state <> 'idle';
select pg_cancel_backend(pid); / pg_terminate_backend(pid);

-- 备份
pg_dump -Fc -f x.dump db
pg_dump -Fd -j 4 -f x.dir db
pg_dump -s / -a / -t table / -T table
pg_dumpall --globals-only > roles.sql

-- 恢复
createdb db && pg_restore -d db -j 4 x.dump
pg_restore -d db -t table --data-only x.dump