三节点 Patroni 高可用下的PostgreSQL小版本和大版本升级

原创张玉龙 2022-09-25

789

介绍

本次实验使用了一个开源工具 multissh ，multissh 是一个简单的并行 SSH 工具，可以批量的对主机通过 SSH 执行命令组合。网址https://github.com/shanghai-edu/multissh
<单机的PostgreSQL的小版本和大版本升级>参考：https://www.modb.pro/db/499478，环境类似，这里不多说废话，只展示命令。

小版本升级，13.3 升级到 13.8

当前的集群信息

[root@pgtest1 pgsql_install_2.5]# sh manager.sh status

  pid  |   state   | client_addr  | sync_priority | sync_state
-------+-----------+--------------+---------------+------------
 64277 | streaming | 192.168.0.33 |             0 | async
 64347 | streaming | 192.168.0.32 |             0 | async
(2 rows)

+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|         ENDPOINT         |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://192.168.0.33:2379 | 79fc6bf1232ecf2c |   3.5.5 |   20 kB |     false |      false |         5 |       5320 |               5320 |        |
| http://192.168.0.32:2379 | 82c4c015c577b79a |   3.5.5 |   20 kB |     false |      false |         5 |       5320 |               5320 |        |
| http://192.168.0.31:2379 | bddc24bfd8b7ba99 |   3.5.5 |   20 kB |      true |      false |         5 |       5320 |               5320 |        |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+

+---------+--------------+---------+---------+----+-----------+
| Member  | Host         | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster_5432 (7147255035430788889) -+-----------+
| pgtest1 | 192.168.0.31 | Leader  | running |  3 |           |
| pgtest2 | 192.168.0.32 | Replica | running |  3 |         0 |
| pgtest3 | 192.168.0.33 | Replica | running |  3 |         0 |
+---------+--------------+---------+---------+----+-----------+

当前的数据库信息

[postgres@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-44), 64-bit
(1 row)

postgres=# \dx
                                                       List of installed extensions
        Name        | Version |   Schema   |                                          Description
--------------------+---------+------------+-----------------------------------------------------------------------------------------------
 orafce             | 3.24    | public     | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
 pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
       sysdate       |              now              |       current_timestamp       |        clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
 2022-09-25 18:40:54 | 2022-09-25 18:40:53.851206+08 | 2022-09-25 18:40:53.851206+08 | 2022-09-25 18:40:53.853862+08
(1 row)

集群所有节点编译安装新版本的软件

./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "mkdir -p /enmo/app/pg13/13.8"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "tar -xzvf /tmp/enmo/soft/postgresql-13.8.tar.gz -C /enmo/soft"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "cd /enmo/soft/postgresql-13.8 && ./configure --prefix=/enmo/app/pg13/13.8 && make -j 8 && make install"

集群所有节点在新版本上安装插件

./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "cd /enmo/soft/postgresql-13.8/contrib && make install PG_CONFIG=/enmo/app/pg13/13.8/bin/pg_config"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "cp /enmo/soft/postgresql-13.3/contrib/orafce-VERSION_*.tar.gz /enmo/soft/postgresql-13.8/contrib"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "cd /enmo/soft/postgresql-13.8/contrib/ && tar -xvf orafce-VERSION_*.tar.gz"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "cd /enmo/soft/postgresql-13.8/contrib/orafce-VERSION_* && make install PG_CONFIG=/enmo/app/pg13/13.8/bin/pg_config"

停止一个备库节点的 patroni 服务，数据库也会自动停止

[root@pgtest1 pgsql_install_2.5]# ./multissh -hosts 192.168.0.33 -t 600 -l -u root -cmds "systemctl stop patroni"
[root@pgtest1 pgsql_install_2.5]# sh manager.sh status
  pid  |   state   | client_addr  | sync_priority | sync_state
-------+-----------+--------------+---------------+------------
 64347 | streaming | 192.168.0.32 |             0 | async
(1 row)

+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|         ENDPOINT         |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://192.168.0.33:2379 | 79fc6bf1232ecf2c |   3.5.5 |   20 kB |     false |      false |         5 |       7295 |               7295 |        |
| http://192.168.0.32:2379 | 82c4c015c577b79a |   3.5.5 |   20 kB |     false |      false |         5 |       7295 |               7295 |        |
| http://192.168.0.31:2379 | bddc24bfd8b7ba99 |   3.5.5 |   20 kB |      true |      false |         5 |       7295 |               7295 |        |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+

