使用pg_upgrade 将PG从13升级到14版本
Tags： PGPostgreSQL升级
👉 本文共约1894个字，系统预计阅读时间或需8分钟。
简介
pg_upgrade 工具可以支持 PostgreSQL 跨版本的就地升级，不需要执行导出和导入操作。pg_upgrade 可以支持 PostgreSQL 8.4.X 到最新版本的升级，包括快照版本和测试版本。
pg_upgrade 提供了升级前的兼容性检查（-c 或者 —check 选项）功能，可以发现插件、数据类型不兼容等问题。如果指定了—link 选项，新版本服务可以直接使用原有的数据库文件而不需要执行复制，通常可以在几分钟内完成升级操作。
本次文档主要记录的是版本13.5到14.1的升级演示
01 PG 13环境检查
1、环境变量检查
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [postgres@lyp ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH export PGHOME=/opt/pgsql13.5 export PGDATA=/home/postgres/pgdata export PGUSER=postgres export PGPORT=5433 export PATH=$HOME/bin:$PGHOME/bin:$PATH export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH [postgres@lyp ~]$ |
2、数据库版本检查
1 2 3 4 5 6 7 8 9 10 11 | [postgres@lyp ~]$ psql psql (13.5) Type "help" for help. postgres=# select version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 13.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit (1 row) postgres=# |
3、模拟数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | postgres=# \d List of relations Schema | Name | Type | Owner --------+-------------------+-------+---------- public | emp | table | postgres (1 rows) postgres=# select count(*) from emp; count --------- 1100000 (1 row) postgres=# |
02 PG 14环境安装
1、解压安装包
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [root@lyp ~]# tar -zxvf postgresql-14.1.tar.gz [root@lyp ~]# ll postgresql-14.1 total 756 -rw-r--r--. 1 1107 1107 490 Nov 9 05:58 aclocal.m4 drwxrwxrwx. 2 1107 1107 4096 Nov 9 06:10 config -rwxr-xr-x. 1 1107 1107 580807 Nov 9 05:58 configure -rw-r--r--. 1 1107 1107 83288 Nov 9 05:58 configure.ac drwxrwxrwx. 58 1107 1107 4096 Nov 9 06:10 contrib -rw-r--r--. 1 1107 1107 1192 Nov 9 05:58 COPYRIGHT drwxrwxrwx. 3 1107 1107 87 Nov 9 06:10 doc -rw-r--r--. 1 1107 1107 4259 Nov 9 05:58 GNUmakefile.in -rw-r--r--. 1 1107 1107 277 Nov 9 05:58 HISTORY -rw-r--r--. 1 1107 1107 63953 Nov 9 06:11 INSTALL -rw-r--r--. 1 1107 1107 1665 Nov 9 05:58 Makefile -rw-r--r--. 1 1107 1107 1213 Nov 9 05:58 README drwxrwxrwx. 16 1107 1107 4096 Nov 9 06:11 src [root@lyp ~]# [root@lyp ~]# mv postgresql-14.1 /opt/pgsql14.1 [root@lyp ~]# |
2、编译安装
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [root@lyp ~]# cd /opt/pgsql14.1/ [root@lyp pgsql14.1]# ./configure --prefix=/opt/pgsql14.1 checking build system type... x86_64-pc-linux-gnu checking host system type... x86_64-pc-linux-gnu ...... config.status: linking src/include/port/linux.h to src/include/pg_config_os.h config.status: linking src/makefiles/Makefile.linux to src/Makefile.port [root@lyp pgsql14.1]# [root@lyp pgsql14.1]# gmake world gmake -C ./src/backend generated-headers gmake[1]: Entering directory `/opt/pgsql14.1/src/backend' ...... gmake[2]: Leaving directory `/opt/pgsql14.1/contrib/vacuumlo' gmake[1]: Leaving directory `/opt/pgsql14.1/contrib' [root@lyp pgsql14.1]# [root@lyp pgsql14.1]# gmake install-world gmake -C ./src/backend generated-headers gmake[1]: Entering directory `/opt/pgsql14.1/src/backend' ...... gmake[2]: Leaving directory `/opt/pgsql14.1/contrib/vacuumlo' gmake[1]: Leaving directory `/opt/pgsql14.1/contrib' [root@lyp pgsql14.1]# |
3、初始化数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | [postgres@lyp ~]$ mkdir pgdata-14 [postgres@lyp ~]$ /opt/pgsql14.1/bin/initdb -D /home/postgres/pgdata-14/ The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /home/postgres/pgdata-14 ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... PRC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: /opt/pgsql14.1/bin/pg_ctl -D /home/postgres/pgdata-14/ -l logfile start [postgres@lyp ~]$ |
4、修改目录权限
1 2 | [root@lyp ~]# chown -R postgres:postgres /opt/pgsql14.1/ [root@lyp ~]# |
03 备份数据库
利用 pg_dump/pg_dumpall 对数据库进行备份，保证升级中遇到各种问题后以便回退。
1 | [postgres@lyp ~]$ /opt/pgsql13.5/bin/pg_dumpall > pgdumpallfull |
04 升级数据库
1、升级前检查
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | [postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_upgrade \ > --old-datadir /home/postgres/pgdata/ \ > --new-datadir /home/postgres/pgdata-14/ \ > --old-bindir /opt/pgsql13.5/bin/ \ > --new-bindir /opt/pgsql14.1/bin/ \ > --check Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for system-defined composite types in user tables ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for user-defined encoding conversions ok Checking for user-defined postfix operators ok Checking for presence of required libraries fatal Your installation references loadable libraries that are missing from the new installation. You can add these libraries to the new installation, or remove the functions using them from the old installation. A list of problem libraries is in the file: loadable_libraries.txt Failure, exiting [postgres@lyp ~]$ more loadable_libraries.txt could not load library "$libdir/walminer": ERROR: could not access file "$libdir/walminer": No such file or directory In database: postgres [postgres@lyp ~]$ |
如果旧版本中安装了扩展模块，在新版本中也需要安装相应的共享对象文件或者 DLL 文件。但是不要执行CREATE EXTENSION命令，因为会从旧数据库中进行升级。另外，如果使用了任何自定义的全文搜索文件（字典、同义词、词库、停用词），也需要复制到新的数据库集群目录中。但是这里的walminer插件MAJORVERSION当前只支持‘10’,‘11’,‘12’,‘13’，所以需要删除插件。
2、删除不兼容的插件
1 2 3 4 5 6 | [postgres@lyp ~]$ /opt/pgsql13.5/bin/psql psql (13.5) Type "help" for help. postgres=# drop extension walminer ; DROP EXTENSION |
3、重新进行升级前检查
—check 表示执行升级检查，而不会真的执行升级操作。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | [postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_upgrade \ > --old-datadir /home/postgres/pgdata/ \ > --new-datadir /home/postgres/pgdata-14/ \ > --old-bindir /opt/pgsql13.5/bin/ \ > --new-bindir /opt/pgsql14.1/bin/ \ > --check Performing Consistency Checks on Old Live Server ------------------------------------------------ Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for system-defined composite types in user tables ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for user-defined encoding conversions ok Checking for user-defined postfix operators ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories ok *Clusters are compatible* [postgres@lyp ~]$ |
4、停止数据库
确定没有客户端访问之后备份数据库，然后停止旧版本的后台服务。
1 2 3 4 5 6 7 8 9 10 11 | [postgres@lyp ~]$ /opt/pgsql13.5/bin/pg_ctl stop -D /home/postgres/pgdata waiting for server to shut down....2022-02-08 02:28:38.499 CST [47025] LOG: received fast shutdown request 2022-02-08 02:28:38.499 CST [47025] LOG: aborting any active transactions 2022-02-08 02:28:38.500 CST [47034] FATAL: terminating connection due to administrator command 2022-02-08 02:28:38.500 CST [47033] FATAL: terminating connection due to administrator command 2022-02-08 02:28:38.501 CST [47025] LOG: background worker "logical replication launcher" (PID 47032) exited with exit code 1 2022-02-08 02:28:38.502 CST [47027] LOG: shutting down 2022-02-08 02:28:38.509 CST [47025] LOG: database system is shut down done server stopped [postgres@lyp ~]$ |
5、升级数据库
可以使用—link 表示将新版本的数据目录硬链接到旧版本的数据目录，而不会复制一份新的数据文件，可以快速进行升级，但回退较为麻烦。
这里我们不使用—link直接升级。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | [postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_upgrade \ > --old-datadir /home/postgres/pgdata/ \ > --new-datadir /home/postgres/pgdata-14/ \ > --old-bindir /opt/pgsql13.5/bin/ \ > --new-bindir /opt/pgsql14.1/bin/ Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for system-defined composite types in user tables ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for user-defined encoding conversions ok Checking for user-defined postfix operators ok Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade ------------------ Analyzing all rows in the new cluster ok Freezing all rows in the new cluster ok Deleting files from new pg_xact ok Copying old pg_xact to new server ok Setting oldest XID for new cluster ok Setting next transaction ID and epoch for new cluster ok Deleting files from new pg_multixact/offsets ok Copying old pg_multixact/offsets to new server ok Deleting files from new pg_multixact/members ok Copying old pg_multixact/members to new server ok Setting next multixact ID and offset for new cluster ok Resetting WAL archives ok Setting frozenxid and minmxid counters in new cluster ok Restoring global objects in the new cluster ok Restoring database schemas in the new cluster ok Copying user relation files ok Setting next OID for new cluster ok Sync data directory to disk ok Creating script to delete old cluster ok Checking for extension updates ok Upgrade Complete ---------------- Optimizer statistics are not transferred by pg_upgrade. Once you start the new server, consider running: /opt/pgsql14.1/bin/vacuumdb --all --analyze-in-stages Running this script will delete the old cluster's data files: ./delete_old_cluster.sh [postgres@lyp ~]$ |
05 更新hba及参数
将 PG 13环境配置文件 pg_hba.conf 和 postgresql.conf 等中的改动在对应的PG 14配置文件中再次进行修改。
1、修改环境变量
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [postgres@lyp ~]$ vi .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH export PGHOME=/opt/pgsql14.1 export PGDATA=/home/postgres/pgdata-14 export PGUSER=postgres export PGPORT=5433 export PATH=$HOME/bin:$PGHOME/bin:$PATH export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH [postgres@lyp ~]$ source .bash_profile [postgres@lyp ~]$ |
06 启动数据库
1 2 3 4 5 6 7 8 9 10 | [postgres@lyp ~]$ pg_ctl start waiting for server to start....2022-02-08 04:07:35.375 CST [95334] LOG: starting PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit 2022-02-08 04:07:35.376 CST [95334] LOG: listening on IPv6 address "::1", port 5433 2022-02-08 04:07:35.376 CST [95334] LOG: listening on IPv4 address "127.0.0.1", port 5433 2022-02-08 04:07:35.379 CST [95334] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2022-02-08 04:07:35.382 CST [95335] LOG: database system was shut down at 2022-02-08 04:04:46 CST 2022-02-08 04:07:35.384 CST [95334] LOG: database system is ready to accept connections done server started [postgres@lyp ~]$ |
07 更新统计信息
pg_upgrade 不会生成新版本数据库的统计信息，按提示执行命令：
1 2 3 4 5 6 7 8 9 10 11 | Optimizer statistics are not transferred by pg_upgrade. Once you start the new server, consider running: /opt/pgsql14.1/bin/vacuumdb --all --analyze-in-stages [postgres@lyp ~]$ /opt/pgsql14.1/bin/vacuumdb --all --analyze-in-stages vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target) vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target) vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets) vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets) vacuumdb: processing database "postgres": Generating default (full) optimizer statistics vacuumdb: processing database "template1": Generating default (full) optimizer statistics [postgres@lyp ~]$ |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
