PostgreSQL 安装与快速上手

安装方式怎么选

PostgreSQL 的安装比 MySQL 省心，没有复杂的初始化向导，装完就是一个能连的实例。常见两条路：

方式	适合场景	优点	缺点
apt 官方源	裸机、云主机上的生产部署	系统托管、开机自启、升级方便	目录结构由发行版约定，配置位置要记
Docker	本地开发、快速验证、CI	一条命令起，版本随便切	数据卷和端口要自己规划

本篇环境是 Ubuntu 22.04 + PostgreSQL 16。生产推荐 apt 源安装，跟着系统做安全更新；本地折腾用 Docker，删了重建不心疼。

为什么不用 Ubuntu 自带的源

Ubuntu 22.04 仓库里的 PostgreSQL 版本偏旧（默认 14），而且小版本更新滞后。PGDG（PostgreSQL Global Development Group）官方源能装到 16、17 这些新版本，小版本也会及时跟进。生产上认准 PGDG 源。

apt 官方源安装

先加 PGDG 源和签名：

# 安装依赖
sudo apt update
sudo apt install -y curl ca-certificates gnupg

# 导入 PGDG 签名
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  https://www.postgresql.org/media/keys/ACCC4CF8.asc

# 添加源（Ubuntu 22.04 代号 jammy）
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
https://apt.postgresql.org/pub/repos/apt jammy-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list

然后安装：

sudo apt update
sudo apt install -y postgresql-16 postgresql-client-16

装完 apt 会自动做三件事：创建 postgres 系统用户、在 /etc/postgresql/16/main/ 生成配置、用 pg_ctlcluster 起一个名为 main 的集群。检查一下：

# 看集群状态
pg_lsclusters

# 预期输出类似
# Ver Cluster Port Status Owner    Data directory              Log file
# 16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

启停用 systemd，注意服务名带集群名：

sudo systemctl status postgresql@16-main
sudo systemctl restart postgresql@16-main
sudo systemctl enable postgresql@16-main

Debian/Ubuntu 的目录约定

用 apt 装的 PostgreSQL，配置和数据是分开的：

• 配置：/etc/postgresql/16/main/，包括 postgresql.conf、pg_hba.conf、pg_ident.conf
• 数据：/var/lib/postgresql/16/main/
• 日志：/var/log/postgresql/postgresql-16-main.log

不要直接改数据目录里的 postgresql.conf，那是初始化时复制过去的副本，真正生效的是 /etc 下这份。用 SHOW config_file; 确认当前实例在读哪个文件。

Docker 方式

本地开发最省事的起法：

docker run -d \
  --name pg16 \
  -e POSTGRES_PASSWORD='Pg@123456' \
  -e POSTGRES_DB=app_db \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16

几个参数的含义：

• POSTGRES_PASSWORD：postgres 超级用户的密码，必填（除非显式允许空密码）。
• POSTGRES_DB：初始化时自动建的库，不填则默认建一个和用户同名的库。
• 数据卷：必须挂载，否则容器删了数据就没了。也可以挂宿主机目录，比如 -v ~/pgdata:/var/lib/postgresql/data。

进容器执行 psql：

docker exec -it pg16 psql -U postgres -d app_db

Docker 里的监听地址

官方镜像默认把 listen_addresses 设成 *，并允许密码登录，所以 -p 5432:5432 一开，局域网内谁都能连。云主机上务必把端口映射改成 -p 127.0.0.1:5432:5432，或者用防火墙挡住 5432。

initdb 与集群目录

PostgreSQL 的一个「集群」（cluster）就是一个数据目录 + 一套配置 + 一个监听端口。一台机器可以跑多个集群，靠端口和数据目录区分。

apt 装的时候已经自动 initdb 过了。如果要从零手工初始化（比如数据盘挂载到 /data）：

# 建目录并交给 postgres 用户
sudo mkdir -p /data/pgdata
sudo chown postgres:postgres /data/pgdata
sudo chmod 700 /data/pgdata

# 切换到 postgres 用户初始化
sudo -u postgres /usr/lib/postgresql/16/bin/initdb \
  -D /data/pgdata \
  --encoding=UTF8 \
  --locale=C.UTF-8

# 启动（-D 指定数据目录，-l 指定日志）
sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl \
  -D /data/pgdata \
  -l /data/pgdata/server.log \
  start

initdb 生成的目录结构里，日常打交道的几个：

