PostgreSQL 升级后功能验证

验证内容

PostgreSQL 升级后功能验证应覆盖以下关键方面：

数据库连接与基础功能
数据完整性验证
扩展功能验证
应用兼容性验证
性能验证
复制与高可用功能验证
安全功能验证

验证步骤

1. 数据库连接与基础功能验证

连接验证

bash

# 验证本地连接
psql -h localhost -p 5432 -U postgres -c "SELECT version();"

# 验证远程连接
psql -h remote-host -p 5432 -U app_user -d mydb -c "SELECT 1;"

# 验证应用连接池
# 假设使用 pgBouncer
pgbouncer -V
psql -h localhost -p 6432 -U pgbouncer -d pgbouncer -c "SHOW POOLS;"

基础功能验证

sql

-- 验证基本查询功能
SELECT 1 + 1 AS result;

-- 验证数据库列表
\l
SELECT datname FROM pg_database;

-- 验证表操作
CREATE TABLE test_table (id SERIAL PRIMARY KEY, name VARCHAR(50));
INSERT INTO test_table (name) VALUES ('test');
SELECT * FROM test_table;
UPDATE test_table SET name = 'updated' WHERE id = 1;
DELETE FROM test_table WHERE id = 1;
DROP TABLE test_table;

2. 数据完整性验证

数据量验证

sql

-- 验证关键表的数据量
SELECT count(*) FROM users;
SELECT count(*) FROM orders;

-- 与升级前数据量对比
-- 建议提前备份升级前的表数据量统计

数据一致性验证

sql

-- 验证主键唯一性
SELECT id, count(*) FROM users GROUP BY id HAVING count(*) > 1;

-- 验证外键关系
-- 检查是否存在无效的外键引用
SELECT * FROM orders WHERE user_id NOT IN (SELECT id FROM users);

-- 验证索引完整性
REINDEX VERBOSE TABLE users;

系统表验证

sql

-- 检查系统表完整性
VACUUM ANALYZE VERBOSE pg_catalog.pg_class;

3. 扩展功能验证

sql

-- 列出所有已安装扩展
SELECT extname, extversion FROM pg_extension;

-- 验证常用扩展功能

-- pg_stat_statements
SELECT * FROM pg_stat_statements LIMIT 5;

-- pgcrypto
SELECT crypt('password', gen_salt('bf'));

-- hstore
SELECT 'a=>1, b=>2'::hstore;

-- 验证扩展升级
ALTER EXTENSION pg_stat_statements UPDATE;

4. 应用兼容性验证

SQL语法验证

bash

# 运行应用常用的SQL查询
psql -d mydb -f app_common_queries.sql

存储过程与函数验证

sql

-- 验证存储过程执行
CALL app_procedure();

-- 验证函数执行
SELECT app_function(123);

-- 验证触发器
INSERT INTO trigger_table (data) VALUES ('test');
SELECT * FROM audit_log WHERE table_name = 'trigger_table';

视图与物化视图验证

sql

-- 验证视图查询
SELECT * FROM app_view LIMIT 10;

-- 刷新并验证物化视图
REFRESH MATERIALIZED VIEW app_materialized_view;
SELECT * FROM app_materialized_view LIMIT 10;

5. 性能验证

基本性能指标

sql

-- 检查缓冲区命中率
SELECT 
  round(blks_hit * 100.0 / (blks_hit + blks_read), 2) AS buffer_hit_ratio
FROM pg_stat_database;

-- 检查索引使用率
SELECT 
  schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

慢查询验证

bash

# 检查慢查询日志
grep -i "duration:.*ms" /var/log/postgresql/postgresql-15-main.log | head -20

# 使用 pgBadger 分析慢查询
pgbadger /var/log/postgresql/postgresql-15-main.log -o slow_query_report.html

TPC-H 或 TPC-C 性能测试

bash

# 使用 pgbench 进行简单性能测试
pgbench -i -s 10 mydb
pgbench -c 10 -j 4 -T 60 -r mydb > post_upgrade_performance.txt

# 与升级前性能对比
diff pre_upgrade_performance.txt post_upgrade_performance.txt

6. 复制与高可用功能验证

主备复制验证

sql

