自部署单实例的PostgreSQL 9.6升级13并迁移数据

环境

  • 系统: CentOS 7.6

完整步骤

1. 安装13

访问官网有完整的脚本说明: 官网

Lang: bash 
1sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2sudo yum install -y postgresql13-server
3sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
4sudo systemctl enable postgresql-13
5sudo systemctl start postgresql-13

2. 配置

  • 停止9.6服务: systemctl stop postgresql-9.6
  • 停止13服务: systemctl stop postgresql-13
  • 修改两个服务的 pg_hba.conf 文件
Lang: conf 
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
  • 切换到 postgres 用户: su - postgres

3. 升级

postgres用户下执行, 详细说明见:官网
说明:

  • –link: 使用连接模式
  • -b: 旧实例bin目录
  • -B: 新实例bin目录
  • -d: 旧实例数据目录
  • -D: 新实例数据目录
  • -p: 旧实例端口
  • -P: 新实例端口
  • -U: 用户(不支持用户密码,所以需要将上面修改pg_hba.conf的步骤)
  • –check: 检查模式, 只检查不升级
  • 注意: pg_upgrade一定要使用新版本bin目录下的, 而不能使用旧版本目录下的
Lang: bash 
 1/usr/pgsql-13/bin/pg_upgrade \
 2--link \
 3-b /usr/pgsql-9.6/bin \
 4-B /usr/pgsql-13/bin \
 5-d /var/lib/pgsql/9.6/data \
 6-D /var/lib/pgsql/13/data \
 7-p 7395 \
 8-P 7396 \
 9-U postgres \
10--check

输出

Lang: bash 
 1Performing Consistency Checks
 2-----------------------------
 3Checking cluster versions                                   ok
 4Checking database user is the install user                  ok
 5Checking database connection settings                       ok
 6Checking for prepared transactions                          ok
 7Checking for system-defined composite types in user tables  ok
 8Checking for reg* data types in user tables                 ok
 9Checking for contrib/isn with bigint-passing mismatch       ok
10Checking for tables WITH OIDS                               ok
11Checking for invalid "sql_identifier" user columns          ok
12Checking for invalid "unknown" user columns                 ok
13Checking for hash indexes                                   ok
14Checking for presence of required libraries                 ok
15Checking database user is the install user                  ok
16Checking for prepared transactions                          ok
17Checking for new cluster tablespace directories             ok
18
19*Clusters are compatible*

开始升级

Lang: bash 
1/usr/pgsql-13/bin/pg_upgrade \
2--link \
3-b /usr/pgsql-9.6/bin \
4-B /usr/pgsql-13/bin \
5-d /var/lib/pgsql/9.6/data \
6-D /var/lib/pgsql/13/data \
7-p 7395 \
8-P 7396 \
9-U postgres

显示如下时候表示升级成功,数据也全部转移过去

Lang: bash 
1Upgrade Complete
2----------------
3Optimizer statistics are not transferred by pg_upgrade so,
4once you start the new server, consider running:
5    ./analyze_new_cluster.sh
6
7Running this script will delete the old cluster's data files:
8    ./delete_old_cluster.sh

修改 pg_hba.conf 后启动新实例即可