PostgreSQL的小版本和大版本升级
小版本升级，13.3 升级到 13.8
- 当前环境，数据库版本数 13.3，安装了一个 orafce 插件
[root@pgtest1 ~]# psql
psql (13.3)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 13.3 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=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+-----------------------------------------------------------------------------------------------
orafce | 3.21 | public | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
postgres=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
sysdate | now | current_timestamp | clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
2022-09-21 19:16:27 | 2022-09-21 19:16:26.589251+08 | 2022-09-21 19:16:26.589251+08 | 2022-09-21 19:16:26.589344+08
(1 row)
- 编译安装新版本的软件
mkdir -p /enmo/app/pg13/13.8
tar -xzvf /root/postgresql-13.8.tar.gz -C /enmo/soft
cd /enmo/soft/postgresql-13.8
./configure --prefix=/enmo/app/pg13/13.8
make -j 8 && make install
[root@pgtest1 ~]# ll /enmo/app/pg13/13.8
total 12
drwxr-xr-x 2 root root 4096 Sep 21 19:12 bin
drwxr-xr-x 4 root root 4096 Sep 21 19:12 include
drwxr-xr-x 4 root root 4096 Sep 21 19:12 lib
drwxr-xr-x 3 root root 24 Sep 21 19:11 share
- 停止13.3的数据库，修改软连接指向新版本
systemctl stop postgres-5432.service rm -f /enmo/app/pgsql ln -s /enmo/app/pg13/13.8 /enmo/app/pgsql
- 注意环境变量是否指向新版本
[root@pgtest1 ~]# cat /etc/profile
# for PostgreSQL
export LANG=en_US.UTF-8
export PGHOME=/enmo/app/pgsql
export PGUSER=postgres
export PGPORT=5432
export PGDATA=/enmo/pgdata_13_5432
export PATH=$PGHOME/bin:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib:/usr/local/libevent/lib:$LD_LIBRARY_PATH
[root@pgtest1 ~]# source /etc/profile
[root@pgtest1 ~]# cat /usr/lib/systemd/system/postgres-5432.service
[Unit]
Description=PostgreSQL 14 database server
After=syslog.target network.target
[Service]
Type=forking
TimeoutSec=120
User=postgres
Environment="PGHOME=/enmo/app/pgsql"
Environment="PGDATA=/enmo/pgdata_13_5432"
Environment="PGPORT=5432"
Environment="LD_LIBRARY_PATH=/enmo/app/pgsql/lib:/usr/local/lib:/usr/local/lib64:/usr/lib64"
ExecStart=/bin/bash -c '${PGHOME}/bin/pg_ctl start -w -D ${PGDATA} -l /enmo/app_log/pglog_13_5432/pg_ctl_startup.log'
ExecStop=/bin/bash -c '${PGHOME}/bin/pg_ctl stop -m fast -w -D ${PGDATA}'
ExecReload=/bin/bash -c '${PGHOME}/bin/pg_ctl reload -D ${PGDATA}'
[Install]
WantedBy=multi-user.target
[root@pgtest1 ~]# systemctl daemon-reload
- 在新版本上安装插件
cd /enmo/soft/postgresql-13.8/contrib/
make install PG_CONFIG=/enmo/app/pg13/13.8/bin/pg_config
-- 安装 orafce 插件
cp /enmo/soft/postgresql-13.3/contrib/orafce-VERSION_3_21_1.tar.gz /enmo/soft/postgresql-13.8/contrib/
cd /enmo/soft/postgresql-13.8/contrib/
tar -xvf orafce-VERSION_3_21_1.tar.gz
cd orafce-VERSION_3_21_1
make install PG_CONFIG=/enmo/app/pg13/13.8/bin/pg_config
- 启动新版本数据
systemctl start postgres-5432.service
- 升级后检查
[root@pgtest1 ~]# psql
psql (13.8)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 13.8 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=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
sysdate | now | current_timestamp | clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
2022-09-21 22:15:54 | 2022-09-21 22:15:53.708798+08 | 2022-09-21 22:15:53.708798+08 | 2022-09-21 22:15:53.710245+08
(1 row)
大版本升级，13.8 升级到 14.3
- pg_upgrade 参考资料：https://www.postgresql.org/docs/current/pgupgrade.html
- 编译安装新版本软件
mkdir -p /enmo/app/pg14/14.3
tar -xzvf /root/postgresql-14.3.tar.gz -C /enmo/soft
cd /enmo/soft/postgresql-14.3
./configure --prefix=/enmo/app/pg14/14.3
make -j 8 && make install
- 使用 initdb 初始化新集群，无需启动新集群
/enmo/app/pg14/14.3/bin/initdb --pgdata=/enmo/pgdata_14_5432 --waldir=/enmo/pgwal_14_5432 --encoding=UTF8 --lc-collate=C --lc-ctype=C --allow-group-access --data-checksums --username=postgres --pwfile=/home/postgres/pwfile --wal-segsize=32
- 安装扩展插件，但是不需要执行 CREATE EXTENSION
cd /enmo/soft/postgresql-14.3/contrib/
make install PG_CONFIG=/enmo/app/pg14/14.3/bin/pg_config
cp /enmo/soft/postgresql-13.8/contrib/orafce-VERSION_3_21_1.tar.gz /enmo/soft/postgresql-14.3/contrib/
cd /enmo/soft/postgresql-14.3/contrib/
tar -xvf orafce-VERSION_3_21_1.tar.gz
cd orafce-VERSION_3_21_1
make install PG_CONFIG=/enmo/app/pg14/14.3/bin/pg_config
- 运行 pg_upgrade 的 check 命令
/enmo/app/pg14/14.3/bin/pg_upgrade --check --old-datadir /enmo/pgdata_13_5432 --new-datadir /enmo/pgdata_14_5432 --old-bindir /enmo/app/pg13/13.8/bin --new-bindir /enmo/app/pg14/14.3/bin
# 如果要使用 link 或 clone 模式，则应使用选项 --link 或 --clone 和 --check 来启用特定于模式的检查
/enmo/app/pg14/14.3/bin/pg_upgrade --check --link --old-datadir /enmo/pgdata_13_5432 --new-datadir /enmo/pgdata_14_5432 --old-bindir /enmo/app/pg13/13.8/bin --new-bindir /enmo/app/pg14/14.3/bin
/enmo/app/pg14/14.3/bin/pg_upgrade --check --clone --old-datadir /enmo/pgdata_13_5432 --new-datadir /enmo/pgdata_14_5432 --old-bindir /enmo/app/pg13/13.8/bin --new-bindir /enmo/app/pg14/14.3/bin
# 检查结果
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
could not clone file between old and new data directories: Operation not supported
Failure, exiting
- 停止13.8的数据库，修改软连接指向新版本
systemctl stop postgres-5432.service rm -f /enmo/app/pgsql ln -s /enmo/app/pg14/14.3 /enmo/app/pgsql
- 运行 pg_upgrade，有三种升级方式，根据自己的喜好选择其一
- 第一种升级方式，默认使用操作系统命令复制数据文件到新目录，这种方式需要文件系统上有足够的空间存储双份数据，如果数据量很大，复制数据文件的过程会占用较长的升级时间。
/enmo/app/pg14/14.3/bin/pg_upgrade --jobs 32 --old-datadir /enmo/pgdata_13_5432 --new-datadir /enmo/pgdata_14_5432 --old-bindir /enmo/app/pg13/13.8/bin --new-bindir /enmo/app/pg14/14.3/bin
- 第二种升级方式，使用 --link 参数，在新数据目录下做数据文件的硬链接，优点是不需要双倍空间存储数据文件，并且升级过程中不需要复制数据文件，节约升级时间，缺点是使用新版本软件启动数据库后不能再使用旧版本软件启动数据库，不利于升级后的回滚，建议做好数据库备份。
/enmo/app/pg14/14.3/bin/pg_upgrade --link --old-datadir /enmo/pgdata_13_5432 --new-datadir /enmo/pgdata_14_5432 --old-bindir /enmo/app/pg13/13.8/bin --new-bindir /enmo/app/pg14/14.3/bin
- 第三种升级方式，使用 --clone 参数，有操作系统内核版本和文件系统类型限制，本文测试支持 RHEL 8 以上的 XFS 文件系统。这种方式也需要文件系统上有足够的空间存储双份数据，但是相比于第一种方式，速度要快得多，堪比第二种方式。
- Use efficient file cloning (also known as “reflinks” on some systems) instead of copying files to the new cluster. This can result in near-instantaneous copying of the data files, giving the speed advantages of -k/–link while leaving the old cluster untouched.
- File cloning is only supported on some operating systems and file systems. If it is selected but not supported, the pg_upgrade run will error. At present, it is supported on Linux (kernel 4.5 or later) with Btrfs and XFS (on file systems created with reflink support), and on macOS with APFS.
/enmo/app/pg14/14.3/bin/pg_upgrade --clone --old-datadir /enmo/pgdata_13_5432 --new-datadir /enmo/pgdata_14_5432 --old-bindir /enmo/app/pg13/13.8/bin --new-bindir /enmo/app/pg14/14.3/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
Cloning 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:
/enmo/app/pg14/14.3/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old clusters data files:
./delete_old_cluster.sh
- 如果没使用 --link 需要复制相关文件到新数据目录
cp /enmo/pgdata_13_5432/pg_hba.conf /enmo/pgdata_14_5432 cp /enmo/pgdata_13_5432/postgresql.conf /enmo/pgdata_14_5432
- 注意环境变量是否指向新版本，PGHOME 和 PGDATA
[root@pgtest1 ~]# cat /etc/profile
# for PostgreSQL
export LANG=en_US.UTF-8
export PGHOME=/enmo/app/pgsql
export PGUSER=postgres
export PGPORT=5432
export PGDATA=/enmo/pgdata_14_5432
export PATH=$PGHOME/bin:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib:/usr/local/libevent/lib:$LD_LIBRARY_PATH
[root@pgtest1 ~]# source /etc/profile
[root@pgtest1 ~]# cat /usr/lib/systemd/system/postgres-5432.service
[Unit]
Description=PostgreSQL 14 database server
After=syslog.target network.target
[Service]
Type=forking
TimeoutSec=120
User=postgres
Environment="PGHOME=/enmo/app/pgsql"
Environment="PGDATA=/enmo/pgdata_13_5432"
Environment="PGPORT=5432"
Environment="LD_LIBRARY_PATH=/enmo/app/pgsql/lib:/usr/local/lib:/usr/local/lib64:/usr/lib64"
ExecStart=/bin/bash -c '${PGHOME}/bin/pg_ctl start -w -D ${PGDATA} -l /enmo/app_log/pglog_13_5432/pg_ctl_startup.log'
ExecStop=/bin/bash -c '${PGHOME}/bin/pg_ctl stop -m fast -w -D ${PGDATA}'
ExecReload=/bin/bash -c '${PGHOME}/bin/pg_ctl reload -D ${PGDATA}'
[Install]
WantedBy=multi-user.target
[root@pgtest1 ~]# systemctl daemon-reload
- 使用新版本软件启动数据库
systemctl start postgres-5432.service
- 升级后检查
[root@pgtest1 ~]# psql
psql (14.3)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
(1 row)
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+-----------------------------------------------------------------------------------------------
orafce | 3.21 | public | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
postgres=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
sysdate | now | current_timestamp | clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
2022-09-23 02:54:16 | 2022-09-23 10:54:15.903545+08 | 2022-09-23 10:54:15.903545+08 | 2022-09-23 10:54:15.904395+08
(1 row)
- 升级后操作
Upgrade Complete ---------------- Optimizer statistics are not transferred by pg_upgrade. Once you start the new server, consider running: /enmo/app/pg14/14.3/bin/vacuumdb --all --analyze-in-stages Running this script will delete the old clusters data files: ./delete_old_cluster.sh
免责声明：
1、本站资源由自动抓取工具收集整理于网络。
2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。
3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。
4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。
5、如果您喜欢本资源，请您支持作者，购买正版内容。
6、资源失效，请下方留言，欢迎分享资源链接
文章评论