Skip to content
中间件
PostgreSQL
安装与配置

安装与配置

本章解决一件事：把 PostgreSQL 18 干净地装到 Windows、Linux、macOS 或 Docker 上，然后摸清它的三个配置文件——数据放在哪、参数在哪改、谁能连进来。

PostgreSQL 是什么，什么时候选它

PostgreSQL 源自 1980 年代末伯克利的 Postgres 项目，三十多年持续开发，是目前演进最快的开源关系数据库。它是"对象-关系"型的：除标准 SQL 外，还有自定义类型、函数、操作符和扩展机制，第三方代码可以插进它的存储层和查询层。

跟 MySQL 做选型时，几个差异值得摆在桌面上看：

  • 类型系统。PG 原生支持数组、范围、复合类型、枚举、域类型、inet/cidr/macaddr、uuid 等。“这列要存一组标签”、“这列存一段时间区间”，在 PG 里是一个列类型的事；在 MySQL 里往往退化成 JSON 或逗号分隔字符串。
  • JSONB + GIN 索引。JSONB 是二进制存储的 JSON，可以建索引、可以和普通列写进同一个查询。半结构化数据不用另开一套存储。
  • 扩展生态。PostGIS（地理）、pgvector（向量检索）、TimescaleDB（时序）、pg_cron（定时任务）……一台 PG 常常能干好几个专用数据库的活。MySQL 在这一维度的可选项少得多。
  • 复杂查询。窗口函数、CTE、LATERAL JOIN、并行查询这些能力 PG 给得更完整也更早；报表和数据分析类负载在 PG 上通常写起来更顺手。

也要公平地说 MySQL 的好处：主从复制运维模型简单、部署足迹小、教程和 DBA 人才池更大、gh-ost 这类在线改表工具生态成熟。简单的主键驱动的 CRUD 业务，两者跑起来没有体感差别。

一句话决策：查询复杂、类型讲究、想把地理/向量/JSON 都收进一个库，选 PG；团队 MySQL 经验深厚、负载是典型的读多写少 Web 应用，MySQL 完全够。

版本现状（2026-10-20 实测）：当前大版本是 18（补丁版 18.6），上一分支 17 已到 17.11。PG 每年秋季发布一个大版本，小版本只修 bug 和安全问题——这个节奏值得记进运维计划。本手册以 PG 18 为基线，兼顾 14–17。

PG 18 值得在安装当天就知道的三件事：异步 I/O 子系统（顺序扫描、位图扫描、vacuum 明显受益，见 01-架构与核心概念.md）；initdb 默认开启数据页校验和（旧版本默认关，这是 18 的重要变化，--no-data-checksums 可关）；MD5 口令认证被官方弃用，给角色设 MD5 口令会告警（下文 pg_hba 一节展开）。

各平台安装

Windows — EDB 图形安装器

官方 Windows 安装器由 EnterpriseDB 维护，从 postgresql.org 的 Downloads 页跳转即可。安装中真正要做决定的就几处：

  • 安装目录默认 C:\Program Files\PostgreSQL\18，一般不动。
  • 数据目录默认 C:\Program Files\PostgreSQL\18\data。机械盘 + SSD 双盘的机器，把数据目录放 SSD；数据库对 WAL 的 fsync 延迟极其敏感，别放网络盘或同步盘。
  • 超级用户口令：属于 postgres 角色（约定俗成的 root，装完第一步就该记住它）。
  • 端口默认 5432，装了多个大版本会冲突，按提示换。
  • 勾选安装 pgAdmin 4 和 Command Line Tools。
  • 装完后会注册一个 Windows 服务 postgresql-x64-18，用 services.msc 或 net stop/start postgresql-x64-18 控制启停——Windows 上 PG 不是前台进程，改了配置要重启服务才生效。
  • 把 C:\Program Files\PostgreSQL\18\bin 加入 PATH（psql、pg_dump、pg_ctl 都在这），然后验证：
psql --version

