PostgreSQL服务器部署教程：安装、初始化与远程连接配置

第一次部署 PostgreSQL 的人常被两个问题卡住：一是 psql: FATAL: Peer authentication failed for user "postgres"，二是改完配置远程还是连不上。前者是 pg_hba.conf 的认证规则问题，后者多半是 listen_addresses 与防火墙没一起处理。本文以 CentOS 系（Rocky / AlmaLinux 8、9）和 Debian 系（Ubuntu 22.04 / 24.04）双线并行，从仓库源安装走到远程连接可用，并给出一组生产环境的最小安全配置。

一、安装：用官方 PGDG 仓库，不要用系统自带源

Linux 发行版自带仓库里的 PostgreSQL 版本通常落后好几个大版本（例如某版本的系统源可能还在提供 PostgreSQL 12，而社区已发布到 16/17），且补丁滞后。生产环境统一走 PGDG（PostgreSQL Global Development Group）官方仓库。

1.1 Rocky / AlmaLinux / CentOS（RPM 系）

# 1) 先禁用系统自带的 postgresql 模块，否则 dnf 可能解析到错误的候选版本
sudo dnf -qy module disable postgresql

# 2) 安装 PGDG 仓库 RPM（以 EL-9 为例，EL-8 把路径里的 9 换成 8）
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 3) 安装服务端（这里装 PostgreSQL 16，换成 15 / 17 同理）
sudo dnf install -y postgresql16-server postgresql16-contrib

# 4) 初始化数据目录（PGDATA 默认 /var/lib/pgsql/16/data）
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

# 5) 启动并设置开机自启
sudo systemctl enable --now postgresql-16
sudo systemctl status postgresql-16

1.2 Ubuntu / Debian（DEB 系）

# 1) 导入仓库签名并安装源
sudo apt update && sudo apt install -y curl ca-certificates gnupg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/pgdg.gpg
echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list

# 2) 安装
sudo apt update
sudo apt install -y postgresql-16 postgresql-contrib-16

# 3) Debian 系的 initdb 在 apt 安装时已完成，数据目录为 /var/lib/postgresql/16/main
sudo pg_ctlcluster 16 main status
sudo systemctl enable --now postgresql

1.3 验证安装结果

psql --version
sudo -u postgres psql -c "SELECT version();"
sudo -u postgres psql -c "SHOW data_directory;"
sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('listen_addresses','port','max_connections','shared_buffers');"

RPM 系默认目录：/var/lib/pgsql/16/data；DEB 系默认目录：/var/lib/postgresql/16/main。配置文件 postgresql.conf 与客户端认证文件 pg_hba.conf 都在这下面， Debian 系为了兼容多实例，还会把这两个文件软链到 /etc/postgresql/16/main/。

postgresql-contrib 建议一并安装，它提供 pg_stat_statements、pgcrypto、hstore、uuid-ossp 等常用扩展。

二、最小可用初始化：角色、数据库与权限

PostgreSQL 不推荐直接用超级用户 postgres 跑业务。正确做法是给每个应用建独立 role 和 database。

-- 进入交互终端
sudo -u postgres psql

-- 建业务角色（PostgreSQL 的 role 即用户）
CREATE ROLE appuser WITH LOGIN PASSWORD 'Str0ng_P@ssw0rd' VALID UNTIL '2099-12-31';

-- 建库并指定归属
CREATE DATABASE appdb OWNER appuser ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;

-- 收口权限：只允许连自己的库
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO appuser;

-- PostgreSQL 15 起 public schema 的默认 CREATE 权限已收紧，低版本需手工收
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO appuser;

\q

几个容易踩的点：

• ENCODING 'UTF8' 必须配合 TEMPLATE template0，因为 template1 的编码已被定死，直接用默认模板建不同字符集会报错。
• 生产环境的 LC_COLLATE 建议用 C 或 en_US.UTF-8，中文排序场景再权衡；C 的排序性能最好，但中文字符串排序结果可能不符合习惯。
• 如果一定要有 SUPERUSER 权限的日常账号，说明权限设计有问题。给业务账号的最小权限往往是：SELECT/INSERT/UPDATE/DELETE。
• 忘记角色密码时用 ALTER ROLE appuser WITH PASSWORD '新密码';，不需要改配置文件。

