PostgreSQL从入门到实战：安装、高阶SQL与生产运维指南-嵌云网-嵌入式AI开发资源站

做后端开发时，MySQL 往往是很多人接触的第一款数据库，但一旦遇到复杂查询、JSON 文档存储、地理位置检索或需要严谨事务支持的场景，MySQL 用起来总有些“使不上劲”的感觉。PostgreSQL 这几年热度持续上升，既可以当传统关系型数据库用，又能在 JSON、全文检索、空间数据、自定义类型等方向发挥独特优势。本文就从零开始，完整走一遍 PostgreSQL 的安装、基础操作、进阶 SQL 和常见生产实践，覆盖从入门到落地的全流程。

这篇文章适合三类读者：第一次安装 PostgreSQL 的新手，希望获得一套完整安装步骤；有一定基础、想系统掌握索引、事务、窗口函数、JSONB 等进阶能力的开发者；以及准备在项目里落地 PostgreSQL 并关心排错、备份、安全和连接池的工程人员。整篇文章的所有命令和代码均采用通用写法，版本差异会单独说明，你可以直接复制运行，再根据实际环境微调。

1. PostgreSQL 是什么：为什么越来越多的团队选择它

1.1 从一段通俗的话开始

PostgreSQL 是一个开源的关系型数据库管理系统，诞生于 1980 年代，经过三十多年迭代，已经成为功能最全面的开源数据库之一。很多人叫它“Postgres”，这两个名字指的是同一个东西。和 MySQL 相比，PostgreSQL 的最大特点是对 SQL 标准的支持非常完整，同时对高级数据类型和扩展能力的包容度很高。

如果说 MySQL 是“轻巧耐用”的数据库，那么 PostgreSQL 更像一个“瑞士军刀”式的数据平台。你可以在里面存普通的关系表，可以把整段 JSON 放进去并做高效查询，可以处理空间坐标数据，可以做全文检索，甚至可以自定义数据类型。很多互联网公司和科研机构都在用它，尤其是金融、地理信息、内容平台、数据分析这类对数据一致性或类型灵活度要求较高的场景。

1.2 核心特性

PostgreSQL 核心特性可以从四个维度理解：

事务与一致性：支持 ACID，提供 Read Committed、Repeatable Read、Serializable 等隔离级别，适合需要严格数据一致性的业务系统。
扩展类型体系：内置 JSON/JSONB、数组、枚举、范围类型、网络地址类型、几何类型，还能通过插件支持 PostGIS 空间扩展、向量检索扩展等。
索引能力丰富：支持 B-Tree、Hash、GIN、GiST、SP-GiST、BRIN 等多种索引结构，能针对不同数据形态选择合适方案。
数据完整性：支持主键、外键、唯一约束、排除约束、触发器、规则、视图、物化视图等关系型数据库完整机制。

1.3 与 MySQL 的简单对比

很多开发者的第一个问题是“我已经会用 MySQL，还有必要学 PostgreSQL 吗”。从功能覆盖来说，MySQL 擅长读多写少的 Web 场景，生态成熟，运维资料多；PostgreSQL 则在复杂查询、JSON 处理、GIS、数据分析等领域能力更强。如果你所在团队已经在使用 PostgreSQL，或者新项目对数据一致性、复杂计算、JSON 存取有明确需求，PostgreSQL 是值得投入学习的方案。

从实际选型角度看，两者并非互相替代，更多是各取所长。MySQL 在某些高并发简单读写场景下经验积累丰富，PostgreSQL 则在功能纵深上更胜一筹。真正决定选型的应该是业务需求，而不是数据库热度。

需要模型API调用？免费领10W Token，多模型网关一键接入 Claude、DeepSeek 等主流模型。

2. 环境准备与版本选择

2.1 PostgreSQL 版本怎么选

PostgreSQL 社区大约每年发布一个大版本，版本号从 16、17 这样的数字递增。选择版本时，要先看项目实际依赖和服务器操作系统官方源是否自带，再看生产环境是否已有固定版本基线。