-- 主库查看复制状态
SELECT * FROM pg_stat_replication;

-- 备库查看复制状态
SELECT * FROM pg_stat_wal_receiver;
SELECT pg_is_in_recovery();

-- 测试复制延迟
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replication_delay
FROM pg_stat_replication;

高可用切换验证

bash

# 测试主备切换（假设使用 Patroni 或其他 HA 工具）
patronictl failover --force

# 验证切换后新主库状态
psql -h new-master -c "SELECT pg_is_in_recovery();"

# 验证应用自动重连

7. 安全功能验证

权限验证

sql

-- 验证用户权限
SELECT usename, usecreatedb, usesuper, createrole FROM pg_user;

-- 验证表权限
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public';

-- 验证行级安全策略
SELECT * FROM secure_table WHERE user_id = current_user;

加密功能验证

sql

-- 验证 SSL 连接
SHOW ssl;

-- 验证数据加密
SELECT column_name, collation_name, character_set_name
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'encrypted_table';

验证工具与脚本

自动化验证脚本

bash

#!/bin/bash

# PostgreSQL 升级后功能验证脚本

DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
DB_NAME="mydb"

# 日志文件
LOG_FILE="post_upgrade_verification.log"

# 开始验证
echo "PostgreSQL 升级后功能验证开始: $(date)" > $LOG_FILE

# 1. 连接验证
echo "\n1. 连接验证:" >> $LOG_FILE
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -c "SELECT version();" >> $LOG_FILE 2>&1

# 2. 数据完整性验证
echo "\n2. 数据完整性验证:" >> $LOG_FILE
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT count(*) FROM users;" >> $LOG_FILE 2>&1
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT count(*) FROM orders;" >> $LOG_FILE 2>&1

# 3. 扩展功能验证
echo "\n3. 扩展功能验证:" >> $LOG_FILE
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT extname, extversion FROM pg_extension;" >> $LOG_FILE 2>&1

# 4. 性能指标验证
echo "\n4. 性能指标验证:" >> $LOG_FILE
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT round(blks_hit * 100.0 / (blks_hit + blks_read), 2) AS buffer_hit_ratio FROM pg_stat_database;" >> $LOG_FILE 2>&1

# 结束验证
echo "\nPostgreSQL 升级后功能验证结束: $(date)" >> $LOG_FILE

echo "验证报告已生成: $LOG_FILE"

验证报告模板

验证项	验证内容	验证方法	预期结果
数据库连接	本地连接	psql 命令	成功连接并返回版本信息
数据库连接	远程连接	psql 命令	成功连接并执行查询
数据完整性	表数据量	COUNT 查询	与升级前一致
数据完整性	主键唯一性	GROUP BY 查询	无重复主键
扩展功能	pg_stat_statements	函数调用	正常返回统计信息
应用兼容性	常用SQL	执行应用SQL脚本	所有查询成功执行
性能	缓冲区命中率	查询 pg_stat_database	>95%
复制功能	复制状态	查询 pg_stat_replication	复制正常，延迟 < 1MB

常见问题及解决方案

1. 扩展无法加载

问题：升级后某些扩展无法加载，报错 "could not access file "$libdir/extension_name": No such file or directory"

解决方案：

sql

-- 重新安装扩展
DROP EXTENSION IF EXISTS extension_name;
CREATE EXTENSION extension_name;

-- 或更新扩展
ALTER EXTENSION extension_name UPDATE;

2. 存储过程执行失败

问题：升级后存储过程执行失败，报错 "function X does not exist"

解决方案：

sql

-- 检查存储过程是否存在
SELECT proname FROM pg_proc WHERE proname = 'function_name';

-- 重新创建存储过程
CREATE OR REPLACE FUNCTION function_name()
RETURNS void AS $$
BEGIN
    -- 函数体
END;
$$ LANGUAGE plpgsql;

3. 性能下降

问题：升级后查询性能明显下降

解决方案：

sql

-- 更新统计信息
VACUUM ANALYZE VERBOSE;

-- 重建索引
REINDEX DATABASE mydb;

-- 优化查询计划
ANALYZE VERBOSE problematic_table;