pgAdmin 还是 psql？ pgAdmin 适合浏览对象树、看会话、偶尔手敲 SQL；日常开发（迁移脚本、EXPLAIN 分析、批量导数）psql 更可控，而且本手册所有示例都可以直接粘进 psql 跑。建议以 psql 为主，pgAdmin 当仪表盘。

Linux — PGDG 官方源（推荐）

发行版自带的源里 PG 版本通常老两代（比如 Ubuntu 自带的还是 16）。生产环境请直接用 PGDG 官方 apt/yum 源。

Debian / Ubuntu：

# 一条脚本搞定：装 postgresql-common、取签名密钥、写源
sudo apt install -y postgresql-common ca-certs curl
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh   # 交互确认即可

sudo apt update
sudo apt install -y postgresql-18
sudo systemctl status postgresql     # Debian 打包会自动初始化集群并拉起服务

手工配源的话：密钥存到 /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc，再写 /etc/apt/sources.list.d/pgdg.sources，注意发行代号要带 -pgdg 后缀（例如 trixie-pgdg），这是新格式最容易踩错的地方。apt.postgresql.org 访问慢的国内网络，可以换成清华镜像站（mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/）——把源文件里的域名换掉即可，其余不变。

RHEL / Rocky / AlmaLinux：

# EL-9 替换成你实际的 major 版本（EL-8/9/10 均官方支持）
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql     # RHEL 8+ 自带 module 会抢包，必须先禁用
sudo dnf install -y postgresql18-server postgresql18
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb    # 初始化数据目录
sudo systemctl enable --now postgresql-18

RHEL 系的布局：二进制在 /usr/pgsql-18/bin，数据目录在 /var/lib/pgsql/18/data，服务名 postgresql-18。

装完默认只有本机 Unix socket 可连；远程访问要动 listen_addresses 和 pg_hba.conf，见下面两节。

macOS — Homebrew 

brew install postgresql@18
brew services start postgresql@18      # 注册成 LaunchAgent，开机自启
psql --version

Homebrew 的公式默认不链接进 PATH，按安装输出的提示把 /opt/homebrew/opt/postgresql@18/bin（Intel 机器是 /usr/local/opt/...）补进 PATH。另外注意：brew 版把超级用户设成你的登录用户名而不是 postgres，且 Unix socket 上口令为空——它把 trust/peer 式开发便利做到了极致，这也是为什么生产永远跑在 Linux 上。

Docker — 最快起一个实例 

docker run -d --name pg18 \
  -e POSTGRES_PASSWORD=devpass123 \
  -e POSTGRES_USER=app \
  -e POSTGRES_DB=appdb \
  -p 5432:5432 \
  -v pg18-data:/var/lib/postgresql/data \
  -v ./init:/docker-entrypoint-initdb.d:ro \
  postgres:18

几个必须明白的点：

  • POSTGRES_PASSWORD 是硬性要求：没给口令又没显式设 POSTGRES_HOST_AUTH_METHOD=trust，容器直接拒绝启动。这个设计就是为了防手滑。
  • 卷挂载位置警告：数据必须在 /var/lib/postgresql/data 挂到命名卷或本地磁盘的 bind mount 上。不要让数据落在容器可写层——那是 overlayfs，大数据库的随机写会疯狂触发 copy-up，写放大严重，而且容器一删数据就没了。Linux 上用 bind mount 时记得宿主机目录要让容器里的 postgres 用户可写（uid 999），命名卷则没这个烦恼。
  • /docker-entrypoint-initdb.d：空数据卷首次初始化时，这个目录里的 .sql / .sql.gz / .sh 会按文件名顺序执行，适合建表种子数据。关键陷阱——只在数据目录为空的那一次执行，改了脚本重启容器不会重跑，先 docker volume rm 再启动。
  • 镜像默认把 listen_addresses 设成 *，并往 pg_hba 追加 host all all all scram-sha-256（认证方法取自 password_encryption 默认值）：只要 5432 映射出去，任何 IP 都能拿口令来试。跑在公网机器上时这等于面向互联网开门，要么收进内网/防火墙，要么改 pg_hba 白名单。

一份可以直接抄进项目的 compose：