# 用新账号登录验证
psql -h 127.0.0.1 -U appuser -d appdb -c "SELECT current_user, current_database();"

# 常用快捷命令
\l        -- 列出数据库
\du       -- 列出角色
\dt       -- 列出表
\conninfo -- 当前连接信息

三、远程连接：listen_addresses + pg_hba.conf + 防火墙，三处缺一不可

这是整套部署里最容易漏的地方，请按顺序确认三处。

3.1 第一处：postgresql.conf 的监听地址

listen_addresses 默认值是 'localhost'，也就是只监听 127.0.0.1，外部请求连 TCP 握手都完成不了。要开放远程，必须改成监听全部或对内网网卡监听：

# /var/lib/pgsql/16/data/postgresql.conf  （Debian 系：/etc/postgresql/16/main/postgresql.conf）
listen_addresses = '*'          # 或 '127.0.0.1,10.0.0.10' 只监听内网，更安全
port             = 5432
max_connections  = 200
superuser_reserved_connections = 5   # 默认 3，给管理员留的专用连接

不想手工编辑文件也可以用 SQL 改（会写入 postgresql.auto.conf，其优先级高于主配置文件）：

ALTER SYSTEM SET listen_addresses = '*';
SELECT pg_reload_conf();        -- 大部分参数 reload 即可生效
SELECT * FROM pg_settings WHERE name = 'listen_addresses';

注意：listen_addresses、port、max_connections、shared_buffers 属于需要重启的参数，pg_reload_conf() 对这些不生效，需要 systemctl restart postgresql-16。pg_settings 的 context 字段会写明每个参数的类型：postmaster 表示需重启，sighup 表示 reload 即可，user 表示会话级可改。

SELECT name, setting, unit, context FROM pg_settings WHERE context = 'postmaster' LIMIT 20;

3.2 第二处：pg_hba.conf 的客户端认证规则

pg_hba.conf（host-based authentication）控制的不是"能否连 TCP"，而是"连上来之后要不要密码、用什么方式认证"。它的规则是自上而下匹配，命中即停止，顺序写错等于白配。典型的四列结构是：类型 / 数据库 / 用户 / 客户端地址 / 认证方法。

# /var/lib/pgsql/16/data/pg_hba.conf
# TYPE   DATABASE    USER        ADDRESS             METHOD
local    all         postgres                        peer
local    all         all                             scram-sha-256
host     appdb       appuser     10.0.0.0/24         scram-sha-256
host     appdb       appuser     127.0.0.1/32        scram-sha-256
host     all         all         127.0.0.1/32        scram-sha-256
host     replication repl_user   10.0.0.0/24         scram-sha-256
host     all         all         0.0.0.0/0           reject

如果 default_password_encryption 是老的 md5，先改成 scram-sha-256 再重设一次所有账户密码，否则旧密码哈希会继续以 md5 形式存在：

SHOW default_password_encryption;
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
ALTER ROLE appuser WITH PASSWORD 'Str0ng_P@ssw0rd';
SELECT rolname, LEFT(rolpassword,14) FROM pg_authid WHERE rolname = 'appuser';  -- 开头为 SCRAM-SHA-256 即正确

3.3 第三处：主机防火墙与云安全组

# firewalld（RPM 系）
sudo firewall-cmd --permanent --add-service=postgresql        # 或 --add-port=5432/tcp
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.0.0/24" port protocol="tcp" port="5432" accept'
sudo firewall-cmd --reload
sudo firewall-cmd --list-all

# ufw（Debian 系）
sudo ufw allow from 10.0.0.0/24 to any port 5432 proto tcp

