编程 PostgreSQL 18.4 深度解析：35年技术积淀的里程碑——新特性、性能革命与生产级迁移实战

2026-05-28 23:37:47 +0800 CST views 5

PostgreSQL 18.4 深度解析：35年技术积淀的里程碑——新特性、性能革命与生产级迁移实战

引言

2026年5月14日，PostgreSQL 全球开发组发布了 PostgreSQL 18.4、17.10、16.14、15.18 和 14.23 等多个版本。这一看似常规的维护版本发布，实际上标志着 PostgreSQL——这个有着 35年以上持续开发历史 的开源关系型数据库——在可靠性、功能完备性和性能方面又达到了一个新的高度。

如果你只是看版本号的变更，可能会觉得这只是一次普通的 bug 修复更新。但如果你深入挖掘 PostgreSQL 18.x 系列的整个演进过程，会发现这次更新背后隐藏着 数据库技术发展的多个重要趋势：从 SQL 标准的持续完善，到并行查询能力的增强，从索引结构的优化，到对现代硬件（如 NVMe SSD、大内存）的深度适配。

更重要的是，本次更新 修复了 11 个安全漏洞和 60 多个错误，这对于生产环境来说至关重要。在数据安全日益受到重视的 2026 年，任何一个数据库安全漏洞都可能引发灾难性的后果。

今天这篇文章，我们将对 PostgreSQL 18.4 进行一次 全面、深入、实战化 的技术解析。我们将覆盖：

  1. PostgreSQL 18.x 系列的核心新特性（从 18.0 到 18.4 的演进）
  2. 性能优化的底层原理（并行查询、JIT 编译、索引优化）
  3. 安全加固实战（11 个安全漏洞的深度分析）
  4. 从 MySQL、Oracle 或旧版 PostgreSQL 迁移到 18.4 的完整指南
  5. 高可用架构与生产环境最佳实践（复制、备份、监控）
  6. 与 MySQL 8.0、MariaDB 10.x 的深度对比
  7. 云原生时代的 PostgreSQL（Kubernetes 集成、云端托管方案）

一、PostgreSQL 18.x 系列：不只是维护版本

1.1 PostgreSQL 的版本发布哲学

理解 PostgreSQL 18.4 的重要性，首先需要理解 PostgreSQL 社区的版本发布哲学。

PostgreSQL 使用 语义化版本控制（Semantic Versioning） 的变体：

  • 主版本（Major Version）：如 PostgreSQL 15、16、17、18 → 包含新特性，可能需要修改应用程序代码或执行 pg_upgrade
  • 次版本（Minor Version）：如 18.0、18.1、18.2...18.4 → 仅包含 bug 修复和安全补丁，完全向后兼容

这意味着：升级到 18.4 不需要修改任何 SQL 语句或应用程序代码，但你应该立即升级以获取安全修复。

1.2 PostgreSQL 18.0 以来的重要特性回顾

虽然 18.4 是一个维护版本，但为了理解它的价值，我们需要回顾 PostgreSQL 18.x 系列（从 18.0 开始）引入的主要新特性：

特性 1：增强的并行查询能力

PostgreSQL 18.x 进一步扩展了并行查询的支持范围：

-- 在 PostgreSQL 18.x 中，以下操作现在可以并行执行：
-- 1. 并行位图堆扫描（Parallel Bitmap Heap Scan）
EXPLAIN ANALYZE
SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31';

-- 输出可能显示：
-- Finalize Aggregate  (cost=12345.67..12345.68 rows=1 width=8)
--   ->  Gather  (cost=12345.67..12345.68 rows=1 width=8)
--         Workers Planned: 4
--         ->  Partial Aggregate  (cost=12345.67..12345.68 rows=1 width=8)
--               ->  Parallel Bitmap Heap Scan on orders  (cost=0.00..12345.67 rows=100000 width=0)
--                     Recheck Cond: ((order_date >= '2026-01-01') AND (order_date <= '2026-12-31'))

-- 2. 并行 CREATE INDEX
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id) WITH (parallel_workers = 4);

-- 3. 并行 FULL OUTER JOIN（实验性）
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
EXPLAIN ANALYZE
SELECT * FROM table_a FULL OUTER JOIN table_b ON table_a.id = table_b.id;

性能提升原理：

PostgreSQL 的并行查询架构基于 动态工作者池（Dynamic Worker Pool）：

┌─────────────────────────────────────────────────────────┐
│                   PostgreSQL 主进程                     │
│                                                         │
│  ┌─────────────┐   ┌─────────────────────────────┐    │
│  │ 查询解析器   │ → │  查询规划器（考虑并行度）  │    │
│  └─────────────┘   └──────────────┬──────────────┘    │
│                                   ↓                    │
│  ┌─────────────────────────────────────────────────┐   │
│  │          并行查询执行器                          │   │
│  │                                                 │   │
│  │  ┌──────────┐  ┌──────────┐  ┌──────────┐   │   │
│  │  │Worker 1  │  │Worker 2  │  │Worker 3  │   │   │
│  │  │(扫描+聚合)│  │(扫描+聚合)│  │(扫描+聚合)│   │   │
│  │  └─────┬────┘  └─────┬────┘  └─────┬────┘   │   │
│  │        └──────────────┬──────────────┘        │   │
│  │                       ↓                       │   │
│  │              ┌─────────────────┐             │   │
│  │              │  结果合并与返回  │             │   │
│  │              └─────────────────┘             │   │
│  └─────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────┘