services:
  db:
    image: postgres:18
    container_name: pg18
    restart: unless-stopped
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: devpass123
      POSTGRES_DB: appdb
    ports:
      - "5432:5432"
    volumes:
      - pg18-data:/var/lib/postgresql/data
      - ./init:/docker-entrypoint-initdb.d:ro
    command:
      - postgres
      - -c
      - shared_buffers=256MB
      - -c
      - log_min_duration_statement=500ms
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d appdb"]
      interval: 5s
      timeout: 5s
      retries: 10

volumes:
  pg18-data:

用 command 传 -c 覆盖参数，或者往 /var/lib/postgresql/conf/conf.d 这类 drop-in 目录写 conf 片段，都不用动主配置文件。

数据目录与三个配置文件

数据目录（下文 PGDATA）启动后大致长这样：

PGDATA/
├── base/              # 每个数据库一个子目录（按数据库 OID 命名），普通表的数据文件都在这里面
├── global/            # 跨库共享的系统表：pg_database、pg_authid 等
├── pg_wal/            # WAL 日志，16MB 一个段文件——崩溃恢复的命脉，见 `01-架构与核心概念.md`
├── pg_xact/           # 全局事务提交状态，元组可见性要靠它判定（见 `06-MVCC与Vacuum.md`）
├── pg_stat/           # 统计信息，重启不丢；pg_stat_tmp/ 是运行期临时统计
├── PG_VERSION         # 版本标记，缺了它 initdb 不认这个目录
├── postgresql.conf    # 主配置
├── postgresql.auto.conf  # ALTER SYSTEM 的落盘处，优先级高于 postgresql.conf
├── pg_hba.conf        # 客户端认证：谁能从哪用什么方式连
└── pg_ident.conf      # 用户名映射表，配合 peer/ident 认证用，绝大多数项目用不上

三个文件的分工可以类比一栋楼：postgresql.conf 是整栋楼的电器参数（多大功率、开几部电梯）；pg_hba.conf 是大门禁（谁能进门、查什么证件），只管进门，不管进门之后你能碰哪些房间——库内权限模型在 12-安全与权限管理.md；pg_ident.conf 是门禁系统里的"工牌名字对花名册"映射表。

各发行版把文件放的位置不一样（Debian 把 conf 挪到 /etc/postgresql/18/main/，RHEL 在 PGDATA 里，Windows 在数据目录下）。别记路径，用查询定位，永远可靠：

SHOW config_file;
SHOW hba_file;
SHOW data_directory;     -- 需要超级用户

改参数是 reload 还是 restart？ 规则很简单：每个参数在文档和 pg_settings 里有一个 context 属性。postmaster 上下文必须重启；sighup 上下文 reload 即可；superuser/user 上下文甚至可以会话内 SET。查一下就知道，不用猜：

SELECT name, setting, context, sourcefile
FROM pg_settings
WHERE name IN ('shared_buffers', 'wal_level', 'work_mem');

reload 不会中断现有连接，只影响新连接（会话级参数对已在跑的会话无效）：

psql -c "SELECT pg_reload_conf();"     # 或 systemctl reload postgresql-18 / pg_ctl -D $PGDATA reload

核心参数：先讲为什么

下表默认值均为 PG 18 文档实测摘录。建议值假设一台 16GB 内存、SSD、专用数据库服务器。

# 参数                默认值      作用                        生效方式
# listen_addresses    localhost   监听哪些网卡                restart
# port                5432        TCP 端口                    restart
# max_connections     100         最大并发连接数              restart
# shared_buffers      128MB       数据库自己的页缓存          restart
# effective_cache_size 4GB        优化器对总可用缓存的估计    reload
# work_mem            4MB         单节点排序/哈希内存上限     reload
# maintenance_work_mem 64MB       vacuum/建索引/恢复的内存    reload
# wal_level           replica     WAL 记录详细程度            restart
# checkpoint_timeout  5min        自动 checkpoint 最大间隔    reload
# max_wal_size        1GB         两个 checkpoint 间 WAL 软上限 reload
# wal_compression     off         压缩 WAL 里的整页镜像       reload
# random_page_cost    4.0         优化器估计的随机页读成本    reload
# log_min_duration_statement -1   慢于多少毫秒的语句记日志    reload