+---------+--------------+---------+---------+----+-----------+
| Member  | Host         | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster_5432 (7147255035430788889) -+-----------+
| pgtest1 | 192.168.0.31 | Leader  | running |  3 |           |
| pgtest2 | 192.168.0.32 | Replica | running |  3 |         0 |
+---------+--------------+---------+---------+----+-----------+

修改软连接指向新版本

./multissh -hosts 192.168.0.33 -t 600 -l -u root -cmds "rm -f /enmo/app/pgsql && ln -s /enmo/app/pg13/13.8 /enmo/app/pgsql"

注意环境变量是否指向新版本

[root@pgtest3 ~]# 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
export PATH=$PGHOME/bin:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib:/usr/local/libevent/lib:$LD_LIBRARY_PATH
# for etcd
export PATH=/enmo/app/etcd:$PATH
# patroni
alias patronictl='patronictl -c /enmo/app/patroni/patroni_config.yml'
# for haproxy
export PATH=/enmo/app/haproxy/sbin:$PATH
# for keepalived
export PATH=/enmo/app/keepalived/sbin:$PATH

[root@pgtest3 ~]# source /etc/profile

[root@pgtest3 postgresql-13.8]# 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"
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/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@pgtest3 ~]# systemctl daemon-reload

启动 patroni 服务，数据库也会自动启动

[root@pgtest1 pgsql_install_2.5]# ./multissh -hosts 192.168.0.33 -t 600 -l -u root -cmds "systemctl start patroni"
[root@pgtest1 pgsql_install_2.5]# sh manager.sh status
  pid  |   state   | client_addr  | sync_priority | sync_state
-------+-----------+--------------+---------------+------------
 92768 | streaming | 192.168.0.33 |             0 | async
 64347 | streaming | 192.168.0.32 |             0 | async
(2 rows)

+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|         ENDPOINT         |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://192.168.0.33:2379 | 79fc6bf1232ecf2c |   3.5.5 |   20 kB |     false |      false |         5 |       8087 |               8087 |        |
| http://192.168.0.32:2379 | 82c4c015c577b79a |   3.5.5 |   20 kB |     false |      false |         5 |       8087 |               8087 |        |
| http://192.168.0.31:2379 | bddc24bfd8b7ba99 |   3.5.5 |   20 kB |      true |      false |         5 |       8087 |               8087 |        |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+

+---------+--------------+---------+---------+----+-----------+
| Member  | Host         | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster_5432 (7147255035430788889) -+-----------+
| pgtest1 | 192.168.0.31 | Leader  | running |  3 |           |
| pgtest2 | 192.168.0.32 | Replica | running |  3 |         0 |
| pgtest3 | 192.168.0.33 | Replica | running |  3 |         0 |
+---------+--------------+---------+---------+----+-----------+

[root@pgtest3 ~]# 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-44), 64-bit
(1 row)

postgres=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
       sysdate       |              now              |       current_timestamp       |        clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
 2022-09-25 18:56:45 | 2022-09-25 18:56:44.532273+08 | 2022-09-25 18:56:44.532273+08 | 2022-09-25 18:56:44.533899+08
(1 row)

同理升级另一个备机 pgtest2
升级 Leader 节点 pgtest1，先将 Leader switchover 到 pgtest2 ，再进行升级

[root@pgtest1 pgsql_install_2.5]# patronictl switchover
Master [pgtest1]:
Candidate ['pgtest2', 'pgtest3'] []: pgtest2
When should the switchover take place (e.g. 2022-09-25T20:08 )  [now]:
Current cluster topology
+---------+--------------+---------+---------+----+-----------+
| Member  | Host         | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster_5432 (7147255035430788889) -+-----------+
| pgtest1 | 192.168.0.31 | Leader  | running |  3 |           |
| pgtest2 | 192.168.0.32 | Replica | running |  3 |         0 |
| pgtest3 | 192.168.0.33 | Replica | running |  3 |         0 |
+---------+--------------+---------+---------+----+-----------+
Are you sure you want to switchover cluster pg_cluster_5432, demoting current master pgtest1? [y/N]: y
2022-09-25 19:09:12.39854 Successfully switched over to "pgtest2"
+---------+--------------+---------+---------+----+-----------+
| Member  | Host         | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster_5432 (7147255035430788889) -+-----------+
| pgtest1 | 192.168.0.31 | Replica | stopped |    |   unknown |
| pgtest2 | 192.168.0.32 | Leader  | running |  3 |           |
| pgtest3 | 192.168.0.33 | Replica | running |  3 |         0 |
+---------+--------------+---------+---------+----+-----------+