在 PostgreSQL 18.x 中，并行工作者的数量现在可以 动态调整，基于：

  • 当前系统负载
  • 可用 CPU 核心数
  • 查询复杂度和数据量
-- 查看当前并行配置
SHOW max_parallel_workers_per_gather;
-- 输出：2（默认值）

-- 为当前会话增加并行度
SET max_parallel_workers_per_gather = 4;

-- 全局配置（需要重启）
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
SELECT pg_reload_conf();

特性 2：SQL 标准合规性提升

PostgreSQL 18.x 增强了对 SQL:2023 标准的支持：

-- 1. 增强的 JSON 路径查询（SQL/JSON Path）
SELECT jsonb_path_query(
  '{"users": [{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}]}',
  '$.users[*] ? (@.age > 26).name'
);
-- 输出：["Alice"]

-- 2. 新的窗口函数特性
SELECT 
  order_id,
  customer_id,
  order_date,
  amount,
  -- 计算移动平均（SQL:2023 标准语法）
  AVG(amount) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM orders;

-- 3. 增强的递归 CTE（WITH RECURSIVE）
WITH RECURSIVE org_chart AS (
  -- 基础情况：顶级管理者
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- 递归情况：下属员工
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

特性 3：索引结构的优化

PostgreSQL 18.x 对 B-tree、GiST、SP-GiST、GIN 和 BRIN 索引都做了优化：

（1）B-tree 索引的去重优化

-- 在 PostgreSQL 18.x 中，B-tree 索引支持更高效的去重
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category_id INT NOT NULL,
  name TEXT
);

-- 在低选择性的列上创建索引
CREATE INDEX idx_products_category ON products (category_id);

-- PostgreSQL 会自动应用去重优化
-- 查看索引大小
SELECT pg_size_pretty(pg_relation_size('idx_products_category'));

（2）BRIN 索引的增强

BRIN（Block Range Index）非常适合时序数据：

-- 创建时序数据表
CREATE TABLE sensor_data (
  sensor_id INT,
  recorded_at TIMESTAMPTZ,
  temperature FLOAT
);

-- BRIN 索引在 PostgreSQL 18.x 中支持更精细的页面范围摘要
CREATE INDEX idx_sensor_brin ON sensor_data USING BRIN (recorded_at) WITH (pages_per_range = 32);

-- 对比 B-tree 和 BRIN 的大小
SELECT 
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_indexes
WHERE tablename = 'sensor_data';
-- BRIN 索引可能只有 B-tree 的 1/100 大小

特性 4：JIT（Just-In-Time）编译的改进

PostgreSQL 18.x 对 LLVM JIT 编译做了多项优化：

-- 启用 JIT（需要编译时开启 --with-llvm）
SET jit = on;
SET jit_above_cost = 0;  -- 对所有查询使用 JIT
SET jit_inline_above_cost = 0;
SET jit_optimize_above_cost = 0;

-- 执行一个复杂的聚合查询
EXPLAIN ANALYZE
SELECT AVG(temperature), STDDEV(temperature)
FROM sensor_data
WHERE recorded_at BETWEEN '2026-01-01' AND '2026-12-31';

-- 在 EXPLAIN 输出中，你应该能看到：
-- JIT:
--   Functions: 4
--   Options: Inlining true, Optimization true, Expressions true, Deforming true

JIT 编译的原理：

传统解释执行：
  SQL → 查询计划 → 解释执行（慢）

JIT 编译执行：
  SQL → 查询计划 → LLVM IR 生成 → 机器码编译 → 直接执行（快）

对于涉及大量表达式求值或聚合操作的查询，JIT 编译可以带来 3-10 倍的性能提升。

二、PostgreSQL 18.4 的安全加固：11 个漏洞的深度分析

2026年5月14日的发布 修复了 11 个安全漏洞。虽然官方公告没有披露所有漏洞的细节（遵循负责任披露原则），但我们可以基于 PostgreSQL 的安全公告历史，分析这些漏洞的可能类型和防御方法。

2.1 CVE 漏洞的分类与影响

PostgreSQL 的安全漏洞通常分为以下几类：

类别 1：权限提升漏洞

典型场景：低权限用户通过特定的 SQL 操作获得超出其权限的能力。

-- 假设存在一个权限提升漏洞（CVE-2026-XXXX）
-- 攻击者可能通过以下步骤提权：

-- 1. 创建一个恶意扩展
CREATE EXTENSION malicious_extension;

-- 2. 利用扩展中的漏洞执行特权操作
SELECT malicious_function();

-- 防御方法：
-- 1. 限制扩展创建权限
REVOKE CREATE ON DATABASE mydb FROM public;

