【德哥说库系列】-PostgreSQL跨版本升级

文章目录

• 前言
• • 📣 1.PG升级介绍
  • 📣 2.安装PG14.9
  • 📣 3. 9.6.24升级到14.9

前言

一种更快的方式pg_upgrade可以实现PostgreSQL版本升级，本文给予PG9升级到PG14

---

📣 1.PG升级介绍

📣 2.安装PG14.9

✨ 1.1 创建用户

✨ 1.2 创建目录

✨ 1.3 安装依赖

✨ 1.4 编译

📣 3. 9.6.24升级到14.9

✨ 3.1 设置权限

✨ 3.2 新版本初始化

✨ 3.3 检查兼容性

✨ 3.4 创建测试数据

✨ 3.5 执行升级

升级前需要关闭源库

[pg96@centos79 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

[pg96@centos79 ~]$ /pg14/postgresql/bin/pg_upgrade -k -b /pg96/postgresql/bin -B /pg14/postgresql/bin -d /pg96/pgdata -D /pg14/pgdata -p 5400 -P 5432

✨ 3.6 修改配置文件

cat >> /pg14/pgdata/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5432
unix_socket_directories='/pg14/pgdata'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF

cat > /pg14/pgdata/pg_hba.conf << EOF
# TYPE  DATABASE    USER    ADDRESS       METHOD
local     all       all                    trust
host      all       all   127.0.0.1/32     trust
host      all       all    0.0.0.0/0        md5
host   replication  all    0.0.0.0/0        md5
EOF

✨ 3.7 修改环境变量

cat >> ~/.bash_profile <<"EOF"
export LANG=en_US.UTF-8
export PS1="[\u@\h \W]\$ "
export PGPORT=5432
export PGDATA=/pg14/pgdata
export PGHOME=/pg14/postgresql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export DATE=`date +"%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
EOF

source  ~/.bash_profile

✨ 3.8 收集统计信息

✨ 3.9 校验数据

[pg96@centos79 ~]$ psql
psql (14.9)
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | postgres=CTc/postgres+
           |          |          |            |            | =c/postgres
 testdb    | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
(4 rows)

postgres=# \c testdb postgres
You are now connected to database "testdb" as user "postgres".
testdb=# \dt
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

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