Skip to content

📚 PostgreSQL Use pgdump to upgrade(Large)

🔍 Prepare：

## OS and Software environment：

Environment name Version
OS（Old） Rocky Linux 8.x
OS（New） Rocky Linux 9.x
DB（Old） PostgreSQL 13.X
DB（New） PostgreSQL 16.X

1. 通知用户并收集用户密码

通知用户将进行DB迁移升级，因PG升级后默认加密方式从password_encryption = md5 变更为 password_encryption = scram-sha-256 需调整程式能支持scram-sha-256加密协议，并提供密码进行升级后的更改

select * from pg_authid where rolcanlogin='true' and rolpassword like '%md5%'; --捞取加密协议为MD5的账号

2. 备份相关文件

备份相关文件：

  • [备份multipath.conf文件]

  • [保留df -h分区命令信息]

  • [备份Crontab job及其脚本]

  • [备份IP配置信息、hosts文件信息]

  • [备份系统配置文件]（sysctl.conf、limits.conf、resolv.conf）

  • [备份PostgreSQL参数文件]（postgresql.conf、pg_hba.conf、postgresql.auto.conf）

  • [备份pgbouncer参数文件]

  • [备份postgres_exporter参数文件]

  • [备份node_exporter参数文件]

  • [备份pgagent参数文件]

路径参考：

  • /etc/sysconfig/network-scripts/ens192

  • /etc/sysconfig/network-scripts/ens224

  • /etc/hosts

  • /etc/sysctl.conf

  • /etc/security/limits.conf

  • /etc/resolv.conf

  • /usr/local/pgbouncer/share/doc/pgbouncer/pgbouncer_*.ini

  • /usr/local/pgbouncer/share/doc/pgbouncer/userlist_*.txt

  • /etc/systemd/system/pgbouncer_*.service

  • /usr/lib/systemd/system/postgres_exporter_*.service

  • /usr/local/postgres_exporter-0.11/queries.yaml

  • /usr/lib/systemd/system/node_exporter.service

  • /usr/lib/systemd/system/pgagent_*.service

  • /etc/pgagent/pgagent_*.conf

3. 搭建OS（参考OS安装SOP）

4. 创建postgres账号及配置用户环境变量（参考软件安装SOP）

5. 安装PG软件及安装PG插件（参考软件安装SOP）

6. 配置postgres_dba 运维脚本（参考软件安装SOP）

7. 初始化实例（参照旧版本服务器初始化所有实例，用于后续导入）

[初始化ZSARMP实例] 

mkdir /pgdata/ZSARMP
chmod 700 /pgdata/ZSARMP
initdb -D /pgdata/ZSARMP --encoding="UTF8" --locale="en_US.UTF-8"

[初始化ZSM0GPSP实例] 

mkdir /pgdata/ZSM0GPSP
chmod 700 /pgdata/ZSM0GPSP
initdb -D /pgdata/ZSM0GPSP --encoding="UTF8" --locale="en_US.UTF-8"

[初始化ZSM1PARKP实例] 

mkdir /pgdata/ZSM1PARKP
chmod 700 /pgdata/ZSM1PARKP
initdb -D /pgdata/ZSM1PARKP --encoding="UTF8" --locale="en_US.UTF-8"

[初始化ZSM2TRANP实例] 

mkdir /pgdata/ZSM2TRANP
chmod 700 /pgdata/ZSM2TRANP
initdb -D /pgdata/ZSM2TRANP --encoding="UTF8" --locale="en_US.UTF-8"

[初始化ZSM3PCBAP实例] 

mkdir /pgdata/ZSM3PCBAP
chmod 700 /pgdata/ZSM3PCBAP
initdb -D /pgdata/ZSM3PCBAP --encoding="UTF8" --locale="en_US.UTF-8"

[初始化ZSM4FATPP实例] 

mkdir /pgdata/ZSM4FATPP
chmod 700 /pgdata/ZSM4FATPP
initdb -D /pgdata/ZSM4FATPP --encoding="UTF8" --locale="en_US.UTF-8"

