📚 PostgreSQL Use pg_upgrade to upgrade(Large)¶

🔍 Prepare：¶

## OS and Software environment：

Environment name Version Download address

OS Rocky Linux 9.x Rocky Download

DB PostgreSQL 16.8 PostgreSQL Download（YUM Repository）

## Download below packages（Example PG 16.8）：

Package name Download address

postgresql16-libs-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package

postgresql16-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package

postgresql16-server-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package

postgresql16-contrib-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package

postgresql16-devel-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package

postgresql16-plpython3-16.8-1PGDG.rhel9.x86_64.rpm Click Download RPM Package

1. Check Install postgres package¶

rpm -qa | grep postgresql

2. Check our version¶

psql -V

psql -p 5435 -c "select version();"

3. Upload our package , example upload to /pgdata/source/¶

4. Install/Replace Package¶

rpm -ivh  /pgdata/source/postgresql16-libs-16.8-1PGDG.rhel9.x86_64.rpm
rpm -ivh  /pgdata/source/postgresql16-16.8-1PGDG.rhel9.x86_64.rpm
rpm -ivh  /pgdata/source/postgresql16-server-16.8-1PGDG.rhel9.x86_64.rpm
rpm -ivh  /pgdata/source/postgresql16-contrib-16.8-1PGDG.rhel9.x86_64.rpm
rpm -ivh  /pgdata/source/postgresql16-devel-16.8-1PGDG.rhel9.x86_64.rpm
rpm -ivh  /pgdata/source/postgresql16-plpython3-16.8-1PGDG.rhel9.x86_64.rpm
chown -R postgres. /usr/pgsql-16
ll /usr/pgsql-16

5. Initial Cluster(使用新版本软件路径初始化，初始化后注意需要将旧版本的参数文件postgresql.conf pg_hba.conf值填回来)¶

/pgsql/pgsql-16/bin/initdb -D /pgdata/SITEOA-16 --encoding="UTF8" --locale="en_US.UTF-8"

/pgsql/pgsql-16/bin/initdb -D /pgdata/SMARTADM-16 --encoding="UTF8" --locale="en_US.UTF-8"

/pgsql/pgsql-16/bin/initdb -D /pgdata/INFRAISMS-16 --encoding="UTF8" --locale="en_US.UTF-8"

/pgsql/pgsql-16/bin/initdb -D /pgdata/SFCSWIKI-16 --encoding="UTF8" --locale="en_US.UTF-8"

6. Upgrade check（如果预检查成功，那么可以与用户约时间进行升级）¶

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SITEOA \
--new-datadir /pgdata/SITEOA-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SMARTADM \
--new-datadir /pgdata/SMARTADM-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/INFRAISMS \
--new-datadir /pgdata/INFRAISMS-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SFCSWIKI \
--new-datadir /pgdata/SFCSWIKI-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

预检查成功不代表到时候升级一定就没有问题

7. drop schema postgres_exporter （升级当天执行此步骤，完成升级后重新设定监控）¶

drop schema postgres_exporter;

否则升级的时候大概率遇到如下报错

8. Stop Cluster（升级当天执行此步骤）¶

关机前再检查

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SITEOA \
--new-datadir /pgdata/SITEOA-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SMARTADM \
--new-datadir /pgdata/SMARTADM-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/INFRAISMS \
--new-datadir /pgdata/INFRAISMS-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SFCSWIKI \
--new-datadir /pgdata/SFCSWIKI-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

关闭数据库（升级前需关闭DB，注意使用旧版本的命令关闭DB）

/pgsql/pgsql-13/pg_ctl stop -D /pgdata/SITEOA
/pgsql/pgsql-13/pg_ctl stop -D /pgdata/SMARTADM
/pgsql/pgsql-13/pg_ctl stop -D /pgdata/INFRAISMS
/pgsql/pgsql-13/pg_ctl stop -D /pgdata/SFCSWIKI

关闭数据库后再检查

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SITEOA \
--new-datadir /pgdata/SITEOA-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SMARTADM \
--new-datadir /pgdata/SMARTADM-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/INFRAISMS \
--new-datadir /pgdata/INFRAISMS-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SFCSWIKI \
--new-datadir /pgdata/SFCSWIKI-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/ --check

9. Upgrade（正式升级，拿掉--check）¶