路径	用途
postgresql.conf	主配置，监听地址、内存、WAL 等
pg_hba.conf	客户端认证规则，谁能从哪连、用什么方式认证
pg_wal/	WAL 段文件，默认 16MB 一个
base/	各数据库的实际数据文件，子目录名是数据库 OID
pg_stat/	统计信息（15 起统计信息在共享内存，磁盘上仍有部分持久化文件）
postmaster.pid	运行中的实例锁文件，删了会出大事

数据目录权限

initdb 要求数据目录权限是 700 且属主是运行数据库的用户，否则直接拒绝启动。这是保护机制，不要为了省事 chmod 777。另外数据目录不要放在 NFS 上（除非用支持 POSIX 锁的方案），WAL 的 fsync 语义在 NFS 上不可靠。

pg_hba.conf 认证配置

pg_hba.conf 决定「哪个用户、从哪个地址、连哪个库、用什么方式认证」。规则从上往下匹配，第一条命中就生效，所以顺序很关键。

一个生产可用的最小示例：

# TYPE  DATABASE  USER      ADDRESS         METHOD
# 本地 postgres 用户走 peer，免密（仅限本机 root 切过去）
local   all       postgres                  peer
# 本机其他用户也走 peer
local   all       all                       peer
# 本机 TCP 连接走密码
host    all       all       127.0.0.1/32    scram-sha-256
host    all       all       ::1/128         scram-sha-256
# 应用服务器网段，只允许连指定库
host    app_db    app_user  10.0.0.0/24     scram-sha-256

认证方式怎么选：

方式	说明	适用场景
peer	用操作系统用户名匹配数据库用户名	本机管理，sudo -u postgres psql
scram-sha-256	密码认证，PG 10+ 的默认推荐	应用连接、跨机连接
md5	老式密码认证，兼容旧客户端	仅用于过渡，新环境别用
cert	客户端证书认证	高安全要求的内网
trust	无条件放行	只应出现在临时测试环境

改完不用重启，reload 即可：

sudo systemctl reload postgresql@16-main
# 或者在 psql 里执行
psql -U postgres -c "SELECT pg_reload_conf();"

密码存储方式

password_encryption 默认就是 scram-sha-256。如果从老版本迁移过来，用户密码可能还是 md5 哈希，需要让用户重新设一次密码才会转成 SCRAM。pg_hba.conf 里写 scram-sha-256 而用户密码是 md5 时，连接会直接失败，报错信息里会提示认证方式不匹配。

postgresql.conf 关键项

配置文件很长，真正需要动的就那几项。下面这份是按「4 核 8G、OLTP 为主」给的起点：

# 连接与监听
listen_addresses = 'localhost'        # 对外提供服务改成具体网卡地址或 '*'
port = 5432
max_connections = 200                 # 配合连接池，不要盲目调大

# 内存
shared_buffers = 2GB                  # 物理内存的 25% 左右
effective_cache_size = 6GB            # 告诉优化器 OS 缓存大概有多少，不实际分配
work_mem = 16MB                       # 每个排序/哈希操作的上限，乘上并发数很吓人
maintenance_work_mem = 512MB          # VACUUM、CREATE INDEX 用

# WAL
wal_level = replica                   # 流复制必需；逻辑复制用 logical
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

# 日志
log_min_duration_statement = 1000     # 超过 1 秒的语句记日志，0 表示全记，-1 关闭
log_line_prefix = '%m [%p] %q%u@%d '  # 时间、PID、用户、库名
log_checkpoints = on
log_lock_waits = on                   # 锁等待超过 deadlock_timeout 记日志

# 时区与字符集
timezone = 'Asia/Shanghai'
log_timezone = 'Asia/Shanghai'

几个容易踩的点：

• work_mem 不是总内存。它是每个排序、哈希、位图操作各自的上限，一个复杂查询可能同时用好几份，再乘上并发连接数。设大了容易 OOM。
• shared_buffers 别照搬 MySQL。PostgreSQL 依赖 OS page cache 做二级缓存，shared_buffers 给到 25% 左右即可，给到 70% 反而挤占文件缓存。
• listen_addresses 默认只监听 localhost。远程连不上先查这里，再查防火墙和 pg_hba.conf。

改配置有两种方式：

# 方式一：改 postgresql.conf 后 reload（大部分参数支持）
sudo systemctl reload postgresql@16-main

# 方式二：SQL 动态修改，写入 postgresql.auto.conf
psql -U postgres -c "ALTER SYSTEM SET work_mem = '32MB';"
psql -U postgres -c "SELECT pg_reload_conf();"

