PostgreSQL 运维实战：个人站长该不该换、参数怎么调、pg_dump 备份恢复与膨胀处理

Author： zz1984
发布时间：September 28, 2026
9 views
No comments
8839 字数
Categories：技术教程

个人站长什么时候该从 MySQL 换到 PostgreSQL

绝大多数用 PHP 建站的站长，数据库默认就是 MySQL 或者 MariaDB，这没什么问题——生态成熟、教程多、随便一个虚拟主机都给。但当站点长到一定规模，或者开始做一些 MySQL 不太擅长的事情时，就会遇到天花板。以下是几个真实的信号，出现其中两个以上就值得认真考虑 PostgreSQL 了。

第一个信号是你要做复杂查询和分析。比如"统计每个分类下文章的平均阅读时长，并且按月份分组，还要算出环比增长率"——这类带窗口函数、CTE、多表聚合的需求，MySQL 8.0 之前基本没法写，只能把数据拉到 PHP 里处理。PostgreSQL 的窗口函数、WITH 递归查询、LATERAL JOIN 都是多年前就成熟的特性，而且查询规划器对复杂联结的优化明显更好。

第二个信号是你需要存储半结构化数据。文章里带标签数组、配置里带 JSON 字段、用户行为日志里带嵌套对象——PostgreSQL 的 jsonb 类型是二进制存储、可建索引、可以用 ->>、@> 这类操作符直接查询。MySQL 的 JSON 类型虽然也有，但索引能力和查询表达力差一截，很多场景还是要靠应用层处理。

第三个信号是你遇到过 DDL 锁表。MySQL 加一个字段、改一个索引，在表大的时候可能锁住整张表几分钟，站点直接 502。PostgreSQL 的 ALTER TABLE 大多只加短的排它锁，配合 CREATE INDEX CONCURRENTLY 可以在线建索引不阻塞读写。对 7x24 有人访问的站点，这个差别很实际。

反过来说，如果你的站就是几千篇文章、访问量一天几百，MySQL 完全够用，换过去只是给自己找事。技术选型要服务于问题，不要为了"更先进"而迁移。

在 Debian 上装 PostgreSQL 并调好第一版配置

Debian 12 自带的是 PostgreSQL 15，装起来很快：

apt update
apt install -y postgresql postgresql-contrib
systemctl enable --now postgresql
systemctl status postgresql
psql --version

安装完默认会创建一个 postgres 系统用户和一个同名数据库角色。日常操作要么 sudo -u postgres psql，要么给自己建一个超级用户。后者更方便：

sudo -u postgres createuser --interactive --pwprompt myname
# 回答：超级用户？y  创建数据库？n  创建角色？n
sudo -u postgres createdb -O myname mydb

然后编辑 /etc/postgresql/15/main/pg_hba.conf，把本地连接方式从 peer 改成 scram-sha-256，这样才能用密码从 TCP 连进来：

# TYPE  DATABASE  USER  ADDRESS       METHOD
local   all       all                 peer
host    all       all   127.0.0.1/32  scram-sha-256
host    all       all   ::1/128       scram-sha-256

改完执行 systemctl reload postgresql——注意是 reload 不是 restart，pg_hba.conf 支持热加载，正在跑的连接不会被掐断。

postgresql.conf 里真正该改的几个参数

网上的调优教程动辄让你改几十个参数，但对一台 2 核 4G 的机器，真正有意义的就是下面这几个：

# /etc/postgresql/15/main/postgresql.conf
listen_addresses = 'localhost'        # 只监听本机，除非确实需要远程
max_connections = 100                 # 别设太大，连接多反而慢
shared_buffers = 1GB                  # 内存的 25% 左右
effective_cache_size = 3GB            # 内存的 75%，只是给规划器的提示
work_mem = 16MB                       # 每个排序/哈希操作可用内存，注意是"每操作"
maintenance_work_mem = 256MB          # VACUUM、建索引时用
wal_level = replica                   # 想用流复制或者逻辑复制就设这个
max_wal_size = 2GB
min_wal_size = 512MB
log_min_duration_statement = 1000     # 记录超过 1 秒的慢查询
log_line_prefix = '%m [%p] %q%u@%d '
log_checkpoints = on
log_lock_waits = on