通常来说，PostgreSQL 16 是目前生产环境使用非常广泛的稳定版本，也是很多教程默认演示的版本。PostgreSQL 17 属于较新版本，适合想要体验最新特性、且团队有能力处理升级问题的场景。如果公司已经有数据库基线，比如内部规范固定使用某个大版本，就优先按公司规范来。个人学习时选择 16 或 17 都没有问题，重点是把安装过程和环境配置走通。

这里要特别提醒一个容易踩坑的点：不要盲目安装比项目依赖更新的版本。比如某些 ORM 框架或报表工具只测试到 PostgreSQL 13，直接跳到 17 可能出现驱动兼容问题。数据库驱动版本、客户端工具版本和服务端版本需要整体匹配。

2.2 操作系统环境

PostgreSQL 支持 Windows、Linux、macOS 三大主流平台，生产环境最常见的是 Linux，尤其是 CentOS、Ubuntu、Debian 这些发行版。不同操作系统安装方式差别较大，但基础路径一致：

Windows：通过图形安装包安装，服务以后台进程方式运行，适合开发调试和本地学习。
Linux：通过包管理器安装，受发行版仓库版本影响，也可以从官方 APT/Yum 仓库安装指定版本。
Docker：通过官方镜像直接启动，适合环境隔离、快速试用和 CI/CD 集成。

2.3 检查本机环境

安装前最好先检查本机是否已经存在 PostgreSQL，避免端口冲突和数据残留。

Windows 可以在命令行执行：

bash复制sc query postgresql
netstat -ano | findstr 5432

Linux 可以执行：

bash复制ps -ef | grep postgres
systemctl status postgresql
ss -lntp | grep 5432

如果发现 5432 端口已经被占用，需要确认是已有 PostgreSQL 实例还是其他程序占用。不建议直接强行杀进程，正确做法是调整新实例的端口，或者优先复用已有实例。

3. PostgreSQL 安装实操：三种主流方式

3.1 Windows 安装教程

Windows 用户最常用的方式是下载 EDB 提供的图形化安装包。打开 PostgreSQL 官网下载页面后，选择对应版本和操作系统位数，一般下载 .exe 格式的安装程序。

安装过程有几个关键点需要留意。

选择安装目录时，建议不要放在 C:\Program Files 这类带空格的路径下，可以自定义为 D:\PostgreSQL\16，后续查找配置文件和日志会更方便。

设置 postgres 超级用户密码时，一定要记住这个密码。PostgreSQL 安装时会创建一个名为 postgres 的超级用户，后续所有高权限操作都依赖这个账号。密码建议包含大小写字母、数字和特殊符号，但不要复杂到自己都记不住，也不要写进明文配置直接提交到代码仓库。

端口默认是 5432，如果本机已有其他 PostgreSQL 实例，可以改成 5433 或自定义端口。安装完成后，在开始菜单找到 “pgAdmin 4” 或直接打开命令行，用 psql 验证安装是否成功。

Windows 下安装完成后，服务默认注册为 postgresql-x64-16，可以在“服务”管理器中确认启动状态，也可以使用命令行：

bash复制pg_ctl status -D "D:\PostgreSQL\16\data"

如果提示找不到 pg_ctl，需要把 D:\PostgreSQL\16\bin 添加到系统环境变量 PATH 中。

3.2 Linux 安装教程（Ubuntu/CentOS）

Linux 是 PostgreSQL 生产环境最常见的地方。以 Ubuntu 为例，官方仓库中自带 PostgreSQL，但版本可能偏旧。如果要安装指定新版本，推荐添加 PostgreSQL 官方 APT 仓库。

先完成基础更新和软件包安装：

bash复制sudo apt update
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/scripts/apt.postgresql.org.sh

脚本执行后，系统会添加官方 APT 源。然后安装 PostgreSQL 16：

bash复制sudo apt install postgresql-16

安装完成后，PostgreSQL 的数据库目录通常位于 /etc/postgresql/16/main，二进制文件位于 /usr/lib/postgresql/16/bin，数据目录位于 /var/lib/postgresql/16/main。

CentOS/RHEL 系列则推荐使用官方 RPM 仓库。首先安装仓库配置包，然后用 dnf 安装：

bash复制sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql16-server

初始化数据库并启动：

bash复制sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16