[root@pgtest1 pgsql_install_2.5]# sh manager.sh status
  pid  |   state   | client_addr  | sync_priority | sync_state
-------+-----------+--------------+---------------+------------
 82883 | streaming | 192.168.0.33 |             0 | async
 82887 | streaming | 192.168.0.31 |             0 | async
(2 rows)

+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|         ENDPOINT         |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://192.168.0.33:2379 | 79fc6bf1232ecf2c |   3.5.5 |   20 kB |     false |      false |         5 |       9845 |               9845 |        |
| http://192.168.0.32:2379 | 82c4c015c577b79a |   3.5.5 |   20 kB |     false |      false |         5 |       9845 |               9845 |        |
| http://192.168.0.31:2379 | bddc24bfd8b7ba99 |   3.5.5 |   20 kB |      true |      false |         5 |       9845 |               9845 |        |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+

+---------+--------------+---------+---------+----+-----------+
| Member  | Host         | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster_5432 (7147255035430788889) -+-----------+
| pgtest1 | 192.168.0.31 | Replica | running |  4 |         0 |
| pgtest2 | 192.168.0.32 | Leader  | running |  4 |           |
| pgtest3 | 192.168.0.33 | Replica | running |  4 |         0 |
+---------+--------------+---------+---------+----+-----------+

[root@pgtest2 orafce-VERSION_3_24_4]# psql "host=192.168.0.34 port=5000 user=postgres dbname=postgres password=Enmo@123" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.0.32     | f
(1 row)

使用相同的升级方式升级 pgtest1

大版本升级，13.3 升级到 14.5

参考文档：
pg_upgrade 参考资料： https://www.postgresql.org/docs/current/pgupgrade.html
Patroni 关于数据库大版本升级的参考资料：https://patroni.readthedocs.io/en/latest/existing_data.html#major-upgrade-of-postgresql-version
当前的集群信息

[root@pgtest1 pgsql_install_2.5]# sh manager.sh status
  pid  |   state   | client_addr  | sync_priority | sync_state
-------+-----------+--------------+---------------+------------
 45590 | streaming | 192.168.0.32 |             0 | async
 45700 | streaming | 192.168.0.33 |             0 | async
(2 rows)

+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|         ENDPOINT         |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://192.168.0.33:2379 | 79fc6bf1232ecf2c |   3.5.5 |   20 kB |     false |      false |         2 |       1730 |               1730 |        |
| http://192.168.0.32:2379 | 82c4c015c577b79a |   3.5.5 |   20 kB |     false |      false |         2 |       1730 |               1730 |        |
| http://192.168.0.31:2379 | bddc24bfd8b7ba99 |   3.5.5 |   20 kB |      true |      false |         2 |       1730 |               1730 |        |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+

+---------+--------------+---------+---------+----+-----------+-----------------+
| Member  | Host         | Role    | State   | TL | Lag in MB | Pending restart |
+ Cluster: pg_cluster_5432 (7147496124471667012) -+-----------+-----------------+
| pgtest1 | 192.168.0.31 | Leader  | running |  3 |           | *               |
| pgtest2 | 192.168.0.32 | Replica | running |  3 |         0 | *               |
| pgtest3 | 192.168.0.33 | Replica | running |  3 |         0 | *               |
+---------+--------------+---------+---------+----+-----------+-----------------+

所有节点编译安装新版本软件

./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "mkdir -p /enmo/app/pg14/14.5"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "tar -xzvf /tmp/enmo/soft/postgresql-14.5.tar.gz -C /enmo/soft"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "cd /enmo/soft/postgresql-14.5 && ./configure --prefix=/enmo/app/pg14/14.5 && make -j 8 && make install"

