PostgreSQL 9.6 升级 13 步骤
自部署单实例的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
后启动新实例即可