Ubuntu 系统安装完成后会自动创建 postgres 系统用户和数据库超级用户。切换身份并进入 psql：

bash复制sudo -i -u postgres
psql

如果希望使用 postgres 用户在远程客户端登录，还需要修改 pg_hba.conf 认证配置和监听地址，这一点后面单独介绍。

3.3 Docker 安装教程

Docker 方式最适合快速体验、本地开发或搭建测试环境。它不需要在宿主机安装数据库客户端，也不污染系统环境，一条命令就能启动。

先确保 Docker 已安装，然后拉取镜像并启动容器：

bash复制docker run -d \
  --name postgres16 \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=123456 \
  -e POSTGRES_DB=testdb \
  -p 5432:5432 \
  postgres:16

参数说明：

POSTGRES_USER：容器内创建的超级用户名。
POSTGRES_PASSWORD：超级用户密码。
POSTGRES_DB：初始化时自动创建的数据库。
-p 5432:5432：把容器内 5432 端口映射到宿主机。

进入容器执行 psql：

bash复制docker exec -it postgres16 psql -U postgres -d testdb

Docker 方式的一个注意事项是数据持久化。容器删除后，默认情况下数据也会消失。生产环境使用 Docker 启动 PostgreSQL 时，需要挂载宿主机目录到容器内 /var/lib/postgresql/data：

bash复制docker run -d \
  --name postgres16 \
  -e POSTGRES_PASSWORD=123456 \
  -p 5432:5432 \
  -v /data/pgdata:/var/lib/postgresql/data \
  postgres:16

挂载目录后，即使容器被杀掉，数据依然保留在宿主机。需要注意的是，不同镜像版本可能对数据目录权限有要求，挂载时如果出现权限问题，需要确认目录属主。

3.4 安装后的基本检查

无论使用哪种安装方式，安装完成后都应该执行同一套检查，确认服务正常。

查看数据库版本：

sql复制SELECT version();

查看当前运行的 PostgreSQL 服务进程：

bash复制ps -ef | grep postgres

查看端口监听状态：

bash复制ss -lntp | grep 5432

尝试连接默认数据库：

bash复制psql -U postgres -h localhost -p 5432

连接成功后，输入 \q 退出。如果整个过程没有报错，说明安装已经完成，接下来可以进入数据库操作实战。

4. 基础实战：库表设计与增删改查

4.1 认识 psql 命令行工具

psql 是 PostgreSQL 自带的交互式命令行工具，功能非常强大。它能执行 SQL 语句，也能执行一系列以反斜杠开头的元命令，用于查看数据库、表结构和索引信息。

常用元命令：

bash复制\l                          # 查看数据库列表
\c testdb                   # 切换到 testdb 数据库
\dt                         # 查看当前库的所有表
\d users                    # 查看 users 表结构
\di                         # 查看当前库的索引
\du                         # 查看所有角色
\q                          # 退出 psql

这些命令看起来琐碎，但排查问题时非常实用。比如忘记表字段名，一个 \d 就能快速看到结构。

4.2 创建数据库和用户

先创建项目独立数据库，避免把所有数据都堆在默认的 postgres 库里。好的习惯是“一个应用一个库，一个库一套专属账号”。

sql复制CREATE DATABASE shopdb;

CREATE USER shop_user WITH PASSWORD 'shop_pass_2024';

GRANT ALL PRIVILEGES ON DATABASE shopdb TO shop_user;

这里需要注意，GRANT ALL PRIVILEGES ON DATABASE 只是给用户连接数据库的权限，不代表用户在数据库内可以随意建表操作。还需要给用户指定模式权限：

sql复制\c shopdb;

GRANT ALL ON SCHEMA public TO shop_user;

4.3 创建设计示例表

下面以电商订单系统为例子，设计三张核心表：用户表、商品表、订单表。

用户表：

sql复制CREATE TABLE users (
    id bigserial PRIMARY KEY,
    username varchar(50) NOT NULL UNIQUE,
    email varchar(100) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

商品表：

sql复制CREATE TABLE products (
    id bigserial PRIMARY KEY,
    name varchar(200) NOT NULL,
    price numeric(10,2) NOT NULL CHECK (price >= 0),
    stock int NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now()
);

订单表：

sql复制CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users(id),
    status varchar(20) NOT NULL DEFAULT 'pending',
    total_amount numeric(10,2) NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now()
);