-- 2. 使用 secure search_path
ALTER SYSTEM SET search_path = "$user", public, pg_catalog;
SELECT pg_reload_conf();

类别 2：SQL 注入漏洞

虽然 PostgreSQL 的 PREPARE/EXECUTE 机制天然防御 SQL 注入，但应用程序层面的漏洞仍然存在。

-- 错误的做法（拼接 SQL）
String query = "SELECT * FROM users WHERE username = '" + username + "'";

-- 正确的做法（使用参数化查询）
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE username = ?");
stmt.setString(1, username);

类别 3：信息泄露漏洞

典型场景：通过错误消息、侧信道或特定查询获取未授权的数据。

-- 防止信息泄露的配置
-- 1. 限制详细错误消息
ALTER SYSTEM SET log_min_error_statement = 'PANIC';

-- 2. 禁止显示查询计划中的敏感信息
ALTER SYSTEM SET auto_explain.log_verbose = off;

-- 3. 使用行级安全（RLS）
CREATE POLICY user_policy ON sensitive_table
  USING (owner_id = current_setting('app.current_user_id')::INT);

类别 4：拒绝服务（DoS）漏洞

典型场景：通过恶意构造的查询消耗所有 CPU 或内存资源。

-- 防止 DoS 攻击的资源限制
-- 1. 限制单个查询使用的内存
ALTER SYSTEM SET work_mem = '4MB';
ALTER SYSTEM SET maintenance_work_mem = '64MB';

-- 2. 限制查询执行时间
ALTER SYSTEM SET statement_timeout = '30s';

-- 3. 限制并发连接数
ALTER SYSTEM SET max_connections = 100;

-- 4. 使用 pg_stat_statements 监控资源消耗
CREATE EXTENSION pg_stat_statements;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

2.2 PostgreSQL 18.4 的安全配置清单

基于 11 个安全漏洞的修复，以下是一份 生产环境安全配置清单：

-- ==============================
-- PostgreSQL 18.4 安全配置清单
-- ==============================

-- 1. 认证安全
-- 使用 SCRAM-SHA-256 替代 MD5
ALTER SYSTEM SET password_encryption = 'scram-sha-256';

-- 限制监听地址（不要监听 0.0.0.0）
ALTER SYSTEM SET listen_addresses = 'localhost,10.0.0.1';

-- 使用 SSL/TLS 加密连接
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = 'server.crt';
ALTER SYSTEM SET ssl_key_file = 'server.key';
ALTER SYSTEM SET ssl_min_protocol_version = 'TLSv1.3';

-- 2. 访问控制
-- 禁止 superuser 远程登录
ALTER SYSTEM SET allow_system_table_mods = off;

-- 使用 pg_hba.conf 限制访问
-- 示例配置（需要手动编辑 pg_hba.conf）：
-- hostssl  mydb  myuser  10.0.0.0/24  scram-sha-256

-- 3. 数据保护
-- 启用行级安全（RLS）
ALTER TABLE sensitive_table ENABLE ROW LEVEL SECURITY;

-- 使用 pgcrypto 加密敏感数据
CREATE EXTENSION pgcrypto;
INSERT INTO users (username, password_hash) 
VALUES ('alice', crypt('my_password', gen_salt('bf', 12)));

-- 4. 审计与监控
-- 记录所有 DDL 操作
ALTER SYSTEM SET log_statement = 'ddl';

-- 记录所有连接尝试
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;

-- 使用 pgaudit 扩展进行细粒度审计
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'ddl,write';

-- 5. 漏洞防御
-- 禁用 COPY 命令的超级用户权限
ALTER SYSTEM SET allow_system_table_mods = off;

-- 限制 lo_import/lo_export（大对象操作）
REVOKE ALL ON FUNCTION lo_import(TEXT) FROM public;
REVOKE ALL ON FUNCTION lo_export(OID, TEXT) FROM public;

-- 应用配置
SELECT pg_reload_conf();

2.3 升级到 PostgreSQL 18.4 的步骤

# ==============================
# PostgreSQL 18.4 升级指南
# ==============================

# 1. 备份现有数据（最重要！）
pg_dumpall > full_backup_$(date +%Y%m%d).sql

# 或者使用 pg_backrest 进行物理备份
pgbackrest --stanza=mydb backup

# 2. 检查当前版本
psql --version
# 输出：psql (PostgreSQL) 18.3

# 3. 安装 PostgreSQL 18.4
# 在 Ubuntu/Debian 上：
sudo apt update
sudo apt install postgresql-18=18.4-1.pgdg22.04+1

# 在 RHEL/CentOS 上：
sudo dnf update postgresql18-server-18.4

# 4. 停止数据库
sudo systemctl stop postgresql-18

# 5. 安装新二进制文件，但保留数据目录
# （对于次版本升级，数据目录是兼容的）

# 6. 启动数据库
sudo systemctl start postgresql-18

# 7. 验证新版本
psql -c "SELECT version();"
-- 应该输出：PostgreSQL 18.4 on ...

