# 1. 安装PostgreSQL 18
# 下载并安装PostgreSQL 18
$ sudo yum install postgresql18-server postgresql18-contrib

# 2. 停止PostgreSQL 17服务
$ sudo systemctl stop postgresql-17

# 3. 备份数据库
$ pg_dumpall -h fgedu.localhost -U postgres -p 5432 > /backup/full_backup.sql

# 4. 检查pg_upgrade工具
$ /postgresql/fgapp/bin/pg_upgrade –help

# 执行pg_upgrade
$ sudo -u postgres /postgresql/fgapp/bin/pg_upgrade \
–old-datadir=/postgresql/data17 \
–new-datadir=/postgresql/data18 \
–old-bindir=/usr/pgsql-17/bin \
–new-bindir=/postgresql/fgapp/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 reg* system OID user data types 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 presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions 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 on 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
Copying user relation files ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok

Upgrade Complete
—————-
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, run:
/postgresql/fgapp/bin/vacuumdb –all –analyze-in-stages

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

# 启动PostgreSQL 18服务
$ sudo systemctl start postgresql-18

# 启用自启动
$ sudo systemctl enable postgresql-18

# 检查服务状态
$ sudo systemctl status postgresql-18

# 输出示例
● postgresql-18.service – PostgreSQL 18 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-18.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2026-04-07 10:00:00 CST; 1min ago
Process: 12345 ExecStart=/postgresql/fgapp/bin/postmaster -D /postgresql/data18 (code=exited, status=0/SUCCESS)
Main PID: 12345 (postmaster)
CGroup: /system.slice/postgresql-18.service
├─12345 /postgresql/fgapp/bin/postmaster -D /postgresql/data18
├─12346 postgres: logger process
├─12348 postgres: checkpointer process
├─12349 postgres: writer process
├─12350 postgres: wal writer process
├─12351 postgres: autovacuum launcher process
└─12352 postgres: stats collector process

Apr 07 10:00:00 fgedu.net.cn systemd[1]: Started PostgreSQL 18 database server.

# 1. 安装PostgreSQL 18
$ sudo yum install postgresql18-server postgresql18-contrib

# 2. 初始化PostgreSQL 18数据库
$ sudo -u postgres /postgresql/fgapp/bin/initdb -D /postgresql/data18

# 3. 配置PostgreSQL 18
$ sudo vi /postgresql/data18/postgresql.conf
# 修改以下参数
# listen_addresses = ‘fgedu.localhost’ 改为 listen_addresses = ‘*’
# port = 5432 改为 port = 5433

# 4. 配置pg_hba.conf
$ sudo vi /postgresql/data18/pg_hba.conf
# 添加以下行
host replication postgres 127.0.0.1/32 trust

# 5. 启动PostgreSQL 18服务
$ sudo systemctl start postgresql-18

# 在PostgreSQL 17上创建发布
$ psql -h fgedu.localhost -U postgres -p 5432

postgres=# CREATE PUBLICATION pg_upgrade_pub FOR ALL TABLES;
CREATE PUBLICATION

# 在PostgreSQL 18上创建订阅
$ psql -h fgedu.localhost -U postgres -p 5433

postgres=# CREATE SUBSCRIPTION pg_upgrade_sub
postgres-# CONNECTION ‘host=fgedu.localhost port=5432 dbname=postgres user=postgres’
postgres-# PUBLICATION pg_upgrade_pub;
CREATE SUBSCRIPTION

# 检查复制状态
postgres=# SELECT * FROM pg_stat_subscription;

# 1. 停止应用程序

# 2. 等待复制完成
$ psql -h fgedu.localhost -U postgres -p 5433
postgres=# SELECT * FROM pg_stat_subscription WHERE status = ‘streaming’;

# 3. 停止PostgreSQL 17服务
$ sudo systemctl stop postgresql-17

# 4. 修改PostgreSQL 18端口为5432
$ sudo vi /postgresql/data18/postgresql.conf
# port = 5433 改为 port = 5432

# 5. 重启PostgreSQL 18服务
$ sudo systemctl restart postgresql-18

# 6. 启动应用程序

# 1. 分析数据库
$ sudo -u postgres /postgresql/fgapp/bin/vacuumdb –all –analyze-in-stages

# 2. 检查数据库状态
$ psql -h fgedu.localhost -U postgres
postgres=# SELECT version();

# 3. 检查扩展插件
postgres=# SELECT * FROM pg_extension;

# 4. 测试应用程序

# 5. 清理旧版本
$ sudo -u postgres ./delete_old_cluster.sh
$ sudo yum remove postgresql17-server postgresql17-contrib