VPS 搭建 PostgreSQL 18：Docker、TLS、PgBouncer 与备份

把 PostgreSQL 放进 Docker 只需要几行 Compose，但“能启动”和“可以承载生产数据”差得很远。常见事故不是数据库不会跑，而是 5432 暴露公网、卷挂错目录、连接数把内存吃完、备份从未恢复过，或直接改镜像大版本导致数据目录不兼容。

本文以 Ubuntu 24.04、PostgreSQL 18.6、Docker Compose、TLS 和 PgBouncer 1.25.2 为基准，搭建一套适合中小型网站、API 和 SaaS 项目的单 VPS 数据库。方案包含最小暴露面、SCRAM 认证、连接池、资源参数、逻辑备份、恢复演练、升级与故障排查。

PostgreSQL 18 已于 2025 年 9 月正式发布，当前示例固定到 18.6 维护版，不使用 latest。18 带来异步 I/O、UUIDv7、虚拟生成列、默认启用数据页校验和等变化；真正决定生产可靠性的，仍然是访问边界、备份和容量管理。

为什么在 VPS 上自建 PostgreSQL 18

自建 PostgreSQL 适合这些场景：

应用和数据库位于同一台或同一私有网络内，希望减少托管数据库成本；
需要完整的扩展、参数、备份和日志控制权；
数据量不大，但对 SQL、事务、JSONB、全文检索或地理扩展有明确需求；
团队能负责安全更新、监控、备份恢复和故障值守。

如果业务不能接受单机停机、团队没有数据库运维能力，或需要自动故障转移、跨区容灾和时间点恢复，托管 PostgreSQL 通常更划算。本文的单 VPS 架构可以降低常见风险，但不等于高可用：宿主机、系统盘或机房故障仍会让数据库整体不可用。

VPS 配置怎么选

数据库比普通 Web 容器更依赖稳定内存、磁盘延迟和持续 IOPS。不要只看 CPU 核心数。

场景	建议配置	数据盘	说明
开发与低流量站点	2 vCPU、4GB 内存	60GB NVMe	数据库与应用同机，连接数要保守
小型生产	4 vCPU、8GB 内存	120GB NVMe 起	本文推荐起点，可启用 PgBouncer
中型生产	8 vCPU、16GB 内存起	独立 NVMe/块存储	建议数据库与应用分机

磁盘至少预留当前数据库体积的 2–3 倍空间，用于 WAL、索引重建、临时文件、备份和升级。先用 fio 和云厂商监控确认持续写入性能，不要只相信“NVMe”商品名。内存少于 4GB 时应先增加 Swap 防止突发 OOM，但 Swap 只能救急，不能替代内存。

网络和端口设计

端口	用途	本文策略
`5432/TCP`	PostgreSQL	只绑定宿主机 `127.0.0.1`，不对公网开放
`6432/TCP`	PgBouncer	只监听 `127.0.0.1`
`22/TCP`	SSH 管理与隧道	密钥登录，并限制来源

应用与数据库同机时走回环地址；远程管理使用 SSH 隧道、WireGuard 或零信任网络。不要因为配置了密码和 TLS 就把 5432 对全网开放。更多边界设计可参考 VPS 数据库不要直接暴露公网。

安装 Docker 并准备目录

先通过 Docker 官方仓库安装 Docker Engine 和 Compose v2，然后确认版本：

sudo apt update
sudo apt full-upgrade -y
sudo apt install -y ca-certificates curl openssl jq

docker --version
docker compose version

创建项目、配置、证书、初始化脚本和备份目录：

sudo install -d -m 0750 -o "$USER" -g "$USER" \
  /opt/postgres18/{conf,initdb,secrets,tls,backups}
cd /opt/postgres18