8. 修改/拷回参数文件配置（需要注意拷将原来的postgres.conf文件与pg_hba文件中自定义的行追加到新建的实例）

9. 启动实例、修改postgres密码、回收publlic权限

[启动ZSARMP实例] 

pg_ctl start -D /pgdata/ZSARMP

[更改postgres账密并回收权限] 

psql -p 5433
\password postgres
revoke all on schema public from public;

[启动ZSM0GPSP实例] 

pg_ctl start -D /pgdata/ZSM0GPSP

[更改postgres账密并回收权限] 

psql -p 5440
\password postgres
revoke all on schema public from public;

[启动ZSM1PARKP实例] 

pg_ctl start -D /pgdata/ZSM1PARKP

[更改postgres账密并回收权限] 

psql -p 5441
\password postgres
revoke all on schema public from public;

[启动ZSM2TRANP实例] 

pg_ctl start -D /pgdata/ZSM2TRANP

[更改postgres账密并回收权限] 

psql -p 5442
\password postgres
revoke all on schema public from public;

[启动ZSM3PCBAP实例] 

pg_ctl start -D /pgdata/ZSM3PCBAP

[更改postgres账密并回收权限] 

psql -p 5443
\password postgres
revoke all on schema public from public;

[启动ZSM4FATPP实例] 

pg_ctl start -D /pgdata/ZSM4FATPP

[更改postgres账密并回收权限] 

psql -p 5444
\password postgres
revoke all on schema public from public;

10. 通知用户停止连接DB程式（做的当天执行此步）

[确认备份完成之后将旧版本DB备份进行rename保留] 

mv /pgbackup/ZSARMP_single /pgbackup/ZSARMP_single_13backup
mv /pgbackup/ZSM0GPSP_single /pgbackup/ZSM0GPSP_single_13backup
mv /pgbackup/ZSM1PARKP_single /pgbackup/ZSM1PARKP_single_13backup
mv /pgbackup/ZSM2TRANP_single /pgbackup/ZSM2TRANP_single_13backup
mv /pgbackup/ZSM3PCBAP_single /pgbackup/ZSM3PCBAP_single_13backup
mv /pgbackup/ZSM4FATPP_single /pgbackup/ZSM4FATPP_single_13backup
mv /pgbackup/ZSARMP_double /pgbackup/ZSARMP_double_13backup
mv /pgbackup/ZSM0GPSP_double /pgbackup/ZSM0GPSP_double_13backup
mv /pgbackup/ZSM1PARKP_double /pgbackup/ZSM1PARKP_double_13backup
mv /pgbackup/ZSM2TRANP_double /pgbackup/ZSM2TRANP_double_13backup
mv /pgbackup/ZSM3PCBAP_double /pgbackup/ZSM3PCBAP_double_13backup
mv /pgbackup/ZSM4FATPP_double /pgbackup/ZSM4FATPP_double_13backup

11. 用户停止程式后将DB置为只读模式（做的当天执行此步）

12. 查询对象及行数---低版本执行（做的当天执行此步，可能使用到的语句参考如下）

[查找postgresql.conf文件中设定的参数] 

select * from pg_file_settings where applied='true';

[查找使用MD5的账号] 

select rolname,rolpassword from pg_authid where rolcanlogin='t' and rolpassword like 'md5%';

[查找数据中所有schema的对象数量] 

select current_database(),nsp.nspname as SchemaName,case cls.relkind when 'r' then 'TABLE' when 'm' then 'MATERIALIZED_VIEW' when 'i' then 'INDEX' when 'S' then 'SEQUENCE' when 'v' then 'VIEW' when 'c' then 'TYPE' when 'I' then 'PARTITIONED INDEX' when 'p' then 'PARTITIONED TABLE' when 'f' then 'FOREIGN TABLE' else cls.relkind::text end as ObjectType,count(*) from pg_class cls join pg_roles rol on rol.oid = cls.relowner join pg_namespace nsp on nsp.oid = cls.relnamespace where nsp.nspname not in ('information_schema', 'pg_catalog') and nsp.nspname not like 'pg_toast%' group by nsp.nspname, cls.relkind order by nsp.nspname ,cls.relkind;