正式升级

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SITEOA \ #旧版本数据目录
--new-datadir /pgdata/SITEOA-16/ \ #新版本数据目录
--old-bindir /pgsql/pgsql-13/bin/ \ #旧版本软件路径
--new-bindir /pgsql/pgsql-16/bin/ #新版本软件路径

升级后动作（因自动生成的文件每个命名规则相同，故建议按照Schema去进行备份后面可以参考）

mv ./delete_old_cluster.sh ./delete_old_cluster_SITEOA.sh
mv ./update_extensions.sql ./update_extensions_SITEOA.sql
./delete_old_cluster_SITEOA.sh #执行sh之后会删除旧版本的路径，如空间资源充足情况喜下可以不执行，达到保留新旧版本两份数据
mv /pgdata/SITEOA-16 /pgdata/SITEOA #此时可以考虑将新版本的临时路径改回原路径
pg_ctl start -D /pgdata/SITEOA
psql -p 5432 -c "select version();" #检查版本
./update_extensions_SITEOA.sql #升级相关的extension
/pgsql/pgsql-16/bin/vacuumdb  -p 5432 --all --analyze-in-stages #执行统计信息收集

正式升级

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SMARTADM \
--new-datadir /pgdata/SMARTADM-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/

升级后动作

mv ./delete_old_cluster.sh ./delete_old_cluster_SMARTADM.sh
mv ./update_extensions.sql ./update_extensions_SMARTADM.sql
./delete_old_cluster_SMARTADM.sh
mv /pgdata/SMARTADM-16 /pgdata/SMARTADM
pg_ctl start -D /pgdata/SMARTADM
psql -p 5432 -c "select version();"
./update_extensions_SMARTADM.sql
/pgsql/pgsql-16/bin/vacuumdb -p 5433 --all --analyze-in-stages

正式升级

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/INFRAISMS \
--new-datadir /pgdata/INFRAISMS-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/

升级后动作

mv ./delete_old_cluster.sh ./delete_old_cluster_INFRAISMS.sh
mv ./update_extensions.sql ./update_extensions_INFRAISMS.sql
./delete_old_cluster_INFRAISMS.sh
mv /pgdata/INFRAISMS-16 /pgdata/INFRAISMS
pg_ctl start -D /pgdata/INFRAISMS
psql -p 5432 -c "select version();"
./update_extensions_INFRAISMS.sql
/pgsql/pgsql-16/bin/vacuumdb -p 5435 --all --analyze-in-stages

正式升级

/pgsql/pgsql-16/bin/pg_upgrade \
--old-datadir /pgdata/SFCSWIKI \
--new-datadir /pgdata/SFCSWIKI-16/ \
--old-bindir /pgsql/pgsql-13/bin/ \
--new-bindir /pgsql/pgsql-16/bin/

升级后动作

mv ./delete_old_cluster.sh ./delete_old_cluster_SFCSWIKI.sh
mv ./update_extensions.sql ./update_extensions_SFCSWIKI.sql
./delete_old_cluster_SFCSWIKI.sh
mv /pgdata/SFCSWIKI-16 /pgdata/SFCSWIKI
pg_ctl start -D /pgdata/SFCSWIKI
psql -p 5432 -c "select version();"
./update_extensions_SFCSWIKI.sql
/pgsql/pgsql-16/bin/vacuumdb -p 5436 --all --analyze-in-stages

10. 参考普罗米修斯SOP安装postgres_exporter¶

异常参考¶

需要关闭集群才能升级

部分extension没有升级，如不使用可以考虑移除

postgres_exporter建议删除后等待升级完成后重新建立

Environment name	Version	Download address
OS	Rocky Linux 9.x	Rocky Download
DB	PostgreSQL 16.8	PostgreSQL Download（YUM Repository）

Package name	Download address
postgresql16-libs-16.8-1PGDG.rhel9.x86_64.rpm	Click Download RPM Package
postgresql16-16.8-1PGDG.rhel9.x86_64.rpm	Click Download RPM Package
postgresql16-server-16.8-1PGDG.rhel9.x86_64.rpm	Click Download RPM Package
postgresql16-contrib-16.8-1PGDG.rhel9.x86_64.rpm	Click Download RPM Package
postgresql16-devel-16.8-1PGDG.rhel9.x86_64.rpm	Click Download RPM Package
postgresql16-plpython3-16.8-1PGDG.rhel9.x86_64.rpm	Click Download RPM Package