这里最容易踩的坑是 work_mem。它不是全局预算，而是"每个连接每个排序操作"的上限。如果设为 64MB，有 50 个并发连接同时各跑了 3 个需要排序的查询，理论峰值就是 64MB × 150 = 9.6GB，机器直接 OOM。所以这个值要小，宁可用临时文件换内存安全。真要跑大查询就单独 SET work_mem = '256MB' 在那个会话里临时调高。

另一个坑是 shared_buffers 设得过大。它不是越大越好，超过系统内存的 40% 之后性能反而会下降，因为有双重缓存的问题。PG 依赖操作系统的页缓存，两者配合才高效。

改完参数 systemctl restart postgresql，然后用 SHOW shared_buffers; 确认生效。不要改完就不管——配置文件语法错误会导致 PG 起不来，先在 psql 里用 SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart; 检查哪些参数需要重启才生效。

pg_dump 备份与恢复：三个必知的实际问题

PostgreSQL 的逻辑备份主力是 pg_dump。基本用法：

pg_dump -U myname -d mydb -Fc -f /backup/mydb_$(date +%F).dump

-Fc 是自定义格式，压缩且支持并行恢复，是最推荐的格式。恢复用 pg_restore：

pg_restore -U myname -d mydb_new -j 4 /backup/mydb_2026-09-28.dump

-j 4 开四个并行任务，恢复大库能快好几倍。但并行只对 -Fc/-Fd 格式有效，纯 SQL 文本格式没法并行。

问题一：备份期间的表锁和数据一致性

很多人不知道 pg_dump（默认模式）会对每张表加 ACCESS SHARE 锁，并且在一个事务里保证一致性快照。这意味着备份过程中如果有人 ALTER TABLE 想加排它锁，会被阻塞。更麻烦的是，如果备份跑了两个小时，那个 ALTER 就等着两个小时。

如果你用的是较老的 PG 或者有大量长事务，备份还可能因为无法获取快照而失败，报 ERROR: canceling statement due to conflict with recovery。解决办法是装 pg_dump 时用 --no-synchronized-snapshots（多库并行时），或者干脆用物理备份方案。对个人站来说更实际的建议是：把备份时间安排在凌晨，并且控制备份时长。

问题二：恢复时的权限和扩展缺失

在 A 机器上 dump，到 B 机器上 restore，最常见的报错是：

ERROR: role "appuser" does not exist
ERROR: extension "pg_trgm" is not installed
ERROR: schema "myschema" does not exist

因为 pg_dump 默认只备份数据，不备份角色、用户和部分全局对象。目标库必须先建好对应的角色和扩展。做法是：

# 在目标机器上先创建依赖
sudo -u postgres createuser appuser
sudo -u postgres psql -d mydb_new -c 'CREATE EXTENSION IF NOT EXISTS pg_trgm;'
sudo -u postgres psql -d mydb_new -c 'CREATE SCHEMA myschema;'
# 然后恢复时忽略所有 owner 和权限，让当前用户接管
pg_restore -U myname -d mydb_new --no-owner --no-privileges -j 4 /backup/mydb.dump

--no-owner 和 --no-privileges 这两个参数在跨机器迁移时几乎是必加的。不加的话，恢复到最后会因为找不到原来那些角色而报一堆 warning（不影响数据，但日志很难看，而且权限确实没恢复对）。

问题三：备份文件是好的，但没验证过

这是最要命的问题。备份脚本天天跑，日志天天显示成功，但真需要恢复的时候才发现文件是空的、或者截断了。原因是 pg_dump 失败了脚本没检查退出码，cron 里的错误邮件又没人看。