代码解释：

bigserial 是自增 bigint 类型，建表时通常直接用它作为主键。
timestamptz 存储带时区的时间戳，比不带时区的 timestamp 更推荐。
numeric(10,2) 适用于金额字段，避免浮点数精度问题。
CHECK 约束用来限制字段取值范围。
REFERENCES 定义外键，确保订单表里的 user_id 一定存在于用户表中。

4.4 基础增删改查

插入用户数据：

sql复制INSERT INTO users (username, email) VALUES
('zhangsan', 'zhangsan@example.com'),
('lisi', 'lisi@example.com');

插入商品数据：

sql复制INSERT INTO products (name, price, stock) VALUES
('无线鼠标', 99.00, 500),
('机械键盘', 399.00, 200),
('显示器', 1299.00, 50);

插入订单数据：

sql复制INSERT INTO orders (user_id, status, total_amount) VALUES
(1, 'completed', 499.00),
(1, 'pending', 1299.00),
(2, 'completed', 99.00);

基础查询：

sql复制SELECT id, username, email FROM users;

SELECT name, price FROM products WHERE price > 100 ORDER BY price DESC;

SELECT user_id, status, total_amount
FROM orders
WHERE created_at >= now() - interval '7 days';

更新和删除：

sql复制UPDATE products SET stock = stock - 1 WHERE id = 1;

DELETE FROM orders WHERE status = 'pending' AND created_at < now() - interval '30 days';

数据库操作中最重要的原则是：UPDATE 和 DELETE 一定要先确认 WHERE 条件，否则容易造成全表更新或全表删除。执行影响行数较大的操作前，先 SELECT 统计一遍更稳妥。

5. 高阶实战：索引、事务与 SQL 优化

5.1 索引：查询加速的核心工具

索引相当于书的目录。没有索引时，数据库要一行一行扫描全表，数据量大了以后必然会慢。PostgreSQL 支持的索引类型很多，最常用的是 B-Tree。

创建普通 B-Tree 索引：

sql复制CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_products_price ON products(price);

多列联合索引适合组合查询条件：

sql复制CREATE INDEX idx_orders_user_status ON orders(user_id, status);

唯一索引适合保证数据唯一性：

sql复制CREATE UNIQUE INDEX idx_users_email ON users(email);

索引虽然能加速查询，但不是越多越好。每次写入数据时，数据库都要同步维护索引结构，索引过多会拖慢 INSERT 和 UPDATE 性能。实际项目中，优先为高频查询、WHERE 条件、JOIN 和 ORDER BY 字段创建索引，低热度字段不要随意加索引。

5.2 事务与隔离级别

事务是数据库保证数据一致性的手段。PostgreSQL 中一个事务内的多条语句要么全部成功，要么全部回滚。

事务基本用法：

sql复制BEGIN;

UPDATE products SET stock = stock - 1 WHERE id = 1;
UPDATE orders SET status = 'completed' WHERE id = 100;

COMMIT;

如果在事务中发现问题，可以回滚：

sql复制BEGIN;

UPDATE products SET stock = stock - 1 WHERE id = 1;

-- 发现条件不对
ROLLBACK;

PostgreSQL 默认隔离级别是 Read Committed，也就是只能读到已提交的数据。这种方式在大多数业务场景足够，但如果业务要求更强的数据一致性，比如转账场景中要避免两次读之间的数据差异，可以手动提高隔离级别。

sql复制BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;
-- 在此事务中，多次读取同一行数据时，结果始终保持一致

COMMIT;

事务使用中最常见的误区是忘记提交。代码里如果开启了事务却没有 COMMIT，连接会一直占用资源，最终可能拖垮连接池。无论用命令行还是编程语言操作数据库，都要养成“开启事务，结束后必须提交或回滚”的习惯。

5.3 窗口函数：轻松完成业务排名

窗口函数可以在不改变行数的情况下，对每一行计算分组聚合结果。它的本质是“在结果集的窗口内做计算”。

假设有一张员工薪资表：

