第 02 章：环境搭建

zjc 于 2026-01-02 发布

这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。本章搭建 PostgreSQL 学习与测试环境，并梳理生产部署必须提前规划的目录、认证、网络、备份和版本策略。

2.1 版本选择

查看版本：

SELECT version();
SHOW server_version;

建议：

环境	版本策略
学习	当前主流稳定版本
新项目	选择社区仍在维护的版本
存量系统	固定小版本，按计划升级
扩展使用	确认 PostGIS、pgvector 等兼容性

跨大版本升级必须使用 pg_upgrade、逻辑复制或导出导入方案，并提前演练。

2.2 Docker 启动

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_DB=shop \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16

连接：

docker exec -it postgres psql -U postgres -d shop

生产不建议只用默认自签名信任环境，应配置强密码、TLS、网络访问控制和最小权限用户。

2.3 Linux 安装

以包管理器安装后，确认服务：

sudo systemctl status postgresql
sudo systemctl enable postgresql

切换系统用户并连接：

sudo -iu postgres psql

常见目录：

/var/lib/postgresql/16/main       数据目录
/etc/postgresql/16/main           配置目录
/var/log/postgresql               日志目录

实际路径与发行版和安装方式有关，以 SHOW data_directory; 为准。

2.4 psql

常用命令：

\l               列出数据库
\c shop          切换数据库
\dt              列出表
\d orders        查看表结构
\di              查看索引
\du              查看用户和角色
\x               切换扩展显示
\timing          显示 SQL 耗时
\q               退出

执行文件：

psql -h 127.0.0.1 -U postgres -d shop -f schema.sql

导出 CSV：

\copy (SELECT * FROM orders) TO 'orders.csv' WITH CSV HEADER

2.5 初始化数据库

CREATE DATABASE shop;
CREATE USER app_user WITH PASSWORD 'strong-password';
GRANT CONNECT ON DATABASE shop TO app_user;

\c shop
CREATE SCHEMA app AUTHORIZATION app_user;
ALTER ROLE app_user SET search_path = app, public;

最小权限：

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

2.6 核心配置

postgresql.conf：

listen_addresses = '*'
max_connections = 200
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 32MB
maintenance_work_mem = 512MB
wal_level = replica
logging_collector = on
log_min_duration_statement = 500

初始参考不是固定公式，要根据内存、连接类型、查询负载和磁盘压测调整。

2.7 认证与网络

pg_hba.conf 控制：

TYPE  DATABASE  USER  ADDRESS  METHOD

示例：

hostssl shop app_user 10.0.0.0/8 scram-sha-256
local   all  postgres          peer

建议：

远程连接使用 hostssl；
密码使用 scram-sha-256；
只放行应用网段；
应用用户与管理员分离；
不使用 trust 认证生产地址。

2.8 基础验证

SELECT current_database(), current_user, version();
SHOW data_directory;
SHOW shared_buffers;
SHOW work_mem;

查看连接：

SELECT pid, usename, datname, client_addr, state, query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();

2.9 常见问题

问题	排查
connection refused	服务、监听地址、防火墙
password authentication failed	用户、密码、pg_hba 方法
no pg_hba.conf entry	地址和方法未放行
too many clients	max_connections 和连接池
database does not exist	连接串库名错误

本章小结

环境搭建不只是安装成功。PostgreSQL 生产环境要提前固定版本、规划数据目录、配置网络认证、拆分用户权限、开启日志和备份。psql 是日常运维最应该熟练的工具。

思考题

shared_buffers 和 effective_cache_size 分别表达什么？
为什么生产远程连接建议使用 hostssl？
如何为应用创建最小权限用户？
pg_hba.conf 的匹配顺序有什么影响？
大版本升级前需要准备什么？