正确的写法是检查退出码并且验证文件：

#!/bin/bash
set -euo pipefail
DEST=/backup
FILE="$DEST/mydb_$(date +%F).dump"
pg_dump -U myname -d mydb -Fc -f "$FILE"
# 检查文件大小是否合理
SIZE=$(stat -c%s "$FILE")
if [ "$SIZE" -lt 102400 ]; then
  echo "备份文件异常小: $SIZE bytes" >&2
  exit 1
fi
# 校验归档完整性
pg_restore -l "$FILE" > /dev/null || { echo "归档损坏" >&2; exit 1; }
echo "备份成功: $FILE ($SIZE bytes)"

pg_restore -l 只列归档目录不真正恢复，是检查文件完整性的轻量手段。跑完之后如果返回非零，说明归档有问题。这一步能拦住大部分"看起来成功实际损坏"的情况。

用 pg_stat_statements 找出真正慢的查询

和 MySQL 的慢查询日志比，PostgreSQL 更推荐用 pg_stat_statements 扩展，因为它记录的是聚合后的统计，能直接看到哪些查询累计耗时最多，而不是一条条散落的事件。

# 装扩展
apt install -y postgresql-contrib
sudo -u postgres psql -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;'

然后在 postgresql.conf 里加上：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

重启之后就能查了。下面这条是运维最常用的，按总耗时排序，找出"单个不一定慢但被调用很多次"的查询：

SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(100 * total_exec_time / sum(total_exec_time) OVER (), 2) AS pct,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

注意看 pct 这一列。经常会出现的情况是：一个看起来很简单的 SELECT 占了总耗时的 40%，因为它被调用了 20 万次；而那个"慢查询"其实只占 3%。优化前者（加索引、加缓存）的收益远大于折腾后者。只看 mean_exec_time 排序会完全错过这类问题。

另一个关键点是 mean_exec_time 和 mean_plan_time 的区别。如果规划时间占总时间比例很高，说明查询太复杂导致规划开销大，这时候考虑用 prepared statement 或者拆简单些。这部分信息 MySQL 的慢查询日志基本给不了。

定位到具体查询之后，用 EXPLAIN (ANALYZE, BUFFERS) 看执行计划。BUFFERS 这个选项一定要加，它能显示实际读了多少块、命中率多少——如果看到 read 远大于 hit，说明缓存没吃上，可能该加索引了。

日常维护：VACUUM、autovacuum 与膨胀

PostgreSQL 的 MVCC 机制决定了更新和删除不会立刻回收空间，而是留下"死元组"，由 VACUUM 清理。这和 MySQL 的 InnoDB 靠 undo log 的方式完全不同，是 PG 新手最容易忽略的运维点。

默认 autovacuum 是开着的，但它的触发阈值在大表上往往太保守：默认是"表的 20% + 50 行"发生变更才触发。一张一亿行的表要两千万行变更才 vacuum，早就膨胀得不像话了。建议针对大表单独设置：

ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01
);

怎么知道哪张表膨胀了？这个查询能看出每张表的死元组占比：