如果是云服务器，安全组里还必须同步放行，并且只放行内网网段。把 5432 暴露在公网，等同于把数据库送给全网爆破机器人——通常几小时内就会有扫描流量。

3.4 打通验证

# 本机先看监听地址是否正确
ss -lntp | grep 5432
# 期望看到 0.0.0.0:5432 或 10.0.0.10:5432，而不是 127.0.0.1:5432

pg_isready -h 10.0.0.10 -p 5432
psql "host=10.0.0.10 port=5432 dbname=appdb user=appuser password=xxx" -c "SELECT 1;"
PGPASSWORD='xxx' psql -h 10.0.0.10 -U appuser -d appdb -c "\conninfo"

四、生产环境的最小基线参数

# postgresql.conf 关键项（按 16GB 内存单机举例）
listen_addresses       = '*'
max_connections        = 200
shared_buffers         = 4GB                 # 约物理内存 25%，超过 8GB 收益递减
effective_cache_size   = 12GB                # 约 50%-75%，供优化器估算，不真实占用
work_mem               = 8MB                 # 每个排序/哈希操作可能独占，切勿拍大
maintenance_work_mem   = 512MB               # VACUUM、建索引使用
wal_level              = replica             # 要搭建物理流复制的最低要求
max_wal_size           = 4GB
min_wal_size           = 1GB
checkpoint_timeout     = 15min
checkpoint_completion_target = 0.9
random_page_cost       = 1.1                 # SSD 场景应下调，默认 4.0 是为机械盘设计的
effective_io_concurrency = 200               # SSD 场景建议 100-300
log_destination        = 'csvlog'
logging_collector      = on
log_directory          = 'log'
log_min_duration_statement = 1000            # 记录超过 1 秒的语句
log_line_prefix        = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
shared_preload_libraries = 'pg_stat_statements'
track_io_timing        = on

shared_buffers 是 PostgreSQL 自己的数据页缓存，与操作系统的 page cache 会形成二级缓存，一般推荐物理内存的 25%，Linux 上超过 8GB 的边际收益明显下降。work_mem 请特别注意：它不是全局上限，一个复杂查询可能并行申请多份 N × work_mem，max_connections = 200 配上 work_mem = 64MB 在最坏情况下就是十几 GB，很容易 OOM。

五、上线后的日常运维要点

5.1 日志在哪里

RPM 系的日志默认写在数据目录的 log/ 子目录（logging_collector = on 时），同时 systemd 也会收集标准输出；Debian 系的日志通常在 /var/log/postgresql/。查启动失败的第一步永远是看日志，而不是猜。

sudo journalctl -u postgresql-16 -n 100 --no-pager
sudo tail -100 /var/lib/pgsql/16/data/log/postgresql-*.csv
# Debian 系
sudo tail -100 /var/log/postgresql/postgresql-16-main.log

常见的启动失败原因有三类：配置文件语法错误（-- check 会以 postgresql.conf:xxx: syntax error 明确给出行号）、数据目录权限不对（必须是 postgres 用户且权限 700/750）、端口已被占用。首次排障可以用前台启动的方式看到完整输出：su - postgres -c "/usr/pgsql-16/bin/pg_ctl -D /var/lib/pgsql/16/data -l /tmp/pg.log start"。

5.2 三个必配的运维项

• 扩展 pg_stat_statements：需要在 shared_preload_libraries 里声明并重启，之后才能 CREATE EXTENSION pg_stat_statements。它相当于 PostgreSQL 版的慢查询聚合，排查慢 SQL 全靠它。
• 备份：逻辑备份用 pg_dump -Fc（自定义格式，支持并行与选择性恢复），物理备份用 pg_basebackup 加 WAL 归档实现时间点恢复。配置完必须演练一次恢复。
• 自动清理 autovacuum：默认开启，但大表往往需要单独调 autovacuum_vacuum_scale_factor 和每张表的 autovacuum_vacuum_threshold，否则会出现"表越来越大、查询越来越慢"的膨胀问题。

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls, ROUND(total_exec_time::numeric,2) AS total_ms, LEFT(query,60)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