所有节点使用 initdb 初始化新集群，无需启动新集群

echo "Enmo@123" > /home/postgres/pwfile
./multissh -hosts 192.168.0.32,192.168.0.33 -l -u root -unload -localfile /home/postgres/pwfile -destdir /home/postgres
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -l -u postgres -p postgres -setupkey
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u postgres -cmds "/enmo/app/pg14/14.5/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

./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "cd /enmo/soft/postgresql-14.5/contrib && make install PG_CONFIG=/enmo/app/pg14/14.5/bin/pg_config"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "cp /enmo/soft/postgresql-13.3/contrib/orafce-VERSION_*.tar.gz /enmo/soft/postgresql-14.5/contrib"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "cd /enmo/soft/postgresql-14.5/contrib/ && tar -xvf orafce-VERSION_*.tar.gz"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "cd /enmo/soft/postgresql-14.5/contrib/orafce-VERSION_* && make install PG_CONFIG=/enmo/app/pg14/14.5/bin/pg_config"

主节点运行 pg_upgrade 的 check 命令

[postgres@pgtest1 ~]$ /enmo/app/pg14/14.5/bin/pg_upgrade --check --old-datadir /enmo/pgdata_13_5432 --new-datadir /enmo/pgdata_14_5432 --old-bindir /enmo/app/pg13/13.3/bin --new-bindir /enmo/app/pg14/14.5/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 incompatible polymorphic functions             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*

所有节点停止 patroni 服务，数据库也会自动停止

# 先停备库再停主库
./multissh -hosts 192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "systemctl stop patroni"
./multissh -hosts 192.168.0.31 -t 600 -l -u root -cmds "systemctl stop patroni"

[root@pgtest1 pgsql_install_2.5]# sh manager.sh status
psql: error: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|         ENDPOINT         |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://192.168.0.33:2379 | 79fc6bf1232ecf2c |   3.5.5 |   20 kB |     false |      false |         2 |       6488 |               6488 |        |
| http://192.168.0.32:2379 | 82c4c015c577b79a |   3.5.5 |   20 kB |     false |      false |         2 |       6488 |               6488 |        |
| http://192.168.0.31:2379 | bddc24bfd8b7ba99 |   3.5.5 |   20 kB |      true |      false |         2 |       6488 |               6488 |        |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
+---------+--------------+---------+---------+----+-----------+-----------------+
| Member  | Host         | Role    | State   | TL | Lag in MB | Pending restart |
+ Cluster: pg_cluster_5432 (7147496124471667012) -+-----------+-----------------+
| pgtest1 | 192.168.0.31 | Replica | stopped |    |   unknown | *               |
| pgtest2 | 192.168.0.32 | Replica | stopped |    |   unknown | *               |
| pgtest3 | 192.168.0.33 | Replica | stopped |    |   unknown | *               |
+---------+--------------+---------+---------+----+-----------+-----------------+

这里使用 --link 方式升级，强烈建议备份数据目录
主节点上执行 pg_upgrade 使用 --link 方式升级

[postgres@pgtest1 ~]$ /enmo/app/pg14/14.5/bin/pg_upgrade --link --old-datadir /enmo/pgdata_13_5432 --new-datadir /enmo/pgdata_14_5432 --old-bindir /enmo/app/pg13/13.3/bin --new-bindir /enmo/app/pg14/14.5/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
Checking for incompatible polymorphic functions             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
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /enmo/pgdata_13_5432/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Linking 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                              notice

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.


Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /enmo/app/pg14/14.5/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster''s data files:
    ./delete_old_cluster.sh

修改软连接指向新版本

./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "rm -f /enmo/app/pgsql && ln -s /enmo/app/pg14/14.5 /enmo/app/pgsql"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "rm -f /enmo/pgdata && ln -s /enmo/pgdata_14_5432 /enmo/pgdata"
./multissh -hosts 192.168.0.31,192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "rm -f /enmo/pgwal && ln -s /enmo/pgwal_14_5432 /enmo/pgwal"

注意环境变量是否指向新版本
从 DCS 中删除集群信息