sql复制CREATE TABLE employees (
    id bigserial PRIMARY KEY,
    department varchar(50) NOT NULL,
    name varchar(50) NOT NULL,
    salary numeric(10,2) NOT NULL
);

INSERT INTO employees (department, name, salary) VALUES
('R&D', '张三', 12000),
('R&D', '李四', 10000),
('R&D', '王五', 15000),
('Sales', '赵六', 9000),
('Sales', '孙七', 11000);

查询每个部门薪资排名：

sql复制SELECT
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

执行结果中，每个部门内部会按薪资从高到低编号。RANK 函数有一个特点：如果两个人薪资相同，会并列名次，并且下一位名次会跳跃。如果不希望跳跃名次，可以使用 DENSE_RANK，或者使用 ROW_NUMBER 强制分配连续编号，具体用哪个取决于业务需求。

窗口函数虽然没有改变数据行数，但对于报表查询、排行榜、分组 TopN 等需求，可以写出比传统自连接更清晰高效的 SQL。

5.4 CTE：让复杂查询具备可读性

CTE 全称 Common Table Expression，意思是公共表表达式。它可以把一个复杂查询拆成多个可见片段，SQL 执行时先计算每个 WITH 片段，再进入主查询。

简单 CTE 示例：

sql复制WITH completed_orders AS (
    SELECT user_id, sum(total_amount) AS amount
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
)
SELECT u.username, c.amount
FROM completed_orders c
JOIN users u ON u.id = c.user_id
ORDER BY c.amount DESC;

递归 CTE 是处理树形数据、层级菜单等结构的利器。比如生成从 1 到 5 的数字：

sql复制WITH RECURSIVE t(n) AS (
    VALUES (1)
    UNION ALL
    SELECT n + 1 FROM t WHERE n < 5
)
SELECT n FROM t;

递归 CTE 在组织架构、评论回复树、分类层级等场景中实用性很强，但使用时要特别注意递归终止条件，否则容易造成死循环。

5.5 JSONB：关系型数据库里的文档能力

JSONB 是 PostgreSQL 中一种用于存储 JSON 数据的二进制类型。相比普通文本类型，JSONB 会解析 JSON 内容并建立索引支持，查询时不需要重新解析字符串。

创建包含 JSONB 字段的表：