[查找数据所有表的名称及其数据量] 

select 'select current_database(),'''||schemaname||'.'|| relname || ''' as tablename, count(*) from "' ||schemaname||'"."'|| relname || '" union all ' FROM pg_stat_all_tables where relname NOT LIKE 'pg_%' AND relname NOT LIKE 'sql_%';

[获取导出全局对象] 

select 'nohup pg_dumpall -p '|| current_setting('port') || ' -U postgres -v -g -f /pgbackup/upgrade/' || split_part(current_setting('data_directory'),'/','3') || '_global_exp.sql' || ' > /pgbackup/upgrade/' || split_part(current_setting('data_directory'),'/','3') || '_global_exp.log' || ' 2>&1 &','nohup psql -p '|| current_setting('port') || ' -v -d postgres -f /pgbackup/upgrade/' || split_part(current_setting('data_directory'),'/','3') || '_global_exp.sql' || ' > /pgbackup/upgrade/' || split_part(current_setting('data_directory'),'/','3') || '_global_imp.log' || ' 2>&1 &' ;

[获取导出DB数据语句] 

select 'nohup pg_dump -p '|| current_setting('port') ||' -d '|| datname || ' -C -v -f /pgbackup/upgrade/' || datname || '.sql' || ' > /pgbackup/upgrade/' || datname || '_exp.log' || ' 2>&1 &','nohup psql -p '|| current_setting('port') || ' -v ON_ERROR_STOP=1 -f /pgbackup/upgrade/' || datname || '.sql' || ' > /pgbackup/upgrade/' || datname || '_imp.log' || ' 2>&1 &' from pg_database where datname not in ('postgres','template1','template0');

[获取导出实例数据语句] 

select 'nohup pg_dumpall -p '|| current_setting('port') ||' -c -v -f /pgbackup/upgrade/' || current_setting('port') || '_alldatabase_backup' || '.sql' || ' > /pgbackup/upgrade/' || current_setting('port') || '_alldatabase_backup' || '_exp.log' || ' 2>&1 &','nohup psql -p '|| current_setting('port') || ' -U postgres -d postgres -f /pgbackup/upgrade/' || current_setting('port') || '_alldatabase_backup' || '.sql' || ' > /pgbackup/upgrade/' || current_setting('port') || '_alldatabase_backup' || '_imp.log' || ' 2>&1 &' ;

13. 导出实例全局对象（Role+Tablespace+Data）（做的当天执行此步）

[ZSARMP导出实例全局对象+数据] 

nohup pg_dumpall -p 5433 -c -v -f /pgbackup/upgrade/5433_alldatabase_backup.sql > /pgbackup/upgrade/5433_alldatabase_backup_exp.log 2>&1 &

[确认导出文件是否提示导出成功] 

tail /pgbackup/upgrade/5433_alldatabase_backup.sql

[ZSM0GPSP导出实例全局对象+数据] 

nohup pg_dumpall -p 5440 -c -v -f /pgbackup/upgrade/5440_alldatabase_backup.sql > /pgbackup/upgrade/5440_alldatabase_backup_exp.log 2>&1 &

[确认导出文件是否提示导出成功] 

tail /pgbackup/upgrade/5440_alldatabase_backup.sql

[ZSM1PARKP导出实例全局对象+数据] 

nohup pg_dumpall -p 5441 -c -v -f /pgbackup/upgrade/5441_alldatabase_backup.sql > /pgbackup/upgrade/5441_alldatabase_backup_exp.log 2>&1 &

[确认导出文件是否提示导出成功] 

tail /pgbackup/upgrade/5441_alldatabase_backup.sql

[ZSM2TRANP导出实例全局对象+数据] 

nohup pg_dumpall -p 5442 -c -v -f /pgbackup/upgrade/5442_alldatabase_backup.sql > /pgbackup/upgrade/5442_alldatabase_backup_exp.log 2>&1 &

[确认导出文件是否提示导出成功] 

tail /pgbackup/upgrade/5442_alldatabase_backup.sql

[ZSM3PCBAP导出实例全局对象+数据] 

nohup pg_dumpall -p 5443 -c -v -f /pgbackup/upgrade/5443_alldatabase_backup.sql > /pgbackup/upgrade/5443_alldatabase_backup_exp.log 2>&1 &

[确认导出文件是否提示导出成功] 

tail /pgbackup/upgrade/5443_alldatabase_backup.sql

[ZSM4FATPP导出实例全局对象+数据] 

nohup pg_dumpall -p 5444 -c -v -f /pgbackup/upgrade/5444_alldatabase_backup.sql > /pgbackup/upgrade/5444_alldatabase_backup_exp.log 2>&1 &

[确认导出文件是否提示导出成功] 

tail /pgbackup/upgrade/5444_alldatabase_backup.sql

14. 将导出文件传输至新机器（如原先备份在bkpool则直接使用bkpool）

15. 检查并创建实例对应表空间路径

[ZSARMP实例检查表空间路径] 

cat /pgbackup/upgrade/5433_alldatabase_backup.sql | grep "CREATE TABLESPACE"

[根据获取内容创建表空间] 

mkdir -p /pgdata/ZSARMP_tbs/airflow
mkdir -p /pgdata/ZSARMP_tbs/ITDOCP
mkdir -p /pgdata/ZSARMP_tbs/keycloak
mkdir -p /pgdata/ZSARMP_tbs/SMPARK

[ZSM0GPSP实例检查表空间路径] 

cat /pgbackup/upgrade/5440_alldatabase_backup.sql | grep "CREATE TABLESPACE"

[根据获取内容创建表空间] 

mkdir -p /pgdata/ZSM0GPSP_tbs/dds_prd
mkdir -p /pgdata/ZSM0GPSP_tbs/M360GPSP
mkdir -p /pgdata/ZSM0GPSP_tbs/SMCONTAINERP
mkdir -p /pgdata/ZSM0GPSP_tbs/spim_prd
mkdir -p /pgdata/ZSM0GPSP_tbs/WZSWHGR

[ZSM1PARKP实例检查表空间路径] 

cat /pgbackup/upgrade/5441_alldatabase_backup.sql | grep "CREATE TABLESPACE"

[根据获取内容创建表空间] 

mkdir -p /pgdata/ZSM1PARKP_tbs/DDMP
mkdir -p /pgdata/ZSM1PARKP_tbs/EHSP
mkdir -p /pgdata/ZSM1PARKP_tbs/SECUREP

[ZSM2TRANP实例检查表空间路径] 

cat /pgbackup/upgrade/5442_alldatabase_backup.sql | grep "CREATE TABLESPACE"

[根据获取内容创建表空间] 

mkdir -p /pgdata/ZSM2TRANP_tbs/ZSAEOP
mkdir -p /pgdata/ZSM2TRANP_tbs/ZSCUSTOMSP
mkdir -p /pgdata/ZSM2TRANP_tbs/ZSEXPORTP
mkdir -p /pgdata/ZSM2TRANP_tbs/ZSIMPORTP
mkdir -p /pgdata/ZSM2TRANP_tbs/ZSSTORAGEP

[ZSM3PCBAP实例检查表空间路径] 

cat /pgbackup/upgrade/5443_alldatabase_backup.sql | grep "CREATE TABLESPACE"

[根据获取内容创建表空间] 

mkdir -p /pgdata/ZSM3PCBAP_tbs/ZSMESP

[ZSM4FATPP实例检查表空间路径] 

cat /pgbackup/upgrade/5444_alldatabase_backup.sql | grep "CREATE TABLESPACE"

[根据获取内容创建表空间] 

mkdir -p /pgdata/ZSM4FATPP_tbs/ZSFACILITYP
mkdir -p /pgdata/ZSM4FATPP_tbs/ZSMESP

16. 导入全局对象+数据并修改用户密码（用户密码请找用户提供并修改）

[ZSARMP导入全局对象+数据] 

nohup psql -p 5433 -U postgres -d postgres -f /pgbackup/upgrade/5433_alldatabase_backup.sql > /pgbackup/upgrade/5433_alldatabase_backup_imp.log 2>&1 &

[检查导出日志是否有相关报错信息]（如仅提示删除操作报错does not exist可忽略相关报错） 

cat /pgbackup/upgrade/5433_alldatabase_backup_imp.log | grep ERROR

[检查数据库默认加密协议] 

psql -p 5433 -c "show password_encryption;"

[更改用户密码为scram-sha-256加密协议] 

ALTER ROLE postgres PASSWORD 'W2$XXX';

[ZSM0GPSP导入全局对象+数据] 

nohup psql -p 5440 -U postgres -d postgres -f /pgbackup/upgrade/5440_alldatabase_backup.sql > /pgbackup/upgrade/5440_alldatabase_backup_imp.log 2>&1 &

[检查导出日志是否有相关报错信息]（如提示postgres、postgres_exporter用户已存在可忽略相关报错） 

cat /pgbackup/upgrade/5440_alldatabase_backup_imp.log | grep ERROR

[检查数据库默认加密协议] 

psql -p 5440 -c "show password_encryption;"

[更改用户密码为scram-sha-256加密协议] 

ALTER ROLE postgres PASSWORD 'W2$XXX';

[ZSM1PARKP导入全局对象+数据] 

nohup psql -p 5441 -U postgres -d postgres -f /pgbackup/upgrade/5441_alldatabase_backup.sql > /pgbackup/upgrade/5441_alldatabase_backup_imp.log 2>&1 &

[检查导出日志是否有相关报错信息]（如提示postgres、postgres_exporter用户已存在可忽略相关报错） 

cat /pgbackup/upgrade/5441_alldatabase_backup_imp.log | grep ERROR

[检查数据库默认加密协议] 

psql -p 5441 -c "show password_encryption;"

[更改用户密码为scram-sha-256加密协议] 

ALTER ROLE postgres PASSWORD 'W2$XXX';

[ZSM2TRANP导入全局对象+数据] 

nohup psql -p 5442 -U postgres -d postgres -f /pgbackup/upgrade/5442_alldatabase_backup.sql > /pgbackup/upgrade/5442_alldatabase_backup_imp.log 2>&1 &

[检查导出日志是否有相关报错信息]（如提示postgres、postgres_exporter用户已存在可忽略相关报错） 

cat /pgbackup/upgrade/5442_alldatabase_backup_imp.log | grep ERROR

[检查数据库默认加密协议] 

psql -p 5442 -c "show password_encryption;"

[更改用户密码为scram-sha-256加密协议] 

ALTER ROLE postgres PASSWORD 'W2$XXX';

[ZSM3PCBAP导入全局对象+数据] 

nohup psql -p 5443 -U postgres -d postgres -f /pgbackup/upgrade/5443_alldatabase_backup.sql > /pgbackup/upgrade/5443_alldatabase_backup_imp.log 2>&1 &

[检查导出日志是否有相关报错信息]（如提示postgres、postgres_exporter用户已存在可忽略相关报错） 

cat /pgbackup/upgrade/5443_alldatabase_backup_imp.log | grep ERROR

[检查数据库默认加密协议] 

psql -p 5443 -c "show password_encryption;"

[更改用户密码为scram-sha-256加密协议] 

ALTER ROLE postgres PASSWORD 'W2$XXX';

[ZSM4FATPP导入全局对象+数据] 

nohup psql -p 5444 -U postgres -d postgres -f /pgbackup/upgrade/5444_alldatabase_backup.sql > /pgbackup/upgrade/5444_alldatabase_backup_imp.log 2>&1 &

[检查导出日志是否有相关报错信息]（如提示postgres、postgres_exporter用户已存在可忽略相关报错） 

cat /pgbackup/upgrade/5444_alldatabase_backup_imp.log | grep ERROR

[检查数据库默认加密协议] 

psql -p 5444 -c "show password_encryption;"

[更改用户密码为scram-sha-256加密协议] 

ALTER ROLE postgres PASSWORD 'W2$xxx';

17. 查询对象及行数---高版本执行（做的当天执行此步）

18. 检查无误后将IP进行切换

19. 配置并测试 crontab job

20. 配置并测试监控（zabbix、promethus）