# 逻辑备份与恢复
pg_dump -h localhost -U appuser -Fc -f /backup/appdb_$(date +\%F).dump appdb
pg_restore -h localhost -U appuser -d appdb -j 4 /backup/appdb_2025-01-01.dump

# 物理备份（需要有 wal_level=replica 与归档设置）
pg_basebackup -h 10.0.0.10 -U repl_user -D /backup/base -Ft -z -P

5.3 连接数规划与 pgBouncer

PostgreSQL 的 max_connections 默认值是 100，比 MySQL 的 151 更紧张，而 superuser_reserved_connections 默认为 3，专门用于给超级用户在连接打满时保留入口。每个连接都是独立进程、各自占用内存，几百条连接会带来明显的上下文切换开销，因此 PostgreSQL 社区更推荐"少量数据库连接 + 连接池"而不是直接放大 max_connections。

• 先统计真实并发：SELECT count(*), state FROM pg_stat_activity GROUP BY state;，多数 Web 业务的活跃并发远低于想象。
• 应用层连接池 ≤ 单实例的 60%-70%，剩下的留给维护、监控和后台任务。
• 并发确实高时用 pgBouncer 做连接池（transaction 或 statement 模式），把上千客户端连接复用到几十条数据库连接上。
• 务必设置 idle_in_transaction_session_timeout（如 60000 毫秒），避免"事务忘了提交"把连接长期占死——这是 PostgreSQL 环境最常见的连接耗尽原因。

-- 查看当前连接与状态分布
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SELECT pid, usename, application_name, client_addr, state, query_start, LEFT(query,60)
FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start LIMIT 10;

-- 找出长时间开着不放的事务
SELECT pid, age(now(), xact_start) AS xact_age, LEFT(query,60)
FROM pg_stat_activity WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 10;

-- 防止长期空闲事务占连接
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60000';
SELECT pg_reload_conf();

-- 终止单个会话（PG 12 之前用 pg_terminate_backend(pid)）
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 12345;

六、部署完成后的检查清单

1. 确认安装来源：rpm -qi / apt policy，版本必须是预期的 PostgreSQL 16（或选定版本），且安装了 contrib。
2. 确认数据目录：SHOW data_directory; 与备份方案要基于同一个目录。
3. 创建业务 role 与 database，禁止应用使用 postgres 超级用户直连。
4. 核对字符集与排序规则：\l 里显示的 Encoding 与 Collate 必须与设计一致。
5. 修改 listen_addresses 并重启，用 ss -lntp 确认不再只监听 127.0.0.1。
6. 在 pg_hba.conf 中按"精确到网段 + scram-sha-256"的方式追加规则，注意规则顺序。
7. 重设所有账号密码，确认 pg_authid 中的哈希前缀是 SCRAM-SHA-256。
8. 同步配置 firewalld / ufw 与云厂商安全组，只放行应用服务器内网段。
9. 从应用服务器用真实业务账号连接验证，而不是只在数据库本机 psql 自测。
10. 按服务器实际内存设置 shared_buffers、effective_cache_size、work_mem，并设置 log_min_duration_statement 便于后续排障。
11. 接入监控：连接数、检查点频率、 replication slot 堆积、磁盘使用率。
12. 配置第一次备份（pg_basebackup 或 pg_dump），确认恢复流程跑通过一次。

小结

PostgreSQL 部署的四道关口是：仓库源、listen_addresses、pg_hba.conf、防火墙。任何一道没做通，远程连接都不会成功，而且报错信息常常指向另一个环节。日常避坑：不要使用 trust 认证；不要用 ALTER SYSTEM 盲改而忘记 postmaster 类参数需要重启；不要把 work_mem 配得很大；不要在 SSD 机器上保留 random_page_cost = 4.0 的默认值（会让优化器错误地偏好全表扫描）；不要把 5432 暴露在公网。把这些细节做成一份标准部署文档，新机器上线基本可以在半小时内完成并且一次通过。