pg使用pg_upgrade升级后的一些检查

pg_upgrade 的核心功能与检查范围

pg_upgrade 主要用于 大版本升级（如从 PostgreSQL 13 升级到 14），其核心逻辑是：

1. 数据文件迁移：直接复制数据文件（避免通过逻辑导出 / 导入），最大限度减少数据丢失风险。
2. 二进制兼容性检查：
   --验证源和目标版本的共享库、系统表结构是否兼容。
   --检查扩展（如 PostGIS、pgcrypto）是否在目标版本中存在或兼容。
   --检测数据库集群的编码、时区等全局配置是否一致。
3. 系统级依赖检查：如文件权限、路径、端口冲突等。

注意：pg_upgrade 不涉及对用户业务逻辑（函数、触发器、SQL 语句）的语法或语义检查，这些属于用户自定义对象，其兼容性需由用户自行保障。
推荐步骤

在目标版本搭建测试环境，恢复旧版本备份。
使用 plpgsql_check 等工具扫描函数 / 触发器语法。
手动测试核心业务 SQL 和存储过程。
参考 PostgreSQL 官方文档的 Upgrading 章节，处理已知兼容性问题。

在升级前，建议手动或通过工具检查以下内容

1.PL/pgSQL 函数 / 触发器：

点击查看代码

CREATE EXTENSION plpgsql_check;
SELECT * FROM plpgsql_check_function('函数名');  -- 检查单个函数
SELECT * FROM plpgsql_check_function_all();       -- 检查所有函数
SELECT proname, plpgsql_check_function(oid)
FROM pg_proc
WHERE prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql');

2.SQL 语句兼容性：

3.存储过程 / 扩展依赖：

检查配置文件语法

检查扩展（EXTENSIONS）兼容性

点击查看代码

-- 查看已安装的扩展及其版本
SELECT * FROM pg_available_extensions 
WHERE installed_version IS NOT NULL;

-- 查询需要升级的扩展（对比新版本支持的扩展版本）
SELECT name, installed_version, default_version 
FROM pg_available_extensions 
WHERE installed_version != default_version;

##操作建议：
访问 PGXN 或扩展官方文档，确认扩展是否支持目标版本。
示例：postgis、pg_stat_statements 等扩展需升级到适配版本。

检查函数兼容性

1.plpgsql_check 扩展

plpgsql_check 主要用于检查 PL/pgSQL 函数的语法和语义错误，在版本升级后可用来检查函数是否存在兼容性问题。

原理：它会对 PL/pgSQL 函数进行静态分析，检查函数内部的语句是否符合新版本的语法规则和语义要求。

点击查看代码

####安装和使用示例
-- 安装扩展
CREATE EXTENSION plpgsql_check;

-- 检查单个函数
SELECT * FROM plpgsql_check_function('your_function_name');

-- 检查所有 PL/pgSQL 函数
SELECT proname, plpgsql_check_function(oid)
FROM pg_proc
WHERE prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql');

检查触发器兼容性

点击查看代码

-- 查看触发器定义的依赖函数
SELECT tgname, tgfoid::regproc, pg_get_triggerdef(oid) 
FROM pg_trigger;
#操作建议
确保触发器调用的函数在新版本中仍有效。
检查 pg_trigger 中是否存在旧版本语法（如 FOR EACH ROW EXECUTE PROCEDURE）

检查存储过程和 SQL 语法

点击查看代码

-- 检查存储过程（PostgreSQL 11+ 支持 PROCEDURE）
SELECT proname, prosrc 
FROM pg_proc 
WHERE prokind = 'p'; -- 'p' 表示存储过程

-- 检查视图和规则中的 SQL 语句
SELECT definition 
FROM pg_views 
WHERE definition LIKE '%DEPRECATED_KEYWORD%';

-- 检查使用旧语法（如 `::regclass` 替代 OID）
SELECT routine_definition 
FROM information_schema.routines 
WHERE routine_definition LIKE '%::oid%';
#常见不兼容 SQL：
隐式类型转换（如 timestamp 与 timestamptz 混用）。
废弃的关键字（如 ENCODING 在 CREATE DATABASE 中的行为变化）。