umask 077
openssl rand -hex 32 > secrets/postgres_superuser_password
openssl rand -hex 32 > secrets/app_db_password
chmod 600 secrets/*

不要把 secrets/、TLS 私钥或备份提交到 Git。数据库密码建议进入密码管理器或密钥系统；本文使用本地只读文件，避免把密码直接写进 Compose。

创建 PostgreSQL TLS 证书

本文使用私有 CA 给 PostgreSQL 签发服务器证书。它适合应用与数据库之间的内部 TLS；若客户端跨主机访问，应把真实私有 DNS 名加入 SAN，并安全分发 ca.crt。

创建 /opt/postgres18/tls/openssl.cnf：

[req]
distinguished_name = dn
prompt = no
req_extensions = server_ext

[dn]
CN = postgres

[server_ext]
subjectAltName = DNS:postgres,DNS:localhost,IP:127.0.0.1
keyUsage = critical,digitalSignature,keyEncipherment
extendedKeyUsage = serverAuth

生成 CA 和服务器证书：

cd /opt/postgres18/tls

openssl req -x509 -newkey rsa:4096 -sha256 -days 3650 -nodes \
  -subj "/CN=PostgreSQL Internal CA" \
  -keyout ca.key -out ca.crt

openssl req -newkey rsa:3072 -sha256 -nodes \
  -config openssl.cnf -keyout server.key -out server.csr

openssl x509 -req -sha256 -days 825 \
  -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
  -extfile openssl.cnf -extensions server_ext \
  -out server.crt

chmod 600 ca.key server.key
chmod 644 ca.crt server.crt
sudo chown 999:999 server.key server.crt

ca.key 只用于签发和续签，不要挂进数据库容器，也不要留在公开可读目录。将它单独加密备份。PostgreSQL 会拒绝权限过宽的服务器私钥，所以 server.key 必须保持严格权限。

编写 PostgreSQL 配置

创建 /opt/postgres18/conf/postgresql.conf：

listen_addresses = '*'
port = 5432

ssl = on
ssl_cert_file = '/run/postgres-tls/server.crt'
ssl_key_file = '/run/postgres-tls/server.key'
ssl_ca_file = '/run/postgres-tls/ca.crt'
ssl_min_protocol_version = 'TLSv1.2'

password_encryption = 'scram-sha-256'
shared_preload_libraries = 'pg_stat_statements'

max_connections = 100
shared_buffers = '2GB'
effective_cache_size = '5GB'
work_mem = '16MB'
maintenance_work_mem = '512MB'

checkpoint_timeout = '15min'
max_wal_size = '4GB'
min_wal_size = '1GB'
wal_compression = on

random_page_cost = 1.1
effective_io_concurrency = 200
track_io_timing = on

log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_min_duration_statement = 1000
deadlock_timeout = '1s'
timezone = 'UTC'

这组参数是 8GB 专用数据库 VPS 的保守起点，不是万能答案。shared_buffers 可从内存的约 25% 起步；work_mem 是每个排序或哈希节点都可能使用的额度，并发查询时总消耗会成倍增加。数据库与应用同机时，应把 shared_buffers 降到 1GB，并为系统和业务容器留足内存。

创建 /opt/postgres18/conf/pg_hba.conf：

local   all             postgres                                peer
local   all             all                                     scram-sha-256

hostssl all             all             0.0.0.0/0               scram-sha-256
hostssl all             all             ::0/0                    scram-sha-256
hostnossl all           all             0.0.0.0/0               reject
hostnossl all           all             ::0/0                    reject

hostssl 强制 TCP 客户端使用 TLS，SCRAM 避免旧式弱认证。这里看似允许全部地址，但 Compose 只把端口绑定到宿主机回环地址，云安全组和 UFW 也不开放 5432；网络层和数据库层共同收口。若以后改为私网跨机部署，应把 CIDR 精确限制为应用子网。

创建应用用户和数据库初始化脚本

创建 /opt/postgres18/initdb/01-app.sh：

#!/usr/bin/env bash
set -Eeuo pipefail

app_password="$(<"${APP_DB_PASSWORD_FILE}")"

psql -v ON_ERROR_STOP=1 \
  --username "${POSTGRES_USER}" \
  --dbname "${POSTGRES_DB}" \
  --set=app_password="${app_password}" <<'EOSQL'
SELECT format('CREATE ROLE appuser LOGIN PASSWORD %L', :'app_password')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'appuser')
\gexec

SELECT 'CREATE DATABASE app OWNER appuser'
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'app')
\gexec

REVOKE ALL ON DATABASE app FROM PUBLIC;
EOSQL

psql -v ON_ERROR_STOP=1 \
  --username "${POSTGRES_USER}" \
  --dbname app \
  -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;'

chmod 700 /opt/postgres18/initdb/01-app.sh

初始化脚本只会在空数据目录第一次启动时执行。已有卷不会因为修改密码文件而自动改密码；后续轮换要用 ALTER ROLE，同时更新应用和 PgBouncer 的凭据。

编写 Docker Compose

创建 /opt/postgres18/compose.yml：

name: postgres18

services:
  postgres:
    image: postgres:18.6-bookworm
    environment:
      POSTGRES_USER: postgres
      POSTGRES_DB: postgres
      POSTGRES_PASSWORD_FILE: /run/secrets/postgres_superuser_password
      APP_DB_PASSWORD_FILE: /run/secrets/app_db_password
    command:
      - postgres
      - -c
      - config_file=/etc/postgresql/postgresql.conf
      - -c
      - hba_file=/etc/postgresql/pg_hba.conf
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql
      - ./conf/postgresql.conf:/etc/postgresql/postgresql.conf:ro
      - ./conf/pg_hba.conf:/etc/postgresql/pg_hba.conf:ro
      - ./initdb:/docker-entrypoint-initdb.d:ro
      - ./tls/ca.crt:/run/postgres-tls/ca.crt:ro
      - ./tls/server.crt:/run/postgres-tls/server.crt:ro
      - ./tls/server.key:/run/postgres-tls/server.key:ro
    secrets:
      - postgres_superuser_password
      - app_db_password
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"]
      interval: 10s
      timeout: 5s
      retries: 10
      start_period: 30s
    shm_size: 1gb
    mem_limit: 6g
    restart: unless-stopped
    logging:
      driver: json-file
      options:
        max-size: 20m
        max-file: "5"

secrets:
  postgres_superuser_password:
    file: ./secrets/postgres_superuser_password
  app_db_password:
    file: ./secrets/app_db_password

volumes:
  postgres-data:

PostgreSQL 官方镜像从 18 起把 PGDATA 改为版本化子目录，卷应挂载到 /var/lib/postgresql。17 及以前常见的 /var/lib/postgresql/data 不能机械照搬到 18。挂错路径可能在重建容器或升级时造成“数据看似丢失”的错觉。

Compose 生产配置的健康检查、资源限制和日志轮转思路，可继续参考 Docker Compose 生产环境清单。

校验并首次启动

cd /opt/postgres18
docker compose config --quiet
docker compose pull
docker compose up -d
docker compose ps
docker compose logs --tail=100 postgres

确认版本、TLS、校验和和扩展：

docker compose exec --user postgres postgres \
  psql -U postgres -d postgres -c 'SELECT version();'

docker compose exec --user postgres postgres \
  psql -U postgres -d postgres -c 'SHOW ssl; SHOW data_checksums; SHOW shared_preload_libraries;'

docker compose exec --user postgres postgres \
  psql -U postgres -d app -c '\dx pg_stat_statements'

再从宿主机通过 TLS 验证应用账户。先安装客户端：

sudo apt install -y postgresql-client

APP_DB_PASSWORD="$(<secrets/app_db_password)" \
PGPASSWORD="${APP_DB_PASSWORD}" \
psql "host=127.0.0.1 port=5432 dbname=app user=appuser sslmode=verify-full sslrootcert=/opt/postgres18/tls/ca.crt" \
  -c 'SELECT current_user, current_database(), ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();'

输出中的 ssl 应为 t。测试完成后执行 unset APP_DB_PASSWORD PGPASSWORD，不要把带密码的连接串写进 Shell 历史。

安装 PgBouncer 连接池

PostgreSQL 每条后端连接都会占用资源。短请求、高并发 API 或 Serverless 客户端容易把 max_connections 打满；PgBouncer 在应用和数据库之间复用连接，让数据库只维护较少的真实会话。

先从 PostgreSQL 官方 APT 仓库安装 PgBouncer，并确认版本不低于 1.25.2：

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt update
sudo apt install -y pgbouncer

pgbouncer --version

1.25.2 修复了多项安全问题。若仓库候选版本更低，不要对外提供连接池服务，应先升级软件源或使用经过审计、固定摘要的镜像。

创建 /etc/pgbouncer/pgbouncer.ini：

[databases]
app = host=127.0.0.1 port=5432 dbname=app

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 300
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
max_prepared_statements = 200

server_tls_sslmode = verify-full
server_tls_ca_file = /etc/pgbouncer/ca.crt

admin_users = postgres
stats_users = postgres
ignore_startup_parameters = extra_float_digits
log_connections = 1
log_disconnections = 1

PgBouncer 的认证文件允许明文、MD5 或 SCRAM verifier。为了让连接池可以代表应用向后端完成 SCRAM 认证，这里使用受权限保护的明文文件：

cd /opt/postgres18
APP_DB_PASSWORD="$(<secrets/app_db_password)"
printf '"appuser" "%s"\n' "${APP_DB_PASSWORD}" > secrets/pgbouncer-userlist.txt
unset APP_DB_PASSWORD

sudo install -m 0640 -o postgres -g postgres \
  secrets/pgbouncer-userlist.txt /etc/pgbouncer/userlist.txt
sudo install -m 0644 -o root -g root \
  tls/ca.crt /etc/pgbouncer/ca.crt

sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncer
sudo systemctl status --no-pager pgbouncer

应用连接地址改为 127.0.0.1:6432/app。transaction 模式在每个事务结束后归还后端连接，吞吐更高，但不适合依赖会话级状态、临时表或跨事务 SET 的应用；遇到这类需求可为特定数据库改用 session 模式。max_client_conn 增大时还要检查 systemd 的文件描述符上限。

测试连接池：

APP_DB_PASSWORD="$(</opt/postgres18/secrets/app_db_password)" \
PGPASSWORD="${APP_DB_PASSWORD}" \
psql -h 127.0.0.1 -p 6432 -U appuser -d app \
  -c 'SELECT current_user, now();'

unset APP_DB_PASSWORD PGPASSWORD

备份：不要只备份 Docker Volume

数据库运行时直接复制卷目录，可能得到不一致的文件集合。中小数据库先用 pg_dump 做每日逻辑备份，同时备份角色等全局对象；数据量继续增长后，再加入 pg_basebackup、WAL 归档和时间点恢复。

手动生成一份可校验的备份：

cd /opt/postgres18
backup_time="$(date -u +%Y%m%dT%H%M%SZ)"

docker compose exec -T --user postgres postgres \
  pg_dump -U postgres -d app -Fc \
  > "backups/app-${backup_time}.dump"

docker compose exec -T --user postgres postgres \
  pg_dumpall -U postgres --globals-only \
  > "backups/globals-${backup_time}.sql"

sha256sum "backups/app-${backup_time}.dump" \
  "backups/globals-${backup_time}.sql" \
  > "backups/SHA256SUMS-${backup_time}"

备份至少保留三份：VPS 本地短期副本、另一地区对象存储副本、离线或独立账户副本。对象存储凭据应使用只能写指定备份桶的最小权限，启用版本控制或对象锁，并定期测试下载和校验哈希。

恢复演练

“备份成功”只说明命令退出，不代表能恢复。每月至少在临时数据库做一次演练：

cd /opt/postgres18
latest_dump="$(find backups -name 'app-*.dump' -type f -print | sort | tail -1)"

docker compose exec -T --user postgres postgres \
  createdb -U postgres -O appuser app_restore_test

docker compose exec -T --user postgres postgres \
  pg_restore -U postgres -d app_restore_test \
  --clean --if-exists < "${latest_dump}"

docker compose exec --user postgres postgres \
  psql -U postgres -d app_restore_test \
  -c "SELECT count(*) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema');"

docker compose exec -T --user postgres postgres \
  dropdb -U postgres app_restore_test

除了表数量，还要检查关键业务行数、扩展、序列、权限和应用能否连接。记录恢复耗时，才能得到真实的 RTO；仅保存备份文件无法证明恢复目标。

性能调优与监控

先观察，再改参数。建议至少监控：CPU、可用内存、Swap、磁盘空间、磁盘延迟、连接数、事务率、慢查询、锁等待、复制/WAL 状态和备份新鲜度。

查看连接和慢查询：

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

不要为了消除“too many clients”直接把 max_connections 从 100 改到 1000。更稳妥的顺序是：修复连接泄漏、缩短空闲事务、引入 PgBouncer、测量单连接内存，再决定是否提高上限。慢查询先用 EXPLAIN (ANALYZE, BUFFERS) 定位索引和执行计划，避免把所有问题都归因于 shared_buffers。

更新和升级

18.6 升到后续 18.x 属于小版本更新，通常不需要 pg_upgrade：

cd /opt/postgres18

: "先完成备份和恢复抽查"
docker compose pull postgres
docker compose up -d postgres
docker compose exec --user postgres postgres \
  psql -U postgres -d postgres -c 'SELECT version();'

生产环境应在维护窗口操作，并保留旧镜像摘要与备份。18 升到 19 属于大版本升级，不能只改镜像标签；应选择 pg_upgrade 或逻辑导出/导入，在副本环境排练后再切换。Docker 官方镜像 18+ 的版本化 PGDATA 目录正是为了让大版本升级布局更清晰，但它不会替你执行迁移。

常见故障排查

容器提示私钥权限错误

检查 server.key 是否由容器内 PostgreSQL 用户可读且未对组外开放：

sudo chown 999:999 /opt/postgres18/tls/server.key
sudo chmod 600 /opt/postgres18/tls/server.key
docker compose restart postgres

重建后看不到原数据库

先停止写入，不要反复执行初始化。检查卷是否仍存在、Compose 项目名是否改变，以及 18 的卷是否挂到 /var/lib/postgresql。不要删除旧卷。确认路径后再以只读方式挂载排查。

`remaining connection slots are reserved`

查看 pg_stat_activity 和 PgBouncer 的 SHOW POOLS;，区分连接泄漏、长事务和池容量不足。优先修复应用连接生命周期，再调整 default_pool_size 或 max_connections。

TLS 验证失败

确认客户端使用的主机名或 IP 存在于证书 SAN，sslrootcert 指向正确 CA，系统时间准确。verify-full 失败时不要临时改成 sslmode=disable，应修复证书链和名称。

5432 连接不上

本文故意只绑定 127.0.0.1。远程连接应先建立 SSH 隧道：

ssh -N -L 15432:127.0.0.1:6432 user@your-vps

然后客户端连接本机 127.0.0.1:15432。若仍失败，可按 PostgreSQL 连接与启动故障排查清单检查监听地址、认证规则、容器日志和防火墙。

上线检查清单

PostgreSQL 固定到明确的 18.x 维护版本，没有使用 latest；
数据卷挂到 18+ 正确的 /var/lib/postgresql；
5432 和 6432 都只监听回环或受控私网；
TCP 连接强制 TLS，账号使用 SCRAM，应用不使用超级用户；
PgBouncer 不低于 1.25.2，并按应用兼容性选择池模式；
资源参数按 VPS 内存与并发压测设置，没有盲目放大连接数；
每日备份已同步到异地，并完成过可记录的恢复演练；
小版本更新有维护窗口，大版本升级有独立迁移和回滚方案；
磁盘、连接、慢查询、锁、WAL 和备份新鲜度都有告警。

完成这些步骤后，这套 PostgreSQL 18 才不只是“Docker 显示 healthy”，而是具备可维护、可恢复、可升级的生产基础。上线前先做一次真实应用压测和断电重启演练，再把监控与备份告警接入日常值守。

场景

建议配置

数据盘

说明

开发与低流量站点

2 vCPU、4GB 内存

60GB NVMe

数据库与应用同机，连接数要保守

小型生产

4 vCPU、8GB 内存

120GB NVMe 起

本文推荐起点，可启用 PgBouncer

中型生产

8 vCPU、16GB 内存起

独立 NVMe/块存储

建议数据库与应用分机

端口

用途

本文策略

5432/TCP

PostgreSQL

只绑定宿主机 127.0.0.1，不对公网开放

6432/TCP

PgBouncer

只监听 127.0.0.1

22/TCP

SSH 管理与隧道

密钥登录，并限制来源

sudo install -d -m 0750 -o "$USER" -g "$USER" \ /opt/postgres18/{conf,initdb,secrets,tls,backups} cd /opt/postgres18 umask 077 openssl rand -hex 32 > secrets/postgres_superuser_password openssl rand -hex 32 > secrets/app_db_password chmod 600 secrets/*

[req] distinguished_name = dn prompt = no req_extensions = server_ext [dn] CN = postgres [server_ext] subjectAltName = DNS:postgres,DNS:localhost,IP:127.0.0.1 keyUsage = critical,digitalSignature,keyEncipherment extendedKeyUsage = serverAuth

cd /opt/postgres18/tls openssl req -x509 -newkey rsa:4096 -sha256 -days 3650 -nodes \ -subj "/CN=PostgreSQL Internal CA" \ -keyout ca.key -out ca.crt openssl req -newkey rsa:3072 -sha256 -nodes \ -config openssl.cnf -keyout server.key -out server.csr openssl x509 -req -sha256 -days 825 \ -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \ -extfile openssl.cnf -extensions server_ext \ -out server.crt chmod 600 ca.key server.key chmod 644 ca.crt server.crt sudo chown 999:999 server.key server.crt

listen_addresses = '*' port = 5432 ssl = on ssl_cert_file = '/run/postgres-tls/server.crt' ssl_key_file = '/run/postgres-tls/server.key' ssl_ca_file = '/run/postgres-tls/ca.crt' ssl_min_protocol_version = 'TLSv1.2' password_encryption = 'scram-sha-256' shared_preload_libraries = 'pg_stat_statements' max_connections = 100 shared_buffers = '2GB' effective_cache_size = '5GB' work_mem = '16MB' maintenance_work_mem = '512MB' checkpoint_timeout = '15min' max_wal_size = '4GB' min_wal_size = '1GB' wal_compression = on random_page_cost = 1.1 effective_io_concurrency = 200 track_io_timing = on log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on log_min_duration_statement = 1000 deadlock_timeout = '1s' timezone = 'UTC'

local all postgres peer local all all scram-sha-256 hostssl all all 0.0.0.0/0 scram-sha-256 hostssl all all ::0/0 scram-sha-256 hostnossl all all 0.0.0.0/0 reject hostnossl all all ::0/0 reject

#!/usr/bin/env bash set -Eeuo pipefail app_password="$(<"${APP_DB_PASSWORD_FILE}")" psql -v ON_ERROR_STOP=1 \ --username "${POSTGRES_USER}" \ --dbname "${POSTGRES_DB}" \ --set=app_password="${app_password}" <<'EOSQL' SELECT format('CREATE ROLE appuser LOGIN PASSWORD %L', :'app_password') WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'appuser') \gexec SELECT 'CREATE DATABASE app OWNER appuser' WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'app') \gexec REVOKE ALL ON DATABASE app FROM PUBLIC; EOSQL psql -v ON_ERROR_STOP=1 \ --username "${POSTGRES_USER}" \ --dbname app \ -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;'

name: postgres18 services: postgres: image: postgres:18.6-bookworm environment: POSTGRES_USER: postgres POSTGRES_DB: postgres POSTGRES_PASSWORD_FILE: /run/secrets/postgres_superuser_password APP_DB_PASSWORD_FILE: /run/secrets/app_db_password command: - postgres - -c - config_file=/etc/postgresql/postgresql.conf - -c - hba_file=/etc/postgresql/pg_hba.conf ports: - "127.0.0.1:5432:5432" volumes: - postgres-data:/var/lib/postgresql - ./conf/postgresql.conf:/etc/postgresql/postgresql.conf:ro - ./conf/pg_hba.conf:/etc/postgresql/pg_hba.conf:ro - ./initdb:/docker-entrypoint-initdb.d:ro - ./tls/ca.crt:/run/postgres-tls/ca.crt:ro - ./tls/server.crt:/run/postgres-tls/server.crt:ro - ./tls/server.key:/run/postgres-tls/server.key:ro secrets: - postgres_superuser_password - app_db_password healthcheck: test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"] interval: 10s timeout: 5s retries: 10 start_period: 30s shm_size: 1gb mem_limit: 6g restart: unless-stopped logging: driver: json-file options: max-size: 20m max-file: "5" secrets: postgres_superuser_password: file: ./secrets/postgres_superuser_password app_db_password: file: ./secrets/app_db_password volumes: postgres-data:

docker compose exec --user postgres postgres \ psql -U postgres -d postgres -c 'SELECT version();' docker compose exec --user postgres postgres \ psql -U postgres -d postgres -c 'SHOW ssl; SHOW data_checksums; SHOW shared_preload_libraries;' docker compose exec --user postgres postgres \ psql -U postgres -d app -c '\dx pg_stat_statements'

sudo apt install -y postgresql-client APP_DB_PASSWORD="$(<secrets/app_db_password)" \ PGPASSWORD="${APP_DB_PASSWORD}" \ psql "host=127.0.0.1 port=5432 dbname=app user=appuser sslmode=verify-full sslrootcert=/opt/postgres18/tls/ca.crt" \ -c 'SELECT current_user, current_database(), ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();'

[databases] app = host=127.0.0.1 port=5432 dbname=app [pgbouncer] listen_addr = 127.0.0.1 listen_port = 6432 unix_socket_dir = /var/run/postgresql auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 300 default_pool_size = 20 min_pool_size = 5 reserve_pool_size = 5 reserve_pool_timeout = 3 max_prepared_statements = 200 server_tls_sslmode = verify-full server_tls_ca_file = /etc/pgbouncer/ca.crt admin_users = postgres stats_users = postgres ignore_startup_parameters = extra_float_digits log_connections = 1 log_disconnections = 1

cd /opt/postgres18 APP_DB_PASSWORD="$(<secrets/app_db_password)" printf '"appuser" "%s"\n' "${APP_DB_PASSWORD}" > secrets/pgbouncer-userlist.txt unset APP_DB_PASSWORD sudo install -m 0640 -o postgres -g postgres \ secrets/pgbouncer-userlist.txt /etc/pgbouncer/userlist.txt sudo install -m 0644 -o root -g root \ tls/ca.crt /etc/pgbouncer/ca.crt sudo systemctl restart pgbouncer sudo systemctl enable pgbouncer sudo systemctl status --no-pager pgbouncer

APP_DB_PASSWORD="$(</opt/postgres18/secrets/app_db_password)" \ PGPASSWORD="${APP_DB_PASSWORD}" \ psql -h 127.0.0.1 -p 6432 -U appuser -d app \ -c 'SELECT current_user, now();' unset APP_DB_PASSWORD PGPASSWORD

cd /opt/postgres18 backup_time="$(date -u +%Y%m%dT%H%M%SZ)" docker compose exec -T --user postgres postgres \ pg_dump -U postgres -d app -Fc \ > "backups/app-${backup_time}.dump" docker compose exec -T --user postgres postgres \ pg_dumpall -U postgres --globals-only \ > "backups/globals-${backup_time}.sql" sha256sum "backups/app-${backup_time}.dump" \ "backups/globals-${backup_time}.sql" \ > "backups/SHA256SUMS-${backup_time}"

cd /opt/postgres18 latest_dump="$(find backups -name 'app-*.dump' -type f -print | sort | tail -1)" docker compose exec -T --user postgres postgres \ createdb -U postgres -O appuser app_restore_test docker compose exec -T --user postgres postgres \ pg_restore -U postgres -d app_restore_test \ --clean --if-exists < "${latest_dump}" docker compose exec --user postgres postgres \ psql -U postgres -d app_restore_test \ -c "SELECT count(*) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema');" docker compose exec -T --user postgres postgres \ dropdb -U postgres app_restore_test

SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count(*) DESC; SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

cd /opt/postgres18 : "先完成备份和恢复抽查" docker compose pull postgres docker compose up -d postgres docker compose exec --user postgres postgres \ psql -U postgres -d postgres -c 'SELECT version();'

为什么在 VPS 上自建 PostgreSQL 18

VPS 配置怎么选

网络和端口设计

安装 Docker 并准备目录

创建 PostgreSQL TLS 证书

编写 PostgreSQL 配置

创建应用用户和数据库初始化脚本

编写 Docker Compose

校验并首次启动

安装 PgBouncer 连接池

备份：不要只备份 Docker Volume

恢复演练

性能调优与监控

更新和升级

常见故障排查

容器提示私钥权限错误

重建后看不到原数据库

remaining connection slots are reserved

TLS 验证失败

5432 连接不上

上线检查清单

Loading...

为什么在 VPS 上自建 PostgreSQL 18

VPS 配置怎么选

网络和端口设计

安装 Docker 并准备目录

创建 PostgreSQL TLS 证书

编写 PostgreSQL 配置

创建应用用户和数据库初始化脚本

编写 Docker Compose

校验并首次启动

安装 PgBouncer 连接池

备份：不要只备份 Docker Volume

恢复演练

性能调优与监控

更新和升级

常见故障排查

容器提示私钥权限错误

重建后看不到原数据库

remaining connection slots are reserved

TLS 验证失败

5432 连接不上

上线检查清单

`remaining connection slots are reserved`

`remaining connection slots are reserved`