20250219_pg数据库物理迁移升级方案

内容概述

本文档主要实现postgresql不同版本数据库pg_upgrade物理迁移升级

原库/目标库检查

### 1. 检查数据库版本
select version();

### 2. 检查数据库启动时间
select pg_postmaster_start_time();

### 3. 检查数据库时区
show timezone;

### 4. 检查OS时区
date

### 5. 检查当前时间
select now();

### 6. 检查集群有哪些数据库
\l+

### 7. 检查集群中有哪些用户
select current_user;  
select * from pg_user;
\du

### 8. 检查集群有哪些表空间
\db+

### 9. 检查 数据库服务器的IP及端口
select inet_server_addr(),inet_server_port();

### 10. 检查数据库实例处于Hot Standby状态还是正常数据库状态, f: 正常数据库状态；t: Hot Standby状态
select pg_is_in_recovery();

### 11. 检查表空间大小
select pg_size_pretty(pg_tablespace_size('pg_global'));
select pg_size_pretty(pg_tablespace_size('pg_default'));

### 12. 检查数据库大小
select pg_size_pretty(pg_database_size('osdba'));

### 13. 检查运行的SQL
select pid,usename,query_start,query from pg_stat_activity;

### 14. 检查运行参数
SHOW ALL;

### 15. 检查表大小
select * from pg_tables;
postgres=# \c osdba   <--切换需要导出数据库
osdba=# \d+           <--检查表大小

### 16. 检查函数
\df

### 17. 检查扩展插件
\dx

### 18. 检查归档信息
show data_directory;
show archive_command;
show archive_mode;
show wal_level;
show wal_segment_size ;
show max_wal_senders
### 19. 检查访问控制信息
cat $PGDATA/pg_hba.conf |egrep -v '^#'

源库导出数据库

### 根据上面检查结果，确认导出目录存储是否足够，建议大于数据库大小

### 创建备份目录
mkdir -p /home/postgres/backup/bak1217
### 源库备份
pg_basebackup -D /home/postgres/backup/bak1217 -Ft -z -P

### 传输备份集到目标端
scp -r *.tar.gz postgres@192.168.1.17:/home/postgres/backup/bak1217/

目标库恢复数据库

### 根据上面检查结果，确认导入目录存储是否足够，建议大于数据库大小

### 创建备份目录
mkdir -p /home/postgres/backup/bak1217/data/
mkdir -p /home/postgres/backup/bak1217/arch/

### 
cd /home/postgres/backup/bak1217/
tar -xvf base.tar.gz -C /home/postgres/backup/bak1217/data/
tar -xvf pg_wal.tar.gz -C /home/postgres/backup/bak1217/arch/

vi /home/postgres/backup/bak1217/data/postgresql.auto.conf

restore_command = 'cp /home/postgres/backup/bak1217/arch/%f %p'
recovery_target = 'immediate'

touch /home/postgres/backup/bak1217/data/recovery.signal

[root@pgdb1 backup]# chown -R postgres:postgres bak1217/
[root@pgdb1 backup]# chmod -R 700 bak1217/
[postgres@pgdb1 ~]$ env|grep PG  <--修改环境变量
pg_ctl start

SELECT pg_is_in_recovery();
### 设置数据库为读写状态
select pg_wal_replay_resume();
SELECT pg_is_in_recovery();

目标端数据库升级

### 目标端创建数据库实例
/u01/app/pg/15.6/pgsql/bin/initdb -D /u01/app/pg/15.6/data_new/  -E UTF8 --lc-collate=C --lc-ctype=en_US.utf8

### 升级检查
/u01/app/pg/15.6/pgsql/bin/pg_upgrade \
--old-datadir /home/postgres/backup/bak1217/data/ \
--new-datadir /u01/app/pg/15.6/data_new/ \
--old-bindir /u01/app/pg/12.8/pgsql/bin/ \
--new-bindir /u01/app/pg/15.6/pgsql/bin/ \
--check


### 数据库升级
/u01/app/pg/15.6/pgsql/bin/pg_upgrade \
--old-datadir /home/postgres/backup/bak1217/data/ \
--new-datadir /u01/app/pg/15.6/data_new/ \
--old-bindir /u01/app/pg/12.8/pgsql/bin/ \
--new-bindir /u01/app/pg/15.6/pgsql/bin/ \
--link

### 数据库启动
pg_ctl start

### 更新统计信息
/u01/app/pg/15.6/pgsql/bin/vacuumdb --all --analyze-in-stages

### 删除旧数据库数据
./delete_old_cluster.sh

###旧集群同步参数文件
postgresql.conf
pg_hba.conf
pg_ident.conf
postgresql.auto.conf

业务侧验证

该步骤DBA配合，及做好监控

常见问题

问题一 old and new pg_controldata WAL segment sizes are invalid or do not match

$ pg_upgrade \
--old-datadir /pgsql/dev1217/data/ \
--new-datadir /dev1217/ \
--old-bindir /home/postgres/pgsql/bin/ \
--new-bindir /home/postgres/pgsql_new/bin/ \
--check
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok

old and new pg_controldata WAL segment sizes are invalid or do not match
Failure, exiting

新库在初始化时WAL segment sizes需要与主库保持一致。
初始化数据库时使用参数--wal-segsizeg与源库保持一致
initdb --wal-segsize=SIZE    size of WAL segments, in megabytes