sql复制CREATE TABLE user_ext (
    id bigserial PRIMARY KEY,
    user_id bigint NOT NULL,
    profile jsonb NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO user_ext (user_id, profile) VALUES
(1, '{"age": 28, "city": "北京", "tags": ["developer", "postgres"]}'),
(2, '{"age": 32, "city": "上海", "tags": ["manager"]}');

使用 JSONB 字段查询：

sql复制SELECT
    user_id,
    profile->>'city' AS city,
    profile->'age' AS age
FROM user_ext
WHERE profile->>'city' = '北京';

JSONB 的 ->> 操作符返回文本类型，-> 返回 JSON 类型。创建 GIN 索引可以加速 JSONB 的包含查询和键值查询：

sql复制CREATE INDEX idx_user_ext_profile ON user_ext USING GIN(profile);

有了 GIN 索引，可以使用糖衣语法直接查询：

sql复制SELECT * FROM user_ext WHERE profile @> '{"city": "北京"}';

JSONB 适合存储结构灵活、不常变更但需要按属性查询的数据。对于定时任务配置、第三方接口报文、用户扩展字段等场景都很合适。

6. 高阶实战：分区表与性能配置

6.1 分区表：解决大表性能问题

当一张表的数据量达到亿级时，即使加了索引，查询和写入的性能也会明显下降。分区表是 PostgreSQL 提供的解决方案之一：把一张大表按某种规则拆分成多个物理子表，但外部访问时仍然像访问一张表。

最典型的是按时间范围分区。

sql复制CREATE TABLE orders_archive (
    id bigserial,
    created_at timestamptz NOT NULL,
    user_id bigint NOT NULL,
    status varchar(20) NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_archive_2024 PARTITION OF orders_archive
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_archive_2025 PARTITION OF orders_archive
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

插入分区表时，PostgreSQL 会根据 created_at 自动决定写入哪个子分区。查询时也可以只扫描对应分区，这就是“分区裁剪”。

分区表并非所有表都需要。对于数据量不大、查询条件稳定的小表，直接使用普通表加索引更简单。分区表适合时间序列日志、历史订单、监控指标这类有明显时间边界的海量数据。

6.2 使用 EXPLAIN ANALYZE 分析慢查询

排查慢 SQL 时，EXPLAIN ANALYZE 是最常用的工具。它能显示 SQL 的执行计划和每一步的真实耗时与数据行数。

sql复制EXPLAIN ANALYZE
SELECT user_id, count(*)
FROM orders
WHERE status = 'completed'
GROUP BY user_id;

输出中，重点留意以下几点：

Seq Scan：全表扫描，数据量很大时通常需要优化。
Index Scan / Bitmap Heap Scan：索引扫描，比全表扫描高效得多。
每个步骤的实际行数与预估行数是否有巨大偏差，偏差说明统计信息过期，需要执行 ANALYZE。
耗时最长的步骤在哪一层计划里，作为优化切入点。

日常优化建议顺序是：先看 SQL 条件是否有索引支撑，再考虑改写 SQL，最后才考虑调整数据库参数。不要把调参当首要手段，大多数慢 SQL 的根本原因是索引缺失或 SQL 写法问题。

6.3 连接池：应用连接 PostgreSQL 的正确姿势

每个数据库连接都是一份宝贵资源。应用程序每执行一条 SQL 就新建一次连接，在高并发下很容易把数据库连接数打满。连接池的作用是提前创建一批连接，应用从池中借用，用完归还。

Java 项目最常用的是 HikariCP，Python 项目常用 psycopg2 连接池或 SQLAlchemy 自带池。在生产环境，还经常使用 PgBouncer 作为 PostgreSQL 专用的轻量连接池中间件。PgBouncer 能同时服务多个应用，把连接数控制在稳定范围，避免每个应用都创建大量连接。

连接往往会限制应用侧连接数和数据库侧 max_connections 的关系：应用连接池的最大值，需要根据数据库 max_connections 和应用实例数量综合计算。比如数据库 max_connections=200，应用有 4 个实例，那么每个实例连接池上限就不要超过 40，还要预留运维、备份等工作连接。

6.4 常用性能参数调整

PostgreSQL 默认配置偏向保守，适合小内存机器和开发环境。生产环境可以按实际硬件调整以下几个参数。

shared_buffers：PostgreSQL 的共享缓冲区大小，通常设置为物理内存的 25% 左右，但不要超过系统总内存的 1/4 太多。

work_mem：单个排序或哈希操作能使用的内存大小。调大可以加速排序，但该参数是按操作分配的，并发高时容易撑爆内存，需要谨慎调整。

effective_cache_size：估算操作系统文件缓存大小，通常设置为物理内存的 50% 到 75%。这个值影响优化器选择索引还是全表扫描的决策，不会被实际分配内存。

max_connections：最大连接数。连接数不是越大越好，每个连接都会占用内存，过大的连接数反而会让整体性能下降。

调整参数需要修改 postgresql.conf 文件，不能直接在会话中修改共享内存参数。修改后重启 PostgreSQL 服务生效。

7. 编程语言连接 PostgreSQL 实战

7.1 Python 连接 PostgreSQL

Python 连接 PostgreSQL 最常用的驱动是 psycopg2。先安装依赖：

bash复制pip install psycopg2-binary

编写基础连接与查询代码：

python复制import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="shopdb",
    user="shop_user",
    password="shop_pass_2024"
)

cur = conn.cursor()

cur.execute("SELECT id, username, email FROM users")
rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

执行带参数的 SQL 时，一定不要使用字符串拼接，而应该使用参数占位符：

python复制cur.execute(
    "SELECT * FROM products WHERE price > %s",
    (100,)
)

%s 形式的参数化查询会把传入值作为数据处理，从根源上避免 SQL 注入风险。

写入数据时，需要手动提交事务：

python复制cur.execute(
    "INSERT INTO users (username, email) VALUES (%s, %s)",
    ("wangwu", "wangwu@example.com")
)
conn.commit()

psycopg2 默认在 conn.commit() 之前不会真正写入数据库。开发时经常会出现“明明执行了 INSERT 但数据查不到”的问题，原因通常就是忘记 commit。

7.2 Java 连接 PostgreSQL

Java 项目通过 JDBC 连接 PostgreSQL，需要引入 PostgreSQL JDBC 驱动。以 Maven 项目为例，在 pom.xml 中添加依赖：

xml复制<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version>
</dependency>

最小连接示例：

java复制import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class PgDemo {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/shopdb";
        String user = "shop_user";
        String password = "shop_pass_2024";

        Connection conn = DriverManager.getConnection(url, user, password);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT id, name FROM products");

        while (rs.next()) {
            System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
        }

        rs.close();
        stmt.close();
        conn.close();
    }
}