-- 检查并调整配置参数
ALTER SYSTEM SET shared_buffers = '8GB';
SELECT pg_reload_conf();

4. 复制延迟增大

问题：升级后备库复制延迟明显增大

解决方案：

sql

-- 检查主库WAL生成速率
SELECT pg_current_wal_lsn();

-- 检查备库WAL应用速率
SELECT pg_last_wal_replay_lsn();

-- 调整备库配置
ALTER SYSTEM SET max_wal_receivers = '10';
ALTER SYSTEM SET hot_standby = 'on';

验证完成标准

验证完成的标准包括：

所有关键验证项通过
数据完整性验证通过
应用兼容性验证通过
性能指标符合预期（或优于升级前）
复制与高可用功能正常
安全功能正常
没有严重的警告或错误日志

常见问题（FAQ）

Q1：升级后必须进行所有验证项吗？

A1：建议进行所有验证项，特别是数据完整性、应用兼容性和复制功能验证。对于非关键系统，可以根据实际情况选择性验证。

Q2：验证过程需要停机吗？

A2：大部分验证可以在线进行，无需停机。但某些验证（如高可用切换测试）可能需要短暂的服务中断，建议在业务低峰期进行。

Q3：如何对比升级前后的性能？

A3：建议在升级前记录关键性能指标和运行基准测试，升级后使用相同的测试方法进行对比。

Q4：验证过程需要多长时间？

A4：验证时间取决于数据库大小和复杂度，从小型数据库的几十分钟到大型数据库的数小时不等。

Q5：验证失败后如何处理？

A5：如果验证失败，应立即分析原因，采取相应的修复措施。如果问题严重，应考虑回滚升级。

Q6：如何自动化验证过程？

A6：可以编写验证脚本，结合CI/CD工具自动执行验证，并生成验证报告。

PostgreSQL 升级后功能验证 ​

验证内容 ​

验证步骤 ​

1. 数据库连接与基础功能验证 ​

连接验证 ​

基础功能验证 ​

2. 数据完整性验证 ​

数据量验证 ​

数据一致性验证 ​

系统表验证 ​

3. 扩展功能验证 ​

4. 应用兼容性验证 ​

SQL语法验证 ​

存储过程与函数验证 ​

视图与物化视图验证 ​

5. 性能验证 ​

基本性能指标 ​

慢查询验证 ​

TPC-H 或 TPC-C 性能测试 ​

6. 复制与高可用功能验证 ​

主备复制验证 ​

高可用切换验证 ​

7. 安全功能验证 ​

权限验证 ​

加密功能验证 ​

验证工具与脚本 ​

自动化验证脚本 ​

验证报告模板 ​

常见问题及解决方案 ​

1. 扩展无法加载 ​

2. 存储过程执行失败 ​

3. 性能下降 ​

4. 复制延迟增大 ​

验证完成标准 ​

常见问题（FAQ） ​

Q1：升级后必须进行所有验证项吗？ ​

Q2：验证过程需要停机吗？ ​

Q3：如何对比升级前后的性能？ ​

Q4：验证过程需要多长时间？ ​

Q5：验证失败后如何处理？ ​

Q6：如何自动化验证过程？ ​

PostgreSQL 升级后功能验证

验证内容

验证步骤

1. 数据库连接与基础功能验证

连接验证

基础功能验证

2. 数据完整性验证

数据量验证

数据一致性验证

系统表验证

3. 扩展功能验证

4. 应用兼容性验证

SQL语法验证

存储过程与函数验证

视图与物化视图验证

5. 性能验证

基本性能指标

慢查询验证

TPC-H 或 TPC-C 性能测试

6. 复制与高可用功能验证

主备复制验证

高可用切换验证

7. 安全功能验证

权限验证

加密功能验证

验证工具与脚本

自动化验证脚本

验证报告模板

常见问题及解决方案

1. 扩展无法加载

2. 存储过程执行失败

3. 性能下降

4. 复制延迟增大

验证完成标准

常见问题（FAQ）

Q1：升级后必须进行所有验证项吗？

Q2：验证过程需要停机吗？

Q3：如何对比升级前后的性能？

Q4：验证过程需要多长时间？

Q5：验证失败后如何处理？

Q6：如何自动化验证过程？