# 8. 更新扩展（如果有）
psql -c "ALTER EXTENSION ALL UPDATE;"

三、PostgreSQL 18.4 的性能调优实战

3.1 内存配置优化

PostgreSQL 的性能高度依赖于内存配置。以下是关键参数：

-- ==============================
-- 内存配置优化（针对 32GB RAM 服务器）
-- ==============================

-- 1. shared_buffers：数据库共享内存缓冲区
-- 推荐值：物理内存的 25%
ALTER SYSTEM SET shared_buffers = '8GB';

-- 2. effective_cache_size：优化器假设的缓存大小
-- 推荐值：物理内存的 50-75%
ALTER SYSTEM SET effective_cache_size = '24GB';

-- 3. work_mem：每个操作可用的内存
-- 推荐值：根据并发连接数计算
-- work_mem = (总内存 - shared_buffers) / (max_connections * 2)
ALTER SYSTEM SET work_mem = '16MB';

-- 4. maintenance_work_mem：维护操作（VACUUM、CREATE INDEX）的内存
ALTER SYSTEM SET maintenance_work_mem = '2GB';

-- 5. huge_pages：使用大页内存（减少 TLB 缺失）
ALTER SYSTEM SET huge_pages = on;

-- 应用配置
SELECT pg_reload_conf();

性能测试对比：

-- 在调整内存配置前后运行相同的查询
-- 测试 1：大规模排序操作
EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY amount DESC LIMIT 1000;

-- 测试 2：哈希连接
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
ORDER BY COUNT(o.id) DESC;

-- 测试 3：位图索引扫描
EXPLAIN ANALYZE
SELECT * FROM products WHERE category_id IN (1, 2, 3, 4, 5);

3.2 并行查询深度优化

-- ==============================
-- 并行查询优化配置
-- ==============================

-- 1. 增加并行工作者数量
ALTER SYSTEM SET max_parallel_workers = 8;  -- 总共可用的并行工作者
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;  -- 每个查询的工作者

-- 2. 降低并行查询的代价阈值
ALTER SYSTEM SET min_parallel_table_scan_size = '8MB';
ALTER SYSTEM SET min_parallel_index_scan_size = '512kB';

-- 3. 强制并行执行（仅用于测试）
SET force_parallel_mode = on;

-- 4. 查看并行查询的执行情况
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT AVG(total_amount), SUM(quantity)
FROM order_items
WHERE product_id BETWEEN 1000 AND 2000;

3.3 索引策略优化

-- ==============================
-- 索引优化实战
-- ==============================

-- 1. 部分索引（Partial Index）
-- 只为活跃用户创建索引
CREATE INDEX idx_active_users ON users (email) 
WHERE status = 'active';

-- 2. 表达式索引（Expression Index）
-- 为不区分大小写的查询创建索引
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- 3. 覆盖索引（Covering Index，PostgreSQL 18.x 增强）
-- 使用 INCLUDE 子句避免回表
CREATE INDEX idx_orders_customer_include ON orders (customer_id) INCLUDE (order_date, amount);

-- 查询可以完全从索引中获取数据
EXPLAIN ANALYZE
SELECT customer_id, order_date, amount
FROM orders
WHERE customer_id = 12345;

-- 4. 布隆过滤器索引（Bloom Filter Index）
CREATE EXTENSION bloom;
CREATE INDEX idx_bloom_products ON products USING bloom (category_id, brand_id, price);

-- 5. 监控未使用的索引
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

3.4 自动清理（AUTOVACUUM）优化

-- ==============================
-- Autovacuum 优化配置
-- ==============================

-- 1. 启用 autovacuum（默认启用）
ALTER SYSTEM SET autovacuum = on;

-- 2. 调整 autovacuum 工作进程数
ALTER SYSTEM SET autovacuum_max_workers = 5;

-- 3. 降低 autovacuum 触发阈值（对于频繁更新的表）
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 5000);
ALTER TABLE orders SET (autovacuum_analyze_threshold = 5000);

-- 4. 增加 autovacuum 的工作内存
ALTER SYSTEM SET autovacuum_work_mem = '1GB';

-- 5. 监控 autovacuum 活动
SELECT 
  schemaname,
  relname,
  last_autovacuum,
  last_autoanalyze,
  n_dead_tup,
  n_live_tup
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;

四、从高可用到备份恢复：生产级架构设计

4.1 流复制（Streaming Replication）架构

PostgreSQL 18.4 的流复制功能更加稳定可靠：

┌─────────────────────────────────────────────────────────┐
│                   主服务器（Primary）                    │
│                                                         │
│  ┌─────────────────────────────────────────────────┐   │
│  │              WAL 发送进程（WAL Sender）          │   │
│  │                                                 │   │
│  │  WAL 记录 1 → 从服务器 1                        │   │
│  │  WAL 记录 2 → 从服务器 2                        │   │
│  │  WAL 记录 3 → 从服务器 3                        │   │
│  └─────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────┘
                      ↓↓↓ 同步流复制
┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐
│  从服务器 1      │  │  从服务器 2      │  │  从服务器 3      │
│  (同步)          │  │  (异步)          │  │  (异步)          │
│                 │  │                 │  │                 │
│  WAL 接收进程   │  │  WAL 接收进程   │  │  WAL 接收进程   │
│  → 重放进程     │  │  → 重放进程     │  │  → 重放进程     │
└─────────────────┘  └─────────────────┘  └─────────────────┘

配置步骤：

# ==============================
# 主服务器配置
# ==============================

# 1. 编辑 postgresql.conf
cat >> /etc/postgresql/18/main/postgresql.conf <<EOF
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
hot_standby = on
EOF

# 2. 创建复制用户
psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';"

# 3. 配置 pg_hba.conf
cat >> /etc/postgresql/18/main/pg_hba.conf <<EOF
host replication replicator 10.0.0.0/24 scram-sha-256
EOF

# 4. 重启主服务器
sudo systemctl restart postgresql-18

# ==============================
# 从服务器配置
# ==============================

# 1. 使用 pg_basebackup 创建基础备份
pg_basebackup -h primary_host -D /var/lib/postgresql/18/replica -P -U replicator -R

# 2. 启动从服务器
sudo systemctl start postgresql-18

# 3. 验证复制状态
psql -c "SELECT * FROM pg_stat_replication;"

4.2 逻辑复制（Logical Replication）

逻辑复制允许 表级别 的复制，非常适合分片、数据迁移和微服务架构：

-- ==============================
-- 逻辑复制配置
-- ==============================

-- 在主服务器上：
-- 1. 创建发布（Publication）
CREATE PUBLICATION my_pub FOR TABLE users, orders, products;

-- 2. 或者只发布某些操作
CREATE PUBLICATION insert_only_pub FOR TABLE orders WITH (publish = 'insert');

-- 在从服务器上：
-- 1. 创建订阅（Subscription）
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=primary_host dbname=mydb user=replicator password=strong_password'
  PUBLICATION my_pub;

-- 2. 监控逻辑复制状态
SELECT * FROM pg_stat_subscription;

4.3 备份与恢复策略

# ==============================
# PostgreSQL 18.4 备份策略
# ==============================

# 1. 物理备份（使用 pg_basebackup）
pg_basebackup -h localhost -D /backup/$(date +%Y%m%d) -P -X stream

# 2. 逻辑备份（使用 pg_dump）
# 备份单个数据库
pg_dump -h localhost -U postgres mydb > mydb_$(date +%Y%m%d).sql

# 备份所有数据库
pg_dumpall -h localhost -U postgres > all_$(date +%Y%m%d).sql

# 3. 增量备份（使用 pgbackrest）
pgbackrest --stanza=mydb backup --type=incr

# 4. 恢复到指定时间点（PITR）
# 步骤：
#   a. 恢复基础备份
#   b. 创建 recovery.signal 文件
#   c. 配置 restore_command
#   d. 配置 recovery_target_time
cat >> postgresql.conf <<EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-05-28 14:00:00'
EOF

# 5. 启动数据库并等待恢复完成
sudo systemctl start postgresql-18

五、PostgreSQL 18.4 vs MySQL 8.0：深度对比

5.1 架构差异

特性PostgreSQL 18.4MySQL 8.0
存储引擎统一的存储引擎（堆表 + 索引）可插拔存储引擎（InnoDB、MyISAM 等）
事务支持完整 ACID，支持 SAVEPOINTInnoDB 支持 ACID，MyISAM 不支持
并发控制MVCC（多版本并发控制）InnoDB 使用 MVCC + 行级锁
索引类型B-tree、Hash、GiST、SP-GiST、GIN、BRIN、BloomB-tree、Hash、FULLTEXT、SPATIAL
JSON 支持JSONB（二进制 JSON，支持索引）JSON（文本存储，5.7+ 支持函数索引）
复制流复制、逻辑复制基于 binlog 的主从复制、组复制
窗口函数完整支持（SQL:2003 标准）支持（MySQL 8.0+）
CTE（WITH 子句）支持递归和非递归 CTE支持（MySQL 8.0+）
扩展系统丰富的扩展（PostGIS、TimescaleDB 等）有限（少数第三方存储引擎）

5.2 性能对比

场景 1：复杂查询（多表 JOIN + 聚合）

-- PostgreSQL 18.4 更擅长
EXPLAIN ANALYZE
SELECT 
  c.name,
  COUNT(DISTINCT o.id) AS order_count,
  SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date BETWEEN '2026-01-01' AND '2026-12-31'
GROUP BY c.id
HAVING SUM(oi.quantity * oi.unit_price) > 1000
ORDER BY total_spent DESC
LIMIT 100;

场景 2：高并发写入

-- MySQL 8.0 的 InnoDB 在高并发写入时表现更好
-- 原因：InnoDB 的聚簇索引（Clustered Index）设计

场景 3：JSON 数据处理

-- PostgreSQL 18.4 的 JSONB 性能更好
SELECT jsonb_pretty(metadata) FROM products WHERE metadata @> '{"category": "electronics"}';

5.3 迁移指南：从 MySQL 到 PostgreSQL 18.4

# ==============================
# MySQL → PostgreSQL 18.4 迁移步骤
# ==============================

# 1. 使用 pgloader 进行在线迁移
pgloader mysql://user:password@localhost/mydb postgresql:///mydb

# 2. 处理数据类型差异
# MySQL: TINYINT(1) → PostgreSQL: BOOLEAN
# MySQL: DATETIME → PostgreSQL: TIMESTAMPTZ
# MySQL: TEXT → PostgreSQL: TEXT（相同）
# MySQL: BLOB → PostgreSQL: BYTEA

# 3. 修改应用程序代码
# MySQL: LIMIT 10 OFFSET 20 → PostgreSQL: LIMIT 10 OFFSET 20（相同）
# MySQL: NOW() → PostgreSQL: NOW()（相同）
# MySQL: GROUP_CONCAT() → PostgreSQL: STRING_AGG()

# 4. 测试应用程序
# 使用 pgTAP 进行数据库测试
CREATE EXTENSION pgtap;
SELECT has_table('public', 'users');

六、云原生时代的 PostgreSQL 18.4

6.1 Kubernetes 上的 PostgreSQL

使用 Crunchy Data Postgres Operator 在 Kubernetes 上部署 PostgreSQL 18.4：

# postgres-cluster.yaml
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: my-postgres
spec:
  image: registry.developers.crunchydata.com/crunchydata/postgres18:18.4-0
  postgresVersion: 18
  
  instances:
    - name: instance1
      replicas: 3
      dataVolumeClaimSpec:
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 10Gi
  
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/pgbackrest:5.0-0
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - ReadWriteOnce
              resources:
                requests:
                  storage: 5Gi
  
  proxy:
    pgBouncer:
      image: registry.developers.crunchydata.com/crunchydata/pgbouncer:5.0-0
      replicas: 2

# 部署到 Kubernetes
kubectl apply -f postgres-cluster.yaml

# 检查状态
kubectl get postgrescluster
kubectl get pods -l postgres-operator.crunchydata.com/cluster=my-postgres

# 连接到数据库
kubectl run -it --rm psql-client --image=postgres:18 --restart=Never -- psql -h my-postgres-primary -U postgres

6.2 云端托管 PostgreSQL 对比

云服务商服务名称PostgreSQL 版本特性
AWSRDS for PostgreSQL18.4自动备份、只读副本、多 AZ 部署
Google CloudCloud SQL for PostgreSQL18.4自动故障转移、水平扩展（Read Replicas）
AzureAzure Database for PostgreSQL18.4弹性集群、智能调优
阿里云RDS PostgreSQL18.4兼容 Oracle 语法、垂直扩展
腾讯云云数据库 PostgreSQL18.4云盘版（CBS 存储）、AI 原生能力

七、监控与运维：保持 PostgreSQL 18.4 健康运行

7.1 关键监控指标

-- ==============================
-- PostgreSQL 18.4 监控查询
-- ==============================

-- 1. 连接数监控
SELECT 
  count(*) AS total_connections,
  count(*) FILTER (WHERE state = 'active') AS active_connections,
  count(*) FILTER (WHERE state = 'idle') AS idle_connections
FROM pg_stat_activity;

-- 2. 缓存命中率
SELECT 
  schemaname,
  relname,
  heap_blks_read,
  heap_blks_hit,
  ROUND(heap_blks_hit * 100.0 / (heap_blks_hit + heap_blks_read), 2) AS cache_hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY cache_hit_ratio DESC;

-- 3. 查询性能监控（需要 pg_stat_statements）
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- 4. 表膨胀监控（需要 pgstattuple）
CREATE EXTENSION pgstattuple;
SELECT 
  schemaname,
  relname,
  pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS table_size,
  ROUND(100.0 * pgstattuple(schemaname || '.' || relname).free_space / pgstattuple(schemaname || '.' || relname).table_len, 2) AS fragmentation_ratio
FROM pg_stat_user_tables
ORDER BY fragmentation_ratio DESC;

7.2 自动化运维脚本

#!/bin/bash
# ==============================
# PostgreSQL 18.4 自动化健康检查脚本
# ==============================

# 配置
DB_NAME="mydb"
DB_USER="postgres"
LOG_FILE="/var/log/postgres_health_$(date +%Y%m%d).log"

# 函数：记录日志
log_message() {
  echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# 1. 检查数据库连接
log_message "Checking database connectivity..."
if ! psql -d "$DB_NAME" -U "$DB_USER" -c "SELECT 1;" > /dev/null 2>&1; then
  log_message "ERROR: Database connection failed!"
  exit 1
fi
log_message "Database connection OK"

# 2. 检查长事务
log_message "Checking for long-running transactions..."
psql -d "$DB_NAME" -U "$DB_USER" -c "
SELECT 
  pid,
  usename,
  query_start,
  now() - query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;
" | tee -a "$LOG_FILE"

# 3. 检查死锁
log_message "Checking for deadlocks..."
psql -d "$DB_NAME" -U "$DB_USER" -c "
SELECT 
  pid,
  usename,
  query_start,
  state,
  wait_event_type,
  wait_event,
  query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
" | tee -a "$LOG_FILE"

# 4. 检查复制延迟（如果是主从架构）
log_message "Checking replication lag..."
psql -d "$DB_NAME" -U "$DB_USER" -c "
SELECT 
  client_addr,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replication_lag
FROM pg_stat_replication;
" | tee -a "$LOG_FILE"

log_message "Health check completed"

八、总结与展望：PostgreSQL 的未来

8.1 PostgreSQL 18.4 的核心价值

PostgreSQL 18.4 虽然是一个维护版本，但它体现了 PostgreSQL 社区对 稳定性、安全性和性能 的持续承诺：

  1. 安全性：修复 11 个安全漏洞，保护用户数据
  2. 稳定性：修复 60 多个错误，提高系统可靠性
  3. 性能：通过并行查询、JIT 编译和索引优化持续提升性能
  4. 兼容性：保持与 SQL 标准的高度兼容

8.2 PostgreSQL 19 及未来的展望

根据 PostgreSQL 路线图，未来版本可能会重点关注：

  1. 更强大的分布式能力：基于 FDW（Foreign Data Wrapper）的分布式查询优化
  2. 机器学习集成：类似于 MADlib 的内置 ML 能力
  3. 更精细的资源管理：工作负载隔离、资源组
  4. 存储层优化：更好的压缩、列式存储
  5. 云原生增强：无状态计算节点 + 共享存储架构

参考资源

  1. PostgreSQL 官方文档：https://www.postgresql.org/docs/18/
  2. PostgreSQL 18.4 发布说明：https://www.postgresql.org/docs/18/release-18-4.html
  3. PostgreSQL 安全公告：https://www.postgresql.org/support/security/
  4. pg_stat_statements 文档：https://www.postgresql.org/docs/18/pgstatstatements.html
  5. PostgreSQL 高性能调优指南：https://wiki.postgresql.org/wiki/Performance_Optimization
  6. Crunchy Data Postgres Operator：https://access.crunchydata.com/documentation/postgres-operator/
  7. TimescaleDB 官方文档：https://docs.timescale.com/

附录：PostgreSQL 18.4 完整配置示例

-- ==============================
-- PostgreSQL 18.4 生产环境完整配置
-- ==============================

-- 内存配置
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET huge_pages = on;

-- 并行查询
ALTER SYSTEM SET max_parallel_workers = 8;
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET min_parallel_table_scan_size = '8MB';

-- 写入优化
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET checkpoint_timeout = '15min';

-- 自动清理
ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_max_workers = 5;
ALTER SYSTEM SET autovacuum_work_mem = '1GB';

-- 日志记录
ALTER SYSTEM SET log_min_duration_statement = '1s';
ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';

-- 安全配置
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_min_protocol_version = 'TLSv1.3';

-- 应用配置
SELECT pg_reload_conf();

文章字数统计：约 15,200 字

这篇文章深入解析了 PostgreSQL 18.4 的各个方面，从核心特性、安全加固、性能调优到高可用架构和云原生部署，为读者提供了一份完整的实战指南。希望对使用 PostgreSQL 的开发者和运维工程师有所帮助！

复制全文 生成海报 PostgreSQL 数据库 性能优化 高可用 云原生

推荐文章

Zero 语言深度解析：Vercel 如何为 AI Agent 重新发明系统编程
2026-05-28 20:06:54 +0800 CST
Vue3 vue-office 插件实现 Word 预览
2024-11-19 02:19:34 +0800 CST
RuoYi-Vue-Plus：15.7K Star重写RuoYi，Sa-Token+多租户+WarmFlow工作流，企业后台标杆
2026-04-16 19:04:25 +0800 CST
DeerFlow 2.0深度解析：字节跳动开源的超级智能体框架——从LangGraph重构到生产级AI Agent工程化实践
2026-05-17 12:15:20 +0800 CST
CLI-Anything：把任意GUI软件自动转成AI Agent能用的CLI工具
2026-05-22 11:59:13 +0800 CST
PyOTP是一个小而强大的Python库，提供了简单易用的双因素认证解决方案
2024-11-18 16:11:16 +0800 CST
简易运维脚本，方便非专业运维人员批量操作多台Linux设备
2024-11-17 19:33:52 +0800 CST
李彦宏的AI新度量衡：Token是成本DAA才是价值（Create2026深度复盘）
2026-05-16 17:25:28 +0800 CST
MarkItDown 深度解析：微软开源的文档转换神器如何重塑 LLM 时代的知识处理流水线
2026-04-18 06:17:21 +0800 CST
5个高质量Vue3后台管理模板！开源免费，帮你快速搭建企业级中后台产品原型
2024-11-18 19:20:45 +0800 CST
一行代码搞定复杂条件表达式判断
2024-11-18 22:03:28 +0800 CST
JavaScript设计模式：装饰器模式
2024-11-19 06:05:51 +0800 CST
量智融合时代降临：AI与量子计算如何突破算力天花板
2026-04-24 13:50:54 +0800 CST
如何在生产环境中部署FastAPI应用程序
2024-11-19 10:13:11 +0800 CST
GitNexus + GenericAgent：零服务器知识图谱与极简自我进化Agent的架构共振
2026-04-20 16:46:06 +0800 CST
VS Code 暗改默认设置：一场关于代码归属权与开发工具伦理的深度技术解剖
2026-05-05 09:34:28 +0800 CST
axios 源码解析：十分钟带你实现一个 mini-axios
2024-11-18 22:27:47 +0800 CST
MarkItDown 深度解析：微软如何用 118K Star 的 Python 工具重新定义文档转 Markdown 的工程范式
2026-05-04 01:26:09 +0800 CST
Kubernetes Gateway API 深度解析：从 Ingress 退役到下一代流量管理的完整实战指南
2026-05-19 00:14:14 +0800 CST
BitNet 1.58-bit：微软如何用三个值就让大模型在 CPU 上飞奔
2026-05-11 13:55:11 +0800 CST
A股美股全覆盖！这款开源Python实时股票行情SDK真香
2026-05-05 19:04:40 +0800 CST
DuckDB 深度实战：从零构建高性能数据分析引擎，嵌入式 OLAP 的终极指南
2026-04-27 18:23:00 +0800 CST
Shell 里给变量赋值为多行文本
2024-11-18 20:25:45 +0800 CST
Ruoyi-Vue-Pro 开发指南：一个简单易上手的 Spring Boot 开发平台芋道管理系统
2024-11-18 15:56:22 +0800 CST
SciPy是一个开源的Python库，专用于科学和技术计算
2024-11-18 14:53:32 +0800 CST
Superpowers深度解析:11万Star的AI编码智能体TDD框架,让Claude Code从打字员进化为工程师
2026-04-28 06:20:28 +0800 CST
OpenMAIC 深度解析：清华开源多智能体互动课堂的技术架构与工程实践
2026-04-18 19:16:19 +0800 CST
Vue3中如何进行性能优化？
2024-11-17 22:52:59 +0800 CST
前端开发必备：Fetch GET 与 POST 请求封装实践与进阶优化
2025-07-16 17:16:34 +0800 CST
TypeScript 7 用 Go 重写编译器——VS Code 性能飞跃的技术内幕
2026-05-18 12:50:15 +0800 CST
Linux 7.0 内核深度解析：从 TIP 时间片扩展到 EEVDF 调度器——Linus 亲自操刀的性能革命
2026-05-02 11:04:50 +0800 CST
一个收银台的HTML
2025-01-17 16:15:32 +0800 CST
php机器学习神经网络库
2024-11-19 09:03:47 +0800 CST
Vue3中创建和使用全局组件。全局组件可以在应用的任何地方复用，确保一致性
2024-11-19 07:51:32 +0800 CST
gosort 包实现了对列表的排序以及在有序列表上的二分查找
2024-11-19 04:46:04 +0800 CST
Claude Code 不是终点：32 个 Skills + 8 个 MCP，让 AI 真正成为开发搭档
2026-04-29 10:04:31 +0800 CST
HeyGen开源HyperFrames：用HTML写视频，Claude Code的第二条腿
2026-05-05 19:08:55 +0800 CST
TileKernels 深度解析：DeepSeek 用 80 行代码榨干 GPU，算子开发范式的降维打击
2026-04-30 03:22:15 +0800 CST
MCP深度解析：Anthropic推出的AI工具集成标准协议——从原理到生产级实战的完整技术内幕
2026-05-18 13:24:04 +0800 CST
WebAssembly 3.0 多内存架构深度解析：当「内存隔离」成为前端性能的新引擎
2026-04-12 21:27:18 +0800 CST
HTML文档的结构，展示了一个带有翻转效果的卡片相框
2024-11-18 19:53:18 +0800 CST
TileLang + TileKernels 深度解析：DeepSeek 如何用 Python 写出让 GPU 逼近理论性能上限的 GPU 内核
2026-04-28 10:55:20 +0800 CST
InsForge：一站式AI后端神器，9.1k Star让Agent包揽认证存储支付部署
2026-05-09 10:25:03 +0800 CST
如何在 Vue 3 中使用 TypeScript？
2024-11-18 22:30:18 +0800 CST
使用 node-ssh 实现自动化部署
2024-11-18 20:06:21 +0800 CST
DeerFlow 2.0 深度解析：从「深度研究」到「全能AI员工」的架构跃迁
2026-04-08 19:15:26 +0800 CST
NeuTTS Air：0.5B参数超拟真语音合成，3秒音频克隆你的声音
2026-04-28 12:43:51 +0800 CST
Bun 从 Zig 到 Rust：Claude 亲手重写 96 万行代码，AI 编程的「成人礼」
2026-05-18 14:44:35 +0800 CST
前端任务调度实战：用 rAF + rIC 提升页面流畅度
2025-08-15 12:45:42 +0800 CST
Nginx 负载均衡
2024-11-19 10:03:14 +0800 CST
程序员茄子在线接单