优先级

postgresql.auto.conf 的优先级高于 postgresql.conf。用 ALTER SYSTEM 改过的参数，再手工改 postgresql.conf 是不生效的，会让人怀疑人生。用 ALTER SYSTEM RESET 参数名; 清掉，或者直接看 SHOW 参数名; 确认最终值。

psql 连接与常用元命令

psql 是官方命令行客户端，运维排查基本靠它。

连接方式：

# 本机 peer 认证（root 切到 postgres 用户）
sudo -u postgres psql

# 指定主机、端口、用户、库
psql -h 127.0.0.1 -p 5432 -U app_user -d app_db

# 连接串形式，适合写进脚本
psql "postgresql://app_user:password@127.0.0.1:5432/app_db?sslmode=prefer"

密码可以走 PGPASSWORD 环境变量（不推荐，会留在 history 里），更规范的是写 ~/.pgpass：

# ~/.pgpass，权限必须是 600
127.0.0.1:5432:app_db:app_user:your_password

进去之后，元命令（反斜杠开头）比 SQL 更常用：

元命令	作用
\l	列出所有数据库
\c dbname	切换数据库
\dt	列出当前库的表
\d 表名	查看表结构、索引、约束
\d+ 表名	额外显示存储大小、列注释
\du	列出角色和权限
\dn	列出 schema
\df	列出函数
\x	切换纵向显示，宽表查询必备
\timing	开关 SQL 执行耗时显示
\i file.sql	执行 SQL 文件
\e	打开编辑器写多行 SQL
\q	退出

排查时的组合拳

\x + \timing 是排查慢查询的起手式：先看单条语句耗时，再用 \d+ 表名 看表和索引的大小。宽表结果用 \x 纵向展开，比横向滚动舒服得多。\watch 5 可以让一条查询每 5 秒重跑一次，盯连接数、锁等待时很好用。

建库建用户授权

最小可用的初始化脚本，建议存成文件用 psql -f 执行，方便留档：

-- 1. 建业务角色（LOGIN 才允许连接，密码用强口令）
CREATE ROLE app_user WITH LOGIN PASSWORD 'ChangeMe_2024';

-- 2. 建库，指定属主；模板用 template0 避免继承多余对象
CREATE DATABASE app_db
  WITH OWNER = app_user
       ENCODING = 'UTF8'
       TEMPLATE = template0;

-- 3. 切到新库后授权（\c app_db）
-- 15 起 public schema 默认不再对所有人可写，必须显式授权
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;

-- 4. 已有表的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- 5. 以后由 postgres 或迁移账号新建的表也自动授权
--    （建表若由 app_user 执行，它本身是属主，不需要这段）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;

创建只读账号的思路是反过来：先建角色，再授 CONNECT + USAGE + SELECT，并把它加入一个只读组，别给 CREATE。

-- 只读角色示例
CREATE ROLE readonly WITH LOGIN PASSWORD 'ReadOnly_2024';
GRANT CONNECT ON DATABASE app_db TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

PG 15 的 public schema 变更

PostgreSQL 15 起，public schema 不再默认对 PUBLIC 角色开放 CREATE 权限，只有库属主能建对象。从 14 升级上来的人经常遇到「应用建表报 permission denied」，原因就是这里。按上面的脚本显式授权即可。

小结

• 生产用 PGDG 官方源装，Ubuntu 22.04 对应 jammy-pgdg；本地验证用 Docker，记得挂数据卷并限制端口映射。
• apt 安装自动生成 main 集群，配置在 /etc/postgresql/16/main/，用 pg_lsclusters 和 systemctl 管理。
• 手工初始化用 initdb，数据目录权限 700、属主 postgres，别放 NFS。
• pg_hba.conf 从上往下第一条匹配生效，生产用 scram-sha-256，改完 reload 不用重启。
• postgresql.conf 重点看 shared_buffers、work_mem、wal_level、log_min_duration_statement；ALTER SYSTEM 写入的 postgresql.auto.conf 优先级更高。
• psql 元命令先记 \l、\c、\dt、\d、\du、\x、\timing，够日常用了。
• 如果建表由 postgres 或迁移账号执行，别忘了 ALTER DEFAULT PRIVILEGES，否则新表应用没权限；应用自己建表则天然是属主。

装好只是开始，下一篇《核心使用》进入 SQL 层面：JSONB、窗口函数、索引选型和 EXPLAIN 分析。