逐个说清楚为什么。

listen_addresses。默认只监听 127.0.0.1，这是新手"明明装了却连不上"的第一现场。改成 '*' 只是开了门，真正决定谁能进来的是 pg_hba——两者永远配套修改，也永远不要配套改成"全开 + trust"。改这个要 restart，因为监听套接字是 postmaster 启动时建的。

max_connections。PG 是每连接一个进程（不是线程，见 01-架构与核心概念.md）。一个 backend 进程有自己的内存上下文和内核栈，空闲状态就占几 MB，查询起来再叠加 work_mem 用量；500 个连接的基线开销就是几个 GB，还要算上调度和锁的压力。所以生产的正确姿势不是把它调到 2000，而是让它保持 100–300 这个量级，应用侧用连接池（pgbouncer 或驱动自带池）把成百上千的空闲连接收敛成几十个真实连接。一个粗略公式：连接池上限 × 应用实例数 ≈ max_connections 的 80%，留出运维通道。超了再调池子，不是调数据库。

shared_buffers。PG 自己管理的页缓存，默认 128MB 小得可笑——那是安全出厂值，不是性能值。官方建议的起点是内存的 25%，而不是"越大越好"，原因是 PG 不做 O_DIRECT，脏页和未命中读取都依赖操作系统 page cache 这第二层缓存（PG 默认走内核的 buffered I/O，自己负责 fsync）。shared_buffers 给到 50% 只会挤压 OS cache，同一份热数据在两层里各存一遍，纯浪费。文档原话：超过内存 40% 很难比更小的值更好。调大它的同时记得调大 max_wal_size——更多脏页意味着 checkpoint 之间要攒更多 WAL。

effective_cache_size。名字骗人：它不分配任何内存，只是告诉优化器"这台机器上数据大概率在缓存里"，影响的是索引扫描 vs 顺序扫描的选择。设成 shared_buffers + 预计 OS 可用缓存之和，实践中 16GB 机器给 12GB 左右。它是 reload 生效，调错了随时改回来。

work_mem。最容易出事故的一个参数。它是每个查询节点、每个排序/哈希操作的独立预算——一个带多个 join 和排序的查询里，hash join 的内存上限还要再乘 hash_mem_multiplier（默认 2.0），并且所有并发连接同时各自消耗。“work_mem 设 256MB"的真实含义可能是 256MB × 节点数 × 连接数，机器不爆才怪。默认 4MB 对 OLTP 是合理的；报表用户单独 SET work_mem='128MB' 或用角色级默认值，比全局调大安全得多。乘号陷阱的量化展开与排序溢盘排查见 09-查询优化与EXPLAIN.md。

maintenance_work_mem。vacuum、CREATE INDEX、恢复 pg_dump 这类维护操作专用，跟并发无关（一个会话一次只跑一个维护操作），可以放心给几百 MB—— restores 会明显变快。唯一注意：每个 autovacuum worker 也会按这个值分配，想单独控制就用 autovacuum_work_mem。

wal_level / checkpoint_timeout / max_wal_size。wal_level 三档 minimal / replica / logical：minimal 只记崩溃恢复所需的信息，代价是开不了归档也做不了物理复制（archive_mode 与 minimal 互斥，max_wal_senders 不为 0 时服务器直接拒绝以 minimal 启动）；默认 replica 适合绝大多数部署；只有逻辑复制/CDC 才上 logical，它多记的开销是持续的。checkpoint 每 checkpoint_timeout（默认 5 分钟）或 WAL 攒到 max_wal_size（默认 1GB，注意是软上限）触发一次，作用与"刷脏风暴"见 01-架构与核心概念.md；批量导入期间把 max_wal_size 临时调大能压掉一波波的检查点延迟尖刺。

