PostgreSQL 16 单机版安装 / 配置 / 运维规范

0. 规范目标与原则

目标

• 在 2C / 4GB 内存 服务器上稳定运行 PostgreSQL 16
• 支撑 多应用共用一套 PostgreSQL 实例
• 安装、配置、接入、下线 全部标准化、可审计

核心原则

1. 一应用一数据库一用户
2. 实例可以共享，权限绝不共享
3. 最小权限原则
4. 所有变更可回滚
5. 单机环境以 稳定性优先于性能

1. 环境基线

2. PostgreSQL 16 安装规范

2.1 安装官方仓库

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql

2.2 安装 PostgreSQL 16

dnf install -y postgresql16 postgresql16-server postgresql16-contrib

3. 初始化与服务管理

/usr/pgsql-16/bin/postgresql-16-setup initdb
systemctl enable --now postgresql-16

4. 基础安全规范

4.1 postgres 管理员密码

sudo -iu postgres
psql
ALTER USER postgres WITH PASSWORD '强密码';

4.2 访问控制（pg_hba.conf）

路径：/var/lib/pgsql/16/data/pg_hba.conf

local   all             all                                 peer
host    all             all         127.0.0.1/32            scram-sha-256
host    all             all         192.168.0.0/24          scram-sha-256

修改后：

systemctl reload postgresql-16

4.3 postgresql.conf 基础项

路径：/var/lib/pgsql/16/data/postgresql.conf

listen_addresses = 'localhost'
port = 5432
password_encryption = scram-sha-256

5. 性能与资源配置（2C4G 固化基线）

本章节参数 未经批准禁止随意修改

shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 8MB
maintenance_work_mem = 256MB

max_connections = 100

wal_buffers = 16MB
checkpoint_completion_target = 0.9

max_worker_processes = 4
max_parallel_workers = 2
max_parallel_workers_per_gather = 1

6. 日志与审计规范

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'

log_min_duration_statement = 1000
log_checkpoints = on

7. 运维必备扩展

7.1 pg_stat_statements

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

8. 多应用接入 SOP（Onboarding）

8.1 接入前检查

• 应用名（唯一）
• 应用服务器 IP
• 并发连接预期
• 是否需要只读账号

8.2 创建应用用户

CREATE ROLE appX_user
  LOGIN
  PASSWORD '强密码'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

8.3 创建数据库

CREATE DATABASE appX_db
  OWNER appX_user
  ENCODING 'UTF8'
  TEMPLATE template0;

8.4 Schema 与默认权限（必做）

\c appX_db
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA public TO appX_user;

ALTER DEFAULT PRIVILEGES
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appX_user;

ALTER DEFAULT PRIVILEGES
GRANT USAGE, SELECT ON SEQUENCES TO appX_user;

8.5 用户资源限制

ALTER ROLE appX_user CONNECTION LIMIT 20;
ALTER ROLE appX_user SET statement_timeout = '30s';

8.6 pg_hba.conf 接入规则

host  appX_db  appX_user  192.168.1.10/32  scram-sha-256

9. 应用下线 SOP（Offboarding）

9.1 下线前

• 应用停机
• 执行最终备份

pg_dump -Fc appX_db > appX_db_final.dump

9.2 下线流程

REVOKE CONNECT ON DATABASE appX_db FROM appX_user;
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity WHERE datname='appX_db';

DROP DATABASE appX_db;
DROP ROLE appX_user;

10. 备份与恢复规范

10.1 日常备份

pg_dump -Fc appX_db > appX_db_$(date +%F).dump

10.2 恢复

pg_restore -d appX_db appX_db.dump

11. 日常运维清单

每日

• 磁盘空间
• 实例存活

每周

VACUUM (ANALYZE);

每月

REINDEX DATABASE appX_db;

12. 禁止事项（红线）

• 使用 postgres 跑业务
• 多应用共用同一数据库或用户
• trust 认证
• 随意修改内存参数
• 不备份直接删除

13. PgBouncer 单机规范（防连接雪崩，强烈推荐）

适用场景：

• 多应用共用 PostgreSQL 单机实例
• 应用存在短连接 / 连接数不可控风险
• 2C4G 内存环境必须使用

13.1 PgBouncer 角色定位

• PgBouncer 不是数据库，是连接池
• 所有应用 必须连接 PgBouncer，而不是直连 PostgreSQL
• PostgreSQL 负责 SQL 执行，PgBouncer 负责连接治理

13.2 安装 PgBouncer

dnf install -y pgbouncer

13.3 目录与文件规范

13.4 用户认证文件（userlist.txt）

"appX_user" "SCRAM-SHA-256$..."

生成方式（在 PostgreSQL 中）：

SELECT rolname, rolpassword FROM pg_authid WHERE rolname='appX_user';

PgBouncer 与 PostgreSQL 使用相同数据库用户

13.5 pgbouncer.ini（单机推荐配置）

[databases]
app1_db = host=127.0.0.1 port=5432 dbname=app1_db
app2_db = host=127.0.0.1 port=5432 dbname=app2_db

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

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

pool_mode = transaction

max_client_conn = 500
default_pool_size = 20
reserve_pool_size = 5

server_idle_timeout = 60
server_lifetime = 3600

log_connections = 1
log_disconnections = 1

13.6 PostgreSQL 侧配合配置

ALTER SYSTEM SET max_connections = 100;

应用连接数只打到 PgBouncer，不直接消耗 PostgreSQL 连接

13.7 启动与管理

systemctl enable --now pgbouncer
systemctl status pgbouncer

13.8 应用接入规范（强制）

❌ 禁止应用直连 5432

13.9 运维检查点

• PgBouncer 连接数：

SHOW POOLS;

• 当前客户端：

SHOW CLIENTS;

• 后端连接：

SHOW SERVERS;

13.10 常见红线（必须遵守）

• ❌ pool_mode 使用 session
• ❌ PgBouncer 与 PostgreSQL 共用超级用户
• ❌ default_pool_size 盲目调大
• ❌ 应用绕过 PgBouncer 直连数据库

14. 规范总结

• PostgreSQL 负责数据一致性
• PgBouncer 负责连接稳定性
• 单机 + 多应用 没有 PgBouncer 就是不完整方案

PgBouncer 是 2C4G PostgreSQL 的生命线

本文由 best 创作，采用 知识共享署名 3.0 中国大陆许可协议 进行许可。 可自由转载、引用，但需署名作者且注明文章出处。