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

环境

  • 系统: CentOS 7.6

完整步骤

1. 安装13

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

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

2. 配置

  • 停止9.6服务: systemctl stop postgresql-9.6
  • 停止13服务: systemctl stop postgresql-13
  • 修改两个服务的 pg_hba.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目录下的, 而不能使用旧版本目录下的
/usr/pgsql-13/bin/pg_upgrade \
--link \
-b /usr/pgsql-9.6/bin \
-B /usr/pgsql-13/bin \
-d /var/lib/pgsql/9.6/data \
-D /var/lib/pgsql/13/data \
-p 7395 \
-P 7396 \
-U postgres \
--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 tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for invalid "unknown" user columns                 ok
Checking for hash indexes                                   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*

开始升级

/usr/pgsql-13/bin/pg_upgrade \
--link \
-b /usr/pgsql-9.6/bin \
-B /usr/pgsql-13/bin \
-d /var/lib/pgsql/9.6/data \
-D /var/lib/pgsql/13/data \
-p 7395 \
-P 7396 \
-U postgres

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

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

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

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