SELECT
  schemaname, relname,
  n_live_tup, n_dead_tup,
  round(100.0 * n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

如果 dead_pct 超过 20% 而且 last_autovacuum 是很久以前，说明 vacuum 跟不上。先用 VACUUM (ANALYZE, VERBOSE) tablename; 手工跑一次看能不能清掉。清不掉的（因为死元组还没到可回收的时机，或者被长事务挡住了）就需要 VACUUM FULL——但要注意 VACUUM FULL 会重写整张表并且持有排它锁，期间这张表完全不可访问，线上操作要谨慎，最好用 pg_repack 代替。

还有一点：长事务是 autovacuum 的天敌。一个开着几小时不提交的事务会阻止 vacuum 回收比它更早的所有死元组，导致膨胀不断累积。用这条查有没有长事务：

SELECT pid, now() - xact_start AS duration, state, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC
LIMIT 10;

发现 idle in transaction 状态持续很久的连接，多半是应用里拿了连接忘了提交或者回滚。这类问题在开发环境不会暴露，上生产之后随着并发上来才会显现。给应用层设置 idle_in_transaction_session_timeout 能让 PG 自动杀掉这类僵尸事务，是一个很实用的兜底。

Last modification：September 28th, 2026 at 08:26 pm

PostgreSQL 运维实战：个人站长该不该换、参数怎么调、pg_dump 备份恢复与膨胀处理

zz1984 • 2026 年 09 月 28 日

个人站长什么时候该从 MySQL 换到 PostgreSQL

在 Debian 上装 PostgreSQL 并调好第一版配置

Debian 12 自带的是 PostgreSQL 15，装起来很快：

apt update
apt install -y postgresql postgresql-contrib
systemctl enable --now postgresql
systemctl status postgresql
psql --version

安装完默认会创建一个 postgres 系统用户和一个同名数据库角色。日常操作要么 sudo -u postgres psql，要么给自己建一个超级用户。后者更方便：

sudo -u postgres createuser --interactive --pwprompt myname
# 回答：超级用户？y  创建数据库？n  创建角色？n
sudo -u postgres createdb -O myname mydb

然后编辑 /etc/postgresql/15/main/pg_hba.conf，把本地连接方式从 peer 改成 scram-sha-256，这样才能用密码从 TCP 连进来：

# TYPE  DATABASE  USER  ADDRESS       METHOD
local   all       all                 peer
host    all       all   127.0.0.1/32  scram-sha-256
host    all       all   ::1/128       scram-sha-256

改完执行 systemctl reload postgresql——注意是 reload 不是 restart，pg_hba.conf 支持热加载，正在跑的连接不会被掐断。

postgresql.conf 里真正该改的几个参数

网上的调优教程动辄让你改几十个参数，但对一台 2 核 4G 的机器，真正有意义的就是下面这几个：

# /etc/postgresql/15/main/postgresql.conf
listen_addresses = 'localhost'        # 只监听本机，除非确实需要远程
max_connections = 100                 # 别设太大，连接多反而慢
shared_buffers = 1GB                  # 内存的 25% 左右
effective_cache_size = 3GB            # 内存的 75%，只是给规划器的提示
work_mem = 16MB                       # 每个排序/哈希操作可用内存，注意是"每操作"
maintenance_work_mem = 256MB          # VACUUM、建索引时用
wal_level = replica                   # 想用流复制或者逻辑复制就设这个
max_wal_size = 2GB
min_wal_size = 512MB
log_min_duration_statement = 1000     # 记录超过 1 秒的慢查询
log_line_prefix = '%m [%p] %q%u@%d '
log_checkpoints = on
log_lock_waits = on

pg_dump 备份与恢复：三个必知的实际问题

PostgreSQL 的逻辑备份主力是 pg_dump。基本用法：

pg_dump -U myname -d mydb -Fc -f /backup/mydb_$(date +%F).dump

-Fc 是自定义格式，压缩且支持并行恢复，是最推荐的格式。恢复用 pg_restore：

pg_restore -U myname -d mydb_new -j 4 /backup/mydb_2026-09-28.dump

-j 4 开四个并行任务，恢复大库能快好几倍。但并行只对 -Fc/-Fd 格式有效，纯 SQL 文本格式没法并行。

问题一：备份期间的表锁和数据一致性

问题二：恢复时的权限和扩展缺失

在 A 机器上 dump，到 B 机器上 restore，最常见的报错是：

ERROR: role "appuser" does not exist
ERROR: extension "pg_trgm" is not installed
ERROR: schema "myschema" does not exist

因为 pg_dump 默认只备份数据，不备份角色、用户和部分全局对象。目标库必须先建好对应的角色和扩展。做法是：

# 在目标机器上先创建依赖
sudo -u postgres createuser appuser
sudo -u postgres psql -d mydb_new -c 'CREATE EXTENSION IF NOT EXISTS pg_trgm;'
sudo -u postgres psql -d mydb_new -c 'CREATE SCHEMA myschema;'
# 然后恢复时忽略所有 owner 和权限，让当前用户接管
pg_restore -U myname -d mydb_new --no-owner --no-privileges -j 4 /backup/mydb.dump

问题三：备份文件是好的，但没验证过

正确的写法是检查退出码并且验证文件：

#!/bin/bash
set -euo pipefail
DEST=/backup
FILE="$DEST/mydb_$(date +%F).dump"
pg_dump -U myname -d mydb -Fc -f "$FILE"
# 检查文件大小是否合理
SIZE=$(stat -c%s "$FILE")
if [ "$SIZE" -lt 102400 ]; then
  echo "备份文件异常小: $SIZE bytes" >&2
  exit 1
fi
# 校验归档完整性
pg_restore -l "$FILE" > /dev/null || { echo "归档损坏" >&2; exit 1; }
echo "备份成功: $FILE ($SIZE bytes)"

用 pg_stat_statements 找出真正慢的查询

# 装扩展
apt install -y postgresql-contrib
sudo -u postgres psql -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;'

然后在 postgresql.conf 里加上：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

重启之后就能查了。下面这条是运维最常用的，按总耗时排序，找出"单个不一定慢但被调用很多次"的查询：

SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(100 * total_exec_time / sum(total_exec_time) OVER (), 2) AS pct,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

日常维护：VACUUM、autovacuum 与膨胀

ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01
);