真实企业中通常不会裸用 JDBC，而是使用 MyBatis、Spring Data JPA 等框架。框架底层的连接管理同样依赖 JDBC 驱动，只是在封装层面帮助你完成连接池、事务和结果映射。无论用哪种框架，理解 JDBC 连接基础仍然很重要。

8. 常见问题与排查思路

PostgreSQL 使用过程中会遇到不少报错。下面整理几个高频问题及排查思路。

问题现象	常见原因	解决思路
psql: error: connection refused	数据库服务未启动或端口错误	检查服务状态，确认监听端口和连接端口一致
FATAL: password authentication failed	密码错误或认证方式不匹配	检查密码，或者调整 pg_hba.conf 认证方式
用户无法访问数据库	用户没有数据库连接权限	使用超级用户执行 GRANT 授权
无法远程连接	监听地址未开启	修改 listen_addresses 为 0.0.0.0 或指定 IP
执行启动失败：Address already in use	5432 端口被占用	查看端口占用进程，关闭服务或换端口
磁盘占用过大	大量写入产生 WAL 日志或表膨胀	检查 WAL 归档策略，执行 VACUUM/REINDEX

8.1 远程连接被拒绝

PostgreSQL 默认只监听 localhost，也就是只允许本机连接。远程连接需要两步修改。

修改 postgresql.conf 中的监听地址：

conf复制listen_addresses = '*'

修改 pg_hba.conf，在文件末尾添加允许远程访问的规则：

conf复制# 允许任意主机使用 md5 密码认证连接所有数据库
host all all 0.0.0.0/0 md5

修改后需要重启 PostgreSQL：

bash复制systemctl restart postgresql

需要强调，0.0.0.0/0 表示允许任意 IP 连接，只适合测试环境。生产环境必须缩小范围，只放行应用服务器或运维跳板机的 IP 网段。

8.2 密码认证失败

密码认证失败通常有三种情况：

密码本身输入错误，需要重置密码或找回正确密码。
用户不存在，需要用超级用户确认角色。
pg_hba.conf 认证方式与客户端发送方式不匹配，例如配置成 trust 但客户端强制走密码认证。

重置普通用户密码：

sql复制ALTER USER shop_user WITH PASSWORD 'new_password';

8.3 数据目录权限导致启动失败

Linux 下如果使用自定义数据目录启动 PostgreSQL，容易出现权限问题。PostgreSQL 进程以 postgres 用户运行，数据目录必须属于 postgres 用户，否则服务无法读写数据文件。

修正权限：

bash复制sudo chown -R postgres:postgres /data/pgdata

8.4 忘记 postgres 密码怎么办

很多新手安装完就忘了密码，这种情况不需要重新安装数据库。可以临时启用本地 trust 认证，然后重置密码。

修改 pg_hba.conf，把本地连接认证方式临时改为 trust：

conf复制host all all 127.0.0.1/32 trust

重启数据库，使用如下命令免密进入：

bash复制psql -U postgres

然后重置密码：

sql复制ALTER USER postgres WITH PASSWORD 'new_password';

最后把 pg_hba.conf 改回 md5 或 scram-sha-256，再重启数据库。这里需要特别提醒：生产环境不要长期开着 trust 认证，这相当于所有本机用户可以免密操作数据库，风险极高。

9. 最佳实践与工程建议

9.1 命名与设计规范