[root@pgtest1 pgsql_install_2.5]# patronictl remove pg_cluster_5432
+--------+------+------+-------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+ Cluster: pg_cluster_5432 (7147496124471667012)+
+--------+------+------+-------+----+-----------+
Please confirm the cluster name to remove: pg_cluster_5432
You are about to remove all information in DCS for pg_cluster_5432, please type: "Yes I am aware": Yes I am aware

主库启动 patroni 服务，数据库也会自动启动

[root@pgtest1 ~]# systemctl start patroni

+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|         ENDPOINT         |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://192.168.0.33:2379 | 79fc6bf1232ecf2c |   3.5.5 |   20 kB |     false |      false |         2 |       6923 |               6923 |        |
| http://192.168.0.32:2379 | 82c4c015c577b79a |   3.5.5 |   20 kB |     false |      false |         2 |       6923 |               6923 |        |
| http://192.168.0.31:2379 | bddc24bfd8b7ba99 |   3.5.5 |   20 kB |      true |      false |         2 |       6923 |               6923 |        |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+

+---------+--------------+--------+---------+----+-----------+-----------------+
| Member  | Host         | Role   | State   | TL | Lag in MB | Pending restart |
+ Cluster: pg_cluster_5432 (7147505124391031736) +-----------+-----------------+
| pgtest1 | 192.168.0.31 | Leader | running |  2 |           | *               |
+---------+--------------+--------+---------+----+-----------+-----------------+

备库的升级有两种方式，一种是使用 rsync，参考文档：https://www.postgresql.org/docs/current/pgupgrade.html，另一种是使用 Patroni 自动同步数据，这里选用第二种方式。
清除备库上数据目录下的文件

./multissh -hosts 192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "rm -rf /enmo/pgdata_14_5432/* && rm -rf /enmo/pgwal_14_5432/*"

备库启动，patroni 自动同步数据

./multissh -hosts 192.168.0.32,192.168.0.33 -t 600 -l -u root -cmds "systemctl start patroni"

升级后操作

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.


Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /enmo/app/pg14/14.5/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster''s data files:
    ./delete_old_cluster.sh

升级后检查

[root@pgtest1 pgsql_install_2.5]# sh manager.sh status
  pid   |   state   | client_addr  | sync_priority | sync_state
--------+-----------+--------------+---------------+------------
 121943 | streaming | 192.168.0.32 |             0 | async
 123075 | streaming | 192.168.0.33 |             0 | async
(2 rows)

+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|         ENDPOINT         |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://192.168.0.33:2379 | 79fc6bf1232ecf2c |   3.5.5 |   20 kB |     false |      false |         2 |       9635 |               9635 |        |
| http://192.168.0.32:2379 | 82c4c015c577b79a |   3.5.5 |   20 kB |     false |      false |         2 |       9635 |               9635 |        |
| http://192.168.0.31:2379 | bddc24bfd8b7ba99 |   3.5.5 |   20 kB |      true |      false |         2 |       9635 |               9635 |        |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+

+---------+--------------+---------+---------+----+-----------+-----------------+
| Member  | Host         | Role    | State   | TL | Lag in MB | Pending restart |
+ Cluster: pg_cluster_5432 (7147505124391031736) -+-----------+-----------------+
| pgtest1 | 192.168.0.31 | Leader  | running |  2 |           | *               |
| pgtest2 | 192.168.0.32 | Replica | running |  2 |         0 | *               |
| pgtest3 | 192.168.0.33 | Replica | running |  2 |         0 | *               |
+---------+--------------+---------+---------+----+-----------+-----------------+

[postgres@pgtest1 ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# \dx
                                                       List of installed extensions
        Name        | Version |   Schema   |                                          Description
--------------------+---------+------------+-----------------------------------------------------------------------------------------------
 orafce             | 3.24    | public     | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
 pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
       sysdate       |              now              |       current_timestamp       |        clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
 2022-09-26 03:25:03 | 2022-09-26 11:25:03.375762+08 | 2022-09-26 11:25:03.375762+08 | 2022-09-26 11:25:03.377383+08
(1 row)

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

三节点 Patroni 高可用下的PostgreSQL小版本和大版本升级

介绍

小版本升级，13.3 升级到 13.8

大版本升级，13.3 升级到 14.5

评论