怎么知道哪张表膨胀了？这个查询能看出每张表的死元组占比：

SELECT
  schemaname, relname,
  n_live_tup, n_dead_tup,
  round(100.0 * n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT pid, now() - xact_start AS duration, state, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC
LIMIT 10;

PostgreSQL 运维实战：个人站长该不该换、参数怎么调、pg_dump 备份恢复与膨胀处理

个人站长什么时候该从 MySQL 换到 PostgreSQL

在 Debian 上装 PostgreSQL 并调好第一版配置

postgresql.conf 里真正该改的几个参数

pg_dump 备份与恢复：三个必知的实际问题

问题一：备份期间的表锁和数据一致性

问题二：恢复时的权限和扩展缺失

问题三：备份文件是好的，但没验证过

用 pg_stat_statements 找出真正慢的查询

日常维护：VACUUM、autovacuum 与膨胀

相关文章

Leave a Comment Cancel reply

自建CF节点之反代Cloudflare加速网站(Gost/iptables转发)

技术学习路径与资源导航

LayerStack：香港原生IP服务器无限流量四网直连外加双向cn2

js底部飘浮广告代码

lnmp 去除防跨域问题

2026年站长必备开源工具箱：十五款免费工具让你的网站运营效率翻倍

Linux 防火墙实战：iptables 与 firewalld 从入门到加固网站服务器

Docker 多容器部署后故障率反而上升：127.0.0.1、UID 权限、depends_on 三个必踩的坑

MySQL 数据库性能优化与安全加固实战指南

Docker 容器化部署实战指南：从入门到生产环境

PostgreSQL 运维实战：个人站长该不该换、参数怎么调、pg_dump 备份恢复与膨胀处理

个人站长什么时候该从 MySQL 换到 PostgreSQL

在 Debian 上装 PostgreSQL 并调好第一版配置

postgresql.conf 里真正该改的几个参数

pg_dump 备份与恢复：三个必知的实际问题

问题一：备份期间的表锁和数据一致性

问题二：恢复时的权限和扩展缺失

问题三：备份文件是好的，但没验证过

用 pg_stat_statements 找出真正慢的查询

日常维护：VACUUM、autovacuum 与膨胀