22、PostgreSQL扩展、性能调优及版本升级指南

脸先着地天使

于 2025-07-19 14:05:00 发布

阅读量110

点赞数

CC 4.0 BY-SA版权

分类专栏： PostgreSQL 13实战指南：从入门到精通文章标签： PostgreSQL plprofiler pg_stat_statements

本文链接：https://blog.youkuaiyun.com/jwt8token/article/details/149548005

PostgreSQL 13实战指南：从入门到精通专栏收录该内容

25 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

PostgreSQL扩展、性能调优及版本升级指南

1. plprofiler扩展安装与使用

1.1 准备工作

要构建 plprofiler 扩展，需要安装PostgreSQL开发包。在不同操作系统上安装先决条件的命令如下：
- CentOS/Red Hat ：

$(root) yum install postgresql13-server postgresql13-devel postgresql13-contrib -y

Ubuntu/Debian ：

$ sudo apt install postgresql-13 postgresql-13-contrib postgresql-server-dev-13 -y

1.2 安装步骤

以下是在CentOS 7.8上安装 plprofiler 的步骤，Ubuntu/Debian系统可能需要做一些修改：
1. 安装依赖 ：

# yum install epel-release
# yum install centos-release-scl
# yum install llvm-toolset-7 llvm-toolset-7-llvm-devel.x86_64

安装编译依赖 ：

# yum install git make gcc unzip -y
# yum install python36 -y
# yum install python3-setuptools -y
# yum install python3-devel
# yum install python3-psycopg2
# yum install pip3 -y
# pip3 install virtualenv

创建虚拟环境 ：

# mkdir -p /projects
# cd /projects
# virtualenv -p /usr/bin/python3.6 pg-tuning-packages
# source pg-tuning-packages/bin/activate

下载并编译 plprofiler ：

# curl -LO https://github.com/bigsql/plprofiler/archive/master.zip
# unzip master.zip
# cd plprofiler-master/
# export PATH=$PATH:/usr/pgsql-13/bin
# make USE_PGXS=1
# make USE_PGXS=1 install

将 plprofiler 作为工具使用 ：

# su - postgres
# cd /projects/plprofiler-master/python-plprofiler
# python3.6 ./setup.py install
# deactivate

在虚拟环境中使用 plprofiler ：

# su - postgres
$ source /projects/pg-tuning-packages/bin/activate
$ plprofiler --help

启用全局级别的分析 ：

migops=# alter system set shared_preload_libraries = 'plprofiler';
 ALTER SYSTEM

$ pg_ctl -D /data01/planning/pgdata restart -mf

验证修改：

migops=# show shared_preload_libraries ;
 shared_preload_libraries
 --------------------------
 plprofiler
 (1 row)

在数据库中创建 plprofiler 扩展 ：

$ psql -d migops -c "create extension plprofiler"
 CREATE EXTENSION

1.3 分析操作

会话级分析 ：

$ plprofiler run --command "SELECT tpcb(1, 2, 3, -42)" -d migops --output tpcb-test1.html

全局级分析 ：

$ plprofiler monitor --interval=10 --duration=60
$ plprofiler report --from-shared --title=globalreport --output=globalreport.html
$ plprofiler reset

2. 使用pg_stat_statements捕获需要调优的语句

2.1 准备工作

pg_stat_statements 扩展包含在PostgreSQL的 contrib 模块中，需要安装相应的包：
- Ubuntu ： postgresql-contrib-13
- CentOS/Red Hat ： postgresql13-contrib

2.2 操作步骤

在需要调优的数据库中创建扩展 ：

$ psql -d migops -c "CREATE EXTENSION pg_stat_statements"

设置 shared_preload_libraries 参数 ：

$ psql -c "ALTER SYSTEM set shared_preload_libraries TO 'pg_stat_statements'"
$ pg_ctl -D $PGDATA restart -mf

注意： $PGDATA 应替换为实际的数据目录位置。
3. 查看执行至少100次的查询，按执行时间降序排列 ：

postgres=# select calls, mean_exec_time, queryid, left(query, 40) as query from pg_stat_statements
GROUP BY mean_exec_time,calls,queryid,query HAVING calls > 100
order by 2 desc limit 10;

2.3 工作原理

pg_stat_statements 扩展可以显示查询的执行次数、平均执行时间、内存块命中数、磁盘块获取数等信息，还能显示查询计划的准备时间和实际执行时间，有助于理解是否需要使用预准备语句来减少查询计划的创建次数。

