VPS 上的 PostgreSQL - 安装、2–8 GB 调优与备份

在 Ubuntu 上安装 PostgreSQL 16、创建用户与数据库、为 2/4/8 GB 服务器调优 shared_buffers 与 work_mem,并用 systemd 配置每日 pg_dump 备份。

PostgreSQL 的默认安装是按上世纪 90 年代的工作站调优的。在 2–8 GB 的 VPS 上,调整几个参数就能带来巨大差别:这里是附带真实数字的精简版。

1. 安装

apt update && apt install -y postgresql postgresql-contrib
systemctl enable --now postgresql
sudo -u postgres psql -c "SELECT version();"

Ubuntu 24.04 自带 PostgreSQL 16。这个版本足以应对您可能运行的任何场景。

2. 创建数据库和用户

sudo -u postgres psql <<'SQL'
CREATE USER app WITH PASSWORD 'strong-password';
CREATE DATABASE appdb OWNER app;
SQL
psql "postgresql://app:strong-password@localhost/appdb" -c "SELECT 1;"

切勿在应用中使用 postgres 超级用户。每个应用一个用户,每个项目一个数据库。

3. 按内存大小调优

编辑 `/etc/postgresql/16/main/postgresql.conf`,按服务器规模设置参数:

参数2 GB VPS4 GB VPS8 GB VPS
shared_buffers512MB1GB2GB
effective_cache_size1GB2.5GB5GB
work_mem16MB32MB48MB
maintenance_work_mem128MB256MB512MB
max_connections50100150
systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"

如果应用会打开数百个连接,请在前面加一层 PgBouncer,而不是调高 max_connections:每个连接都要占用内存。

4. 每日转储并轮换

cat > /usr/local/bin/pg-dump-all <<'EOF'
#!/bin/sh
set -e
DIR=/var/backups/postgres
mkdir -p "$DIR"
sudo -u postgres pg_dumpall --globals-only > "$DIR/globals.sql"
for db in $(sudo -u postgres psql -Atc "SELECT datname FROM pg_database WHERE datistemplate = false"); do
  sudo -u postgres pg_dump -Fc "$db" > "$DIR/$db.dump"
done
find "$DIR" -type f -mtime +7 -delete
EOF
chmod +x /usr/local/bin/pg-dump-all

用 `crontab -e` 添加到 cron:

30 3 * * * /usr/local/bin/pg-dump-all

转储是一致性的快照;直接复制运行中的数据目录则不是。之后请把转储文件发送到异地:这部分见我们的 restic 指南。

5. 恢复转储

sudo -u postgres createdb appdb_restore
sudo -u postgres pg_restore -d appdb_restore /var/backups/postgres/appdb.dump

常见问题

可以把 PostgreSQL 开放到互联网吗?

最好不要。让它只监听 localhost,由应用直接连接,或使用 SSH 隧道。如果必须暴露,请在防火墙中只允许您的 IP,并强制使用 TLS。

PostgreSQL 需要哪个套餐?

2 GB 内存足够开发环境和小型应用,4 GB 适合生产环境的网站,8 GB 可应对带缓存的繁忙数据库。在这里,NVMe 存储比多几个核心更重要。

如何找出慢查询?

启用 pg_stat_statements 扩展并按平均耗时排序。通常能发现一两个缺失的索引,它们就是大部分负载的原因。

一个宽裕的生产数据库从 Core VPS 6,€16/月起步。

相关内容

查看套餐指南常见问题