wal_compression。默认 off。开启后压缩 WAL 里的整页镜像（主要来源是 full page image——checkpoint 后首次修改的页要整页写 WAL，量不小），省的是存储和复制带宽，换的是 CPU。方法可选 pglz（on 是它的历史拼法）、lz4、zstd（后两者取决于编译选项，发行版包通常都带）。写带宽紧、CPU 有余量的实例值得一开，PG 14+ 的 lz4/zstd 开销已明显低于 pglz。

random_page_cost。默认 4.0 诞生于机械盘时代——随机读一页约等于 4 倍顺序读。SSD 的随机读延迟和顺序读只差几倍以内，所以 SSD 机器上普遍调到 1.1–1.2：不改的话，优化器会系统性高估索引扫描的代价，明明该走索引却选了顺序扫描，这是"明明建了索引却不走"的经典元凶之一。它同样不分配任何东西，纯粹是成本模型的输入。

log_min_duration_statement。默认 -1（关闭）。开发环境设 250ms、生产从 1s 起步逐步收紧，慢查询日志是发现坏 SQL 最便宜的雷达。每条日志都要写盘，设成 0（打印全部）本身就会拖慢高吞吐实例。配合 log_checkpoints（默认已开）和 log_line_prefix 一起规划，运维细节见 11-备份恢复与运维.md。

pg_hba.conf：谁能进门

规则一行一条，字段依次是 TYPE / DATABASE / USER / ADDRESS / METHOD（ADDRESS 列在 TYPE 为 local 时不存在，Unix socket 没有 IP 概念）。匹配逻辑必须记住两条：自上而下，第一条命中即生效；一条都不命中则默认拒绝。排错时先怀疑顺序——把宽规则写在窄规则前面，后面的窄规则就是死代码。

psql -c "SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules;"   # 看解析结果，error 列非空说明有语法错
psql -c "SELECT pg_reload_conf();"   # 改完 reload 即可生效，不用重启

常用 METHOD 一句话版：

  • scram-sha-256：当前推荐。挑战-应答式，口令本身从不过网（服务器只见哈希），PG 14 起 password_encryption 默认就是它。
  • md5：正在被淘汰。除算法强度外，md5 认证会把口令的等价物送过网络；PG 18 起官方已明确弃用（给角色设 md5 口令就告警，未来大版本会移除），存量实例尽快迁到 scram。
  • trust：来了就让进，不要口令。只配出现在"本机开发机"语境里，任何能被外部访问的实例上用 trust 都等同裸奔。
  • peer：Unix socket 上比对操作系统用户名。Linux 上 sudo -u postgres psql 免密，就是默认 pg_hba 里的 peer 在工作。
  • ident：向对端主机索要真实 OS 用户名做校验，跨网络信任边界模糊，用得越来越少。
  • reject：显式拒绝某段地址/某批用户，写黑名单时有用。

Windows 上没有 Unix socket，local 行不起作用，所有连接都走 host。

开发机最小可用（图省事，仅限本机回环）：

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

生产最小可用（本机运维 + 应用网段 + 复制专用）：

# TYPE     DATABASE       USER        ADDRESS          METHOD
local      all            postgres                     peer
host       all            all         127.0.0.1/32     scram-sha-256
host       appdb          app         10.20.0.0/16     scram-sha-256
host       replication    repl        10.20.0.5/32     scram-sha-256
host       all            all         0.0.0.0/0        reject

注意生产示例里应用账号 app 只允许从应用网段访问 appdb，而不是"所有库所有网段”；最后一行 reject 是显式兜底（不写也默认拒绝，但写出来意图清楚）。TLS、pgbouncer 接入后的地址写法、以及进门之后的权限模型，见 12-安全与权限管理.md。

psql 速查

连接串两种写法等价：

psql "postgres://app:devpass123@db.internal:5432/appdb?sslmode=require&application_name=cli"
psql -h db.internal -p 5432 -U app -d appdb -w

-w 禁止回退到终端输口令（脚本里防卡死），sslmode=require 在生产默认带上。PGPASSWORD 环境变量或 ~/.pgpass 文件可以避免口令进命令行，后者权限必须 0600。