3. 使用EXPLAIN查看执行计划

3.1 准备工作

运行 EXPLAIN 时，SQL语句的绑定变量必须替换为实际值。运行 EXPLAIN ANALYZE 时，应避免在执行DML或DDL的SQL语句上使用，紧急情况下可在事务中运行并立即回滚。

3.2 操作步骤

生成优化器选择的执行计划 ：

migops=# EXPLAIN DELETE FROM t1 WHERE id IN (select dept from t2 where id between 1 and 100);

查看实际成本和时间 ：

migops=# BEGIN;
migops=*# EXPLAIN (ANALYZE, TIMING, COSTS, BUFFERS, VERBOSE) DELETE FROM t1 WHERE id IN (select dept from t2 where id between 1 and 100);
migops=*# ROLLBACK ;

优化查询 ：

migops=# CREATE INDEX t1_idx ON t1 (id);
migops=# CREATE INDEX t2_idx ON t2 (id);
migops=# BEGIN ;
migops=*# EXPLAIN (ANALYZE, TIMING, COSTS, BUFFERS, VERBOSE) DELETE FROM t1 WHERE id IN (select dept from t2 where id between 1 and 100);
migops=*# ROLLBACK ;

3.3 工作原理

EXPLAIN 可以帮助分析成本较高的计划节点。如果 EXPLAIN 的输出不够，可以使用 EXPLAIN ANALYZE 生成实际数据。找到查询耗时较长的节点后，可以通过重写查询或创建索引来优化。

4. PostgreSQL版本升级概述

4.1 升级原因

修复操作系统或数据库软件中新发现的安全漏洞。
应用用户发现的漏洞补丁。
引入新功能以提高性能或用户体验。
实现用户普遍需求的缺失功能。

4.2 技术要求

进行版本升级时，需要一个PostgreSQL服务器，至少有两个CPU和2GB RAM，并能从PGDG仓库和GitHub下载RPM包。

4.3 主要和次要版本区别

9.6及之前 ：版本号的前两位是主要版本，第三位是次要版本。
10及之后 ：版本号的第一位是主要版本，第二位是次要版本。

次要版本通常包含漏洞补丁、安全修复或现有功能的改进，升级时通常只需重启数据库或服务器。主要版本可能包含全新的功能或对现有功能的重大增强，升级时需要将整个数据库转储并恢复到使用较新主要版本二进制文件初始化的不同数据目录中，或者使用 pg_upgrade ，这可能需要更多的停机时间和人力。

4.4 过时版本

PostgreSQL全球开发组对一个主要版本的支持期为自初始发布后的5年，之后该版本被视为EOL（生命周期结束）。目前，支持次要版本发布的主要版本是9.5及更高版本，9.4及更早版本已被视为过时，不再受社区支持。

4.5 升级方法

主要版本升级到PostgreSQL 13使用 pg_dumpall
主要版本升级到PostgreSQL 13使用 pg_dump 和 pg_restore
主要版本升级到PostgreSQL 13使用 pg_upgrade （有停机时间）
主要版本升级到PostgreSQL 13使用 pg_upgrade （使用硬链接实现无缝停机）
安装 pglogical 扩展以将旧版本升级到PostgreSQL 13
使用 pglogical 扩展升级到PostgreSQL 13以避免停机
使用逻辑复制和逻辑解码升级到PostgreSQL 13
更新PostgreSQL 13的次要版本

4.6 测试环境要求

为了进行测试，服务器配置应为一个CPU、1GB RAM和20GB可用磁盘空间，操作系统建议使用CentOS，也可以使用Ubuntu/Debian，并具备从PGDG仓库下载包的互联网连接。

4.7 升级流程mermaid图

graph LR
    A[确定升级类型] --> B{主要版本升级}
    A --> C{次要版本升级}
    B --> D[选择升级方法]
    D --> E[执行升级操作]
    C --> F[更新二进制文件]
    F --> G[重启服务器]
    E --> H[验证升级结果]
    G --> H

4.8 主要和次要版本对比表格

版本类型	版本号定义	包含内容	升级方式	停机时间
主要版本	9.6及之前：前两位；10及之后：第一位	新功能或重大增强	转储恢复或 `pg_upgrade`	较长
次要版本	9.6及之前：第三位；10及之后：第二位	漏洞补丁、安全修复、功能改进	重启数据库或服务器	较短

5. 主要版本升级到 PostgreSQL 13 使用 pg_dumpall

5.1 操作步骤