数据库表和字段命名建议全部使用小写英文字母、数字和下划线，不要使用驼峰式命名，也不要使用中文命名。PostgreSQL 对未加引号的表名会自动转为小写，统一命名风格能避免大量歧义。

表名使用复数还是单数取决于团队约定，但一个项目内部要统一。字段名推荐明确表达含义，比如 created_at、updated_at、user_id，避免使用无意义的 a、b 缩写。

数据库设计尽量遵守三范式，但不要为了范式牺牲查询性能。在高频查询场景，合理使用冗余字段或物化视图，往往比复杂的多表 JOIN 更快更稳。

9.2 安全配置

生产环境的 PostgreSQL 安全配置应从最小权限原则出发。应用账号只授权所需数据库和模式，不带 SUPERUSER 权限；访问内网敏感数据时尽量使用专用 VLAN 或至少做好防火墙规则。

密码安全是另一道红线。不要使用简单的业务名加数字作为密码，也不要将密码硬编码在代码仓库中。可以使用环境变量、配置中心或密钥管理系统。应用侧连接数据库时，尽量使用专门创建的账号替代超级用户，避免应用一旦被注入就获得整库控制权。

客户端连接 PostgreSQL 时，如果条件允许，可以开启 SSL 加密传输。数据在互联网传输时没有 SSL，相当于明文，容易被中间人截获。

9.3 备份与恢复

备份是数据库运维中最不能省略的一环。PostgreSQL 提供两种备份思路：逻辑备份和物理备份。

逻辑备份使用 pg_dump：

bash复制pg_dump -U shop_user -h localhost -p 5432 shopdb > shopdb.sql

恢复逻辑备份：

bash复制psql -U shop_user -h localhost -p 5432 shopdb < shopdb.sql

逻辑备份的优点是跨版本和跨平台迁移方便，适合中小规模数据。数据量达到 TB 级别时，逻辑备份耗时较长，更适合使用物理备份工具 pg_basebackup 结合 WAL 归档，能提供接近实时的恢复能力。

备份策略的选择要结合 RTO（恢复时间目标）和 RPO（恢复点目标）来决定。核心业务建议做到每日全量备份 + 持续 WAL 归档，同时定期演练恢复流程。备份不是“备份完就完了”，没有恢复验证的备份等于没有备份。

9.4 监控与慢查询追踪

上线 PostgreSQL 之后，监控必须跟上。pg_stat_activity 视图可以查看当前连接会话和正在执行的 SQL：

sql复制SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE state = 'active';

pg_stat_statements 扩展可以统计每个 SQL 的执行次数、总耗时、平均耗时，是排查慢 SQL 的重要入口。

启用方式：

sql复制CREATE EXTENSION pg_stat_statements;

然后查询耗时排序靠前的 SQL：

sql复制SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

定期查看这些统计信息，可以提前发现潜在的性能瓶颈，而不是等系统真正卡住后才去翻日志。

9.5 自动化与 CI/CD 集成建议

开发环境可以使用 Docker Compose 一键启动 PostgreSQL，测试环境可以借助 Flyway 或 Liquibase 管理表结构变更。数据库迁移脚本应该提交到代码仓库，由 CI/CD 流程统一执行。每次变更前先在测试环境跑一遍脚本，再进入生产发布流程，避免在线上直接手动修改表结构。

生产环境执行 DDL 时，务必评估锁表风险。给大表添加字段、重建索引、修改字段类型等操作，在数据量大的表上可能长时间持有锁，阻塞正常业务。推荐使用 pg_repack 或锁等待时间参数控制风险，并在业务低峰期执行变更。

PostgreSQL 作为一个成熟的开源数据库，完全有能力支撑从个人博客到企业核心交易的各类场景。它不像某些商业数据库那样需要昂贵授权，也不像一些轻量数据库那样在高复杂度场景下力不从心。学会 PostgreSQL，意味着你同时拥有了关系型数据库的严谨和文档数据库、分析型数据库的灵活。如果你已经顺利完成了安装和基础操作，下一步建议重点研究执行计划分析、分区表设计和备份恢复演练这三块内容。实战中遇到的每个报错和慢查询，都是提升数据库能力的宝贵素材。

PostgreSQL从入门到实战：安装、高阶SQL与生产运维指南