元命令（在 psql 交互里敲）：

  • \l 列数据库，\c appdb 切库，\conninfo 确认当前连到哪
  • \dt / \dt+ 列表（带 + 显示大小、描述），\dn 列 schema，\dx 列扩展
  • \d orders 看表结构 + 索引；\d+ orders 再多显示存储、TOAST 信息和表占用
  • \x 打开扩展显示——一行字段多时竖排输出，读 pg_stat_activity 救命用
  • \timing 打开每条语句的耗时
  • -E 连同元命令展开出的内部 SQL 一起打印（想知道 \d 到底查了什么系统表，就靠它）

非交互执行，脚本化必备：

psql -h 127.0.0.1 -U app -d appdb -c "SELECT count(*) FROM orders;"          # 一条语句，跑完退出
psql -v ON_ERROR_STOP=1 -f ./migrations/001_init.sql                          # 跑文件，出错立即退出而不是继续
psql -d appdb -c "COPY orders TO STDOUT WITH CSV HEADER" > orders.csv

确认版本和数据目录位置：

SELECT version();              -- PostgreSQL 18.6 on x86_64... 带平台和构建信息
SHOW server_version;           -- 只要版本号
SHOW data_directory;           -- 需要超级用户

装完先跑一遍验证清单 

# 1. 能连上、版本对
psql -h 127.0.0.1 -U postgres -c "SELECT version();"
# 2. 关键配置看一眼
psql -c "SHOW listen_addresses;" -c "SHOW data_directory;" \
     -c "SELECT name, setting FROM pg_settings WHERE name IN ('password_encryption','wal_level','shared_buffers');"
# 3. 18 默认应有数据校验和（initdb 默认开启）——校验和是集群属性，用 pg_controldata 直接看
pg_controldata "$PGDATA" | grep -i checksum   # 期望 "Data page checksum version: 1"
# 4. 真写一笔：建表、插入、回读、删掉，证明 WAL 和 fsync 链路是通的
psql -c "CREATE TABLE t_probe(id int); INSERT INTO t_probe VALUES (1); SELECT * FROM t_probe; DROP TABLE t_probe;"

第 4 步不是形式主义：容器里 volume 挂错、权限不对时，前面都正常，一写就炸。

版本升级路径概述。小版本（18.1 → 18.6）只换二进制、重启服务即可，数据格式不变，理论上无需备份但升级前照例先 pg_dump 或物理备份一份。大版本（17 → 18）两条主流路线：一是 pg_upgrade 原地迁移，快（--link 模式近乎瞬时，但迁移后旧目录就不能回退使用了；18 要求新旧集群的校验和配置一致，这正好是上面 checksum 那条命令的用处），适合停机窗口能接受的场景；二是逻辑复制，旧版本当发布端、新版本当订阅端，数据追平后切流，停机接近零、还能顺便把新实例当演练环境，代价是搭建复杂。两条路的完整操作在 10-复制与高可用.md 和 11-备份恢复与运维.md。

实战建议

  • 生产环境一律 PGDG 源装，别用发行版自带包——大版本和补丁都会落后，等到出事才发现修 CVE 的版本装不上。
  • Docker 只用于本地开发和临时验证；上生产优先裸进程或 systemd，至少把数据卷放在专用本地盘上，别把 overlayfs 当存储驱动。
  • 改任何参数前，先 SELECT name, context FROM pg_settings WHERE name = '参数名';——知道是 reload 还是 restart，能避免一次不必要的重启事故。
  • 连接数焦虑先动 pgbouncer，再动 max_connections。看到"把 max_connections 调到 5000"的帖子，基本可以判断作者没在生产环境看过 PG 的内存分配。
  • pg_hba 每次改动后立刻用 pg_hba_file_rules 验证解析结果，再 reload；行内注释（#）紧跟方法后面是合法的，但整行注释别写成多行粘连。
  • 18 的新实例默认有校验和、口令默认 scram，这两件事不用再手动配置；从 ≤13 一路升级上来的老库则务必确认这两项，老习惯是事故的温床。
架构与核心概念