停止旧版本的 PostgreSQL 服务 ：

$ pg_ctl -D /path/to/old/data/directory stop

使用 pg_dumpall 转储整个数据库集群 ：

$ pg_dumpall -U postgres -h localhost -p 5432 > all_databases.sql

安装 PostgreSQL 13 ：根据操作系统的不同，使用相应的包管理工具安装 PostgreSQL 13。
初始化新的数据目录 ：

$ initdb -D /path/to/new/data/directory

启动 PostgreSQL 13 服务 ：

$ pg_ctl -D /path/to/new/data/directory start

恢复数据库 ：

$ psql -U postgres -h localhost -p 5432 -f all_databases.sql

5.2 注意事项

转储和恢复过程可能需要较长时间，尤其是对于大型数据库。
确保新的数据目录有足够的磁盘空间。

6. 主要版本升级到 PostgreSQL 13 使用 pg_dump 和 pg_restore

6.1 操作步骤

停止旧版本的 PostgreSQL 服务 ：

$ pg_ctl -D /path/to/old/data/directory stop

使用 pg_dump 转储每个数据库 ：

$ pg_dump -U postgres -h localhost -p 5432 -F c -b -v -f database1.dump database1
$ pg_dump -U postgres -h localhost -p 5432 -F c -b -v -f database2.dump database2

安装 PostgreSQL 13 ：根据操作系统的不同，使用相应的包管理工具安装 PostgreSQL 13。
初始化新的数据目录 ：

$ initdb -D /path/to/new/data/directory

启动 PostgreSQL 13 服务 ：

$ pg_ctl -D /path/to/new/data/directory start

创建新的数据库 ：

CREATE DATABASE database1;
CREATE DATABASE database2;

使用 pg_restore 恢复每个数据库 ：

$ pg_restore -U postgres -h localhost -p 5432 -d database1 database1.dump
$ pg_restore -U postgres -h localhost -p 5432 -d database2 database2.dump

6.2 优点

可以选择性地恢复数据库，灵活性更高。
恢复速度可能比 pg_dumpall 更快。

7. 主要版本升级到 PostgreSQL 13 使用 pg_upgrade （有停机时间）

7.1 操作步骤

安装 PostgreSQL 13 ：根据操作系统的不同，使用相应的包管理工具安装 PostgreSQL 13。
停止旧版本的 PostgreSQL 服务 ：

$ pg_ctl -D /path/to/old/data/directory stop

$ initdb -D /path/to/new/data/directory

运行 pg_upgrade ：

$ pg_upgrade -b /path/to/old/bin -B /path/to/new/bin -d /path/to/old/data/directory -D /path/to/new/data/directory

启动新的 PostgreSQL 13 服务 ：

$ pg_ctl -D /path/to/new/data/directory start

验证升级结果 ：连接到新的数据库，执行一些简单的查询，确保一切正常。

7.2 注意事项

pg_upgrade 要求旧版本和新版本的数据目录在同一台服务器上。
升级过程中需要足够的磁盘空间来存储临时文件。

8. 主要版本升级到 PostgreSQL 13 使用 pg_upgrade （使用硬链接实现无缝停机）

8.1 操作步骤

安装 PostgreSQL 13 ：根据操作系统的不同，使用相应的包管理工具安装 PostgreSQL 13。
创建新的数据目录 ：

$ mkdir /path/to/new/data/directory

创建硬链接 ：

$ ln /path/to/old/data/directory/* /path/to/new/data/directory/

运行 pg_upgrade ：

$ pg_upgrade -b /path/to/old/bin -B /path/to/new/bin -d /path/to/old/data/directory -D /path/to/new/data/directory

切换到新的数据目录 ：修改 PostgreSQL 服务的配置文件，指向新的数据目录。
启动新的 PostgreSQL 13 服务 ：

$ pg_ctl -D /path/to/new/data/directory start

8.2 优点

可以实现无缝升级，减少停机时间。
避免了数据的复制，节省了时间和磁盘空间。

9. 安装 pglogical 扩展以将旧版本升级到 PostgreSQL 13

9.1 操作步骤

安装 pglogical 扩展 ：根据操作系统的不同，使用相应的包管理工具安装 pglogical 扩展。
在旧数据库和新数据库中创建 pglogical 扩展 ：

CREATE EXTENSION pglogical;

在旧数据库中创建发布 ：

SELECT pglogical.create_node(
    node_name := 'old_node',
    dsn := 'host=old_host port=5432 dbname=old_db user=old_user password=old_password'
);
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

在新数据库中创建订阅 ：

SELECT pglogical.create_node(
    node_name := 'new_node',
    dsn := 'host=new_host port=5432 dbname=new_db user=new_user password=new_password'
);
SELECT pglogical.create_subscription(
    subscription_name := 'old_to_new',
    provider_dsn := 'host=old_host port=5432 dbname=old_db user=old_user password=old_password'
);

等待数据同步完成 ：监控订阅的状态，确保数据同步完成。
切换应用程序到新数据库 ：修改应用程序的数据库连接配置，指向新数据库。

9.2 优点

可以实现零停机升级。
支持增量同步，减少了数据传输量。

10. 使用 pglogical 扩展升级到 PostgreSQL 13 以避免停机

10.1 操作步骤

安装 pglogical 扩展 ：同第 9 节的步骤 1。
在旧数据库和新数据库中创建 pglogical 扩展 ：同第 9 节的步骤 2。
在旧数据库中创建发布 ：同第 9 节的步骤 3。
在新数据库中创建订阅 ：同第 9 节的步骤 4。
在新数据库中进行数据验证 ：执行一些查询，确保数据同步正确。
切换应用程序到新数据库 ：修改应用程序的数据库连接配置，指向新数据库。
停止旧数据库的发布和订阅 ：

SELECT pglogical.drop_subscription('old_to_new');
SELECT pglogical.drop_node('old_node');

10.2 注意事项

需要确保旧数据库和新数据库之间的网络连接稳定。
数据同步过程可能需要一定的时间，尤其是对于大型数据库。

11. 使用逻辑复制和逻辑解码升级到 PostgreSQL 13

11.1 操作步骤

在旧数据库中启用逻辑复制 ：修改 postgresql.conf 文件，添加以下配置：

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

重启旧数据库 ：

$ pg_ctl -D /path/to/old/data/directory restart

在旧数据库中创建发布 ：

CREATE PUBLICATION my_publication FOR ALL TABLES;

在新数据库中创建订阅 ：

CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=old_host port=5432 dbname=old_db user=old_user password=old_password'
PUBLICATION my_publication;

等待数据同步完成 ：监控订阅的状态，确保数据同步完成。
切换应用程序到新数据库 ：修改应用程序的数据库连接配置，指向新数据库。

11.2 优点

逻辑复制和逻辑解码是 PostgreSQL 内置的功能，无需额外安装扩展。
可以实现实时数据同步，减少停机时间。

12. 更新 PostgreSQL 13 的次要版本

12.1 操作步骤

备份数据 ：在进行任何升级操作之前，务必备份数据库数据。
停止 PostgreSQL 服务 ：

$ pg_ctl -D /path/to/data/directory stop

安装新的次要版本 ：根据操作系统的不同，使用相应的包管理工具安装新的次要版本。
启动 PostgreSQL 服务 ：

$ pg_ctl -D /path/to/data/directory start

验证升级结果 ：连接到数据库，执行一些简单的查询，确保一切正常。

12.2 注意事项

次要版本升级通常比较简单，但仍然建议在测试环境中进行测试。
升级过程中可能需要重启数据库服务，因此需要提前通知相关人员。

13. 升级方法对比表格

升级方法	停机时间	操作复杂度	适用场景
pg_dumpall	较长	简单	小型数据库集群
pg_dump 和 pg_restore	较长	中等	选择性恢复数据库
pg_upgrade （有停机时间）	较长	中等	同一服务器上的升级
pg_upgrade （使用硬链接实现无缝停机）	短	中等	减少停机时间
pglogical 扩展	零停机	复杂	大型数据库零停机升级
逻辑复制和逻辑解码	短	中等	实时数据同步
更新次要版本	短	简单	快速修复漏洞和改进功能

14. 升级流程总结 mermaid 图

graph LR
    A[选择升级方法] --> B{主要版本升级}
    A --> C{次要版本升级}
    B --> D[准备环境]
    D --> E[执行升级操作]
    C --> F[更新二进制文件]
    F --> G[重启服务器]
    E --> H[验证升级结果]
    G --> H
    H --> I[切换应用程序]

综上所述，PostgreSQL 的扩展安装、性能调优和版本升级是数据库管理中的重要任务。通过合理使用各种工具和方法，可以提高数据库的性能和稳定性，同时减少升级过程中的停机时间。在进行升级操作之前，务必进行充分的测试和备份，以确保数据的安全。