PostgreSQL教程FG063-PG多版本共存：环境隔离与切换方法
本文档风哥主要介绍PostgreSQL数据库多版本共存相关知识，包括多版本安装配置、环境隔离方法、版本切换技巧、服务管理等内容，风哥教程参考PostgreSQL官方文档Server Setup and Operation章节，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 PostgreSQL数据库多版本共存概述
PostgreSQL数据库多版本共存是指在同一台服务器上同时运行多个不同版本的PostgreSQL数据库实例。这种部署方式在数据库版本升级测试、应用兼容性验证、开发测试环境等场景中非常常见。通过合理的目录规划、端口配置和环境变量设置，可以实现多版本的独立运行和灵活切换。更多视频教程www.fgedu.net.cn
from oracle:www.itpux.com
- 独立的安装目录
- 独立的数据目录
- 不同的服务端口
- 独立的环境变量配置
- 独立的系统服务
1.2 PostgreSQL数据库多版本共存应用场景
PostgreSQL数据库多版本共存的主要应用场景：
- 版本升级测试：在新版本上测试应用兼容性
- 开发测试环境：不同项目使用不同版本
- 数据迁移验证：验证迁移后的数据正确性
- 功能对比测试：对比不同版本的功能差异
- 培训学习环境：同时学习多个版本特性
1.3 PostgreSQL数据库多版本隔离原理
PostgreSQL数据库多版本隔离原理说明：
## 进程隔离
– 每个版本的PostgreSQL使用独立的postmaster进程
– 进程通过不同的端口区分
– 各版本进程互不干扰
## 文件隔离
– 安装目录：/postgresql/fgapp/pgsql-16、/postgresql/fgapp/pgsql-17、/postgresql/fgapp/pgsql-18
– 数据目录：/postgresql/data/pg16、/postgresql/data/pg17、/postgresql/data/pg18
– 日志目录：/postgresql/logs/pg16、/postgresql/logs/pg17、/postgresql/logs/pg18
## 网络隔离
– 端口区分：5432（PG16）、5433（PG17）、5434（PG18）
– Unix Socket区分：/tmp/.s.PGSQL.5432、/tmp/.s.PGSQL.5433
## 环境隔离
– PATH环境变量指向不同版本
– PGDATA环境变量指向不同数据目录
– PGPORT环境变量设置不同端口
Part02-生产环境规划与建议
2.1 PostgreSQL数据库多版本目录规划
生产环境PostgreSQL数据库多版本目录规划建议：
## 安装目录
PG16：/postgresql/fgapp/pgsql-16
PG17：/postgresql/fgapp/pgsql-17
PG18：/postgresql/fgapp/pgsql-18
## 数据目录
PG16：/postgresql/data/pg16
PG17：/postgresql/data/pg17
PG18：/postgresql/data/pg18
## 日志目录
PG16：/postgresql/logs/pg16
PG17：/postgresql/logs/pg17
PG18：/postgresql/logs/pg18
## 备份目录
PG16：/postgresql/backup/pg16
PG17：/postgresql/backup/pg17
PG18：/postgresql/backup/pg18
## 归档目录
PG16：/postgresql/archive/pg16
PG17：/postgresql/archive/pg17
PG18：/postgresql/archive/pg18
# 创建目录命令
mkdir -p /postgresql/fgapp
mkdir -p /postgresql/data/{pg16,pg17,pg18}
mkdir -p /postgresql/logs/{pg16,pg17,pg18}
mkdir -p /postgresql/backup/{pg16,pg17,pg18}
mkdir -p /postgresql/archive/{pg16,pg17,pg18}
2.2 PostgreSQL数据库多版本端口规划
PostgreSQL数据库多版本端口规划建议：
## 标准端口规划
PG16：5432（主版本）
PG17：5433
PG18：5434
## 扩展端口规划（更多版本）
PG14：5430
PG15：5431
PG16：5432
PG17：5433
PG18：5434
## 端口使用原则
– 5432为默认端口，通常分配给生产版本
– 测试版本使用5433及以后端口
– 端口范围建议：5432-5450
– 避免使用系统保留端口
# 查看端口占用
netstat -tlnp | grep 543
ss -tlnp | grep 543
2.3 PostgreSQL数据库多版本环境变量规划
PostgreSQL数据库多版本环境变量规划：
## PG16环境变量
export PGHOME=/postgresql/fgapp/pgsql-16
export PGDATA=/postgresql/data/pg16
export PGPORT=5432
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
## PG17环境变量
export PGHOME=/postgresql/fgapp/pgsql-17
export PGDATA=/postgresql/data/pg17
export PGPORT=5433
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
## PG18环境变量
export PGHOME=/postgresql/fgapp/pgsql-18
export PGDATA=/postgresql/data/pg18
export PGPORT=5434
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
## 版本切换函数（添加到.bashrc）
function set_pg16() {
export PGHOME=/postgresql/fgapp/pgsql-16
export PGDATA=/postgresql/data/pg16
export PGPORT=5432
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
echo “Switched to PostgreSQL 16”
}
function set_pg17() {
export PGHOME=/postgresql/fgapp/pgsql-17
export PGDATA=/postgresql/data/pg17
export PGPORT=5433
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
echo “Switched to PostgreSQL 17”
}
function set_pg18() {
export PGHOME=/postgresql/fgapp/pgsql-18
export PGDATA=/postgresql/data/pg18
export PGPORT=5434
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
echo “Switched to PostgreSQL 18”
}
Part03-生产环境项目实施方案
3.1 PostgreSQL数据库多版本安装实施
3.1.1 安装PostgreSQL 16版本
$ sudo dnf install -y postgresql16-server postgresql16
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
postgresql16 x86_64 16.7-1PGDG.rhel9 pgdg16 1.5 M
postgresql16-server x86_64 16.7-1PGDG.rhel9 pgdg16 6.0 M
Transaction Summary
================================================================================
Install 2 Packages
Total download size: 7.5 M
Installed size: 28 M
Downloading Packages:
(1/2): postgresql16-16.7-1PGDG.rhel9.x86_64.rpm 8.5 MB/s | 1.5 MB 00:00
(2/2): postgresql16-server-16.7-1PGDG.rhel9.x86_64.rp 15 MB/s | 6.0 MB 00:00
——————————————————————————–
Total 15 MB/s | 7.5 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/2
Installing : postgresql16-16.7-1PGDG.rhel9.x86_64 1/2
Installing : postgresql16-server-16.7-1PGDG.rhel9.x86_64 2/2
Verifying : postgresql16-16.7-1PGDG.rhel9.x86_64 1/2
Verifying : postgresql16-server-16.7-1PGDG.rhel9.x86_64 2/2
Installed:
postgresql16-16.7-1PGDG.rhel9.x86_64
postgresql16-server-16.7-1PGDG.rhel9.x86_64
Complete!
# 初始化PG16数据目录
$ sudo mkdir -p /postgresql/data/pg16
$ sudo chown pgsql: pgsql /postgresql/data/pg16
$ sudo -u pgsql /usr/pgsql-16/bin/initdb -D /postgresql/data/pg16
The files belonging to this fgedudb system will be owned by fgedu “postgres”.
This fgedu must also own the server process.
The fgedudb cluster will be initialized with locale “zh_CN.UTF-8”.
The default fgedudb encoding has accordingly been set to “UTF8”.
initdb: could not find suitable text search configuration for locale “zh_CN.UTF-8”
The default text search configuration will be set to “simple”.
Data page checksums are disabled.
fixing permissions on existing directory /postgresql/data/pg16 … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … Asia/Shanghai
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
initdb: warning: enabling “trust” authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or –auth-local and –auth-fgedu.net.cn, the next time you run initdb.
Success. You can now start the fgedudb server using:
/usr/pgsql-16/bin/pg_ctl -D /postgresql/data/pg16 -l logfile start
3.1.2 安装PostgreSQL 17版本
$ sudo dnf install -y postgresql17-server postgresql17
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
postgresql17 x86_64 17.3-1PGDG.rhel9 pgdg17 1.6 M
postgresql17-server x86_64 17.3-1PGDG.rhel9 pgdg17 6.1 M
Transaction Summary
================================================================================
Install 2 Packages
Total download size: 7.7 M
Installed size: 29 M
Downloading Packages:
(1/2): postgresql17-17.3-1PGDG.rhel9.x86_64.rpm 8.8 MB/s | 1.6 MB 00:00
(2/2): postgresql17-server-17.3-1PGDG.rhel9.x86_64.rp 15 MB/s | 6.1 MB 00:00
——————————————————————————–
Total 15 MB/s | 7.7 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/2
Installing : postgresql17-17.3-1PGDG.rhel9.x86_64 1/2
Installing : postgresql17-server-17.3-1PGDG.rhel9.x86_64 2/2
Verifying : postgresql17-17.3-1PGDG.rhel9.x86_64 1/2
Verifying : postgresql17-server-17.3-1PGDG.rhel9.x86_64 2/2
Installed:
postgresql17-17.3-1PGDG.rhel9.x86_64
postgresql17-server-17.3-1PGDG.rhel9.x86_64
Complete!
# 初始化PG17数据目录
$ sudo mkdir -p /postgresql/data/pg17
$ sudo chown pgsql: pgsql /postgresql/data/pg17
$ sudo -u pgsql /usr/pgsql-17/bin/initdb -D /postgresql/data/pg17
The files belonging to this fgedudb system will be owned by fgedu “postgres”.
This fgedu must also own the server process.
The fgedudb cluster will be initialized with locale “zh_CN.UTF-8”.
The default fgedudb encoding has accordingly been set to “UTF8”.
initdb: could not find suitable text search configuration for locale “zh_CN.UTF-8”
The default text search configuration will be set to “simple”.
Data page checksums are disabled.
fixing permissions on existing directory /postgresql/data/pg17 … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … Asia/Shanghai
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
Success. You can now start the fgedudb server using:
/usr/pgsql-17/bin/pg_ctl -D /postgresql/data/pg17 -l logfile start
3.1.3 安装PostgreSQL 18版本
$ sudo dnf install -y postgresql18-server postgresql18
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
postgresql18 x86_64 18.3-1PGDG.rhel9 pgdg18 1.6 M
postgresql18-server x86_64 18.3-1PGDG.rhel9 pgdg18 6.2 M
Transaction Summary
================================================================================
Install 2 Packages
Total download size: 7.8 M
Installed size: 30 M
Downloading Packages:
(1/2): postgresql18-18.3-1PGDG.rhel9.x86_64.rpm 8.8 MB/s | 1.6 MB 00:00
(2/2): postgresql18-server-18.3-1PGDG.rhel9.x86_64.rp 15 MB/s | 6.2 MB 00:00
——————————————————————————–
Total 15 MB/s | 7.8 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/2
Installing : postgresql18-18.3-1PGDG.rhel9.x86_64 1/2
Installing : postgresql18-server-18.3-1PGDG.rhel9.x86_64 2/2
Verifying : postgresql18-18.3-1PGDG.rhel9.x86_64 1/2
Verifying : postgresql18-server-18.3-1PGDG.rhel9.x86_64 2/2
Installed:
postgresql18-18.3-1PGDG.rhel9.x86_64
postgresql18-server-18.3-1PGDG.rhel9.x86_64
Complete!
# 初始化PG18数据目录
$ sudo mkdir -p /postgresql/data/pg18
$ sudo chown pgsql: pgsql /postgresql/data/pg18
$ sudo -u pgsql /postgresql/fgapp/bin/initdb -D /postgresql/data/pg18
The files belonging to this fgedudb system will be owned by fgedu “postgres”.
This fgedu must also own the server process.
The fgedudb cluster will be initialized with locale “zh_CN.UTF-8”.
The default fgedudb encoding has accordingly been set to “UTF8”.
initdb: could not find suitable text search configuration for locale “zh_CN.UTF-8”
The default text search configuration will be set to “simple”.
Data page checksums are disabled.
fixing permissions on existing directory /postgresql/data/pg18 … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … Asia/Shanghai
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
Success. You can now start the fgedudb server using:
/postgresql/fgapp/bin/pg_ctl -D /postgresql/data/pg18 -l logfile start
3.2 PostgreSQL数据库版本环境切换方法
3.2.1 配置版本切换脚本
$ cat > /etc/profile.d/pg_switch.sh << 'EOF' #!/bin/bash # pg_switch.sh # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # from:www.itpux.com.qq113257174.wx:itpux-com # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: `http://www.fgedu.net.cn` # web: http://www.fgedu.net.cn function pg16() { export PGHOME=/usr/pgsql-16 export PGDATA=/postgresql/data/pg16 export PGPORT=5432 export PATH=$PGHOME/bin:$PATH export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export MANPATH=$PGHOME/share/man:$MANPATH echo "========================================" echo "PostgreSQL 16 Environment Activated" echo "PGHOME: $PGHOME" echo "PGDATA: $PGDATA" echo "PGPORT: $PGPORT" echo "========================================" } function pg17() { export PGHOME=/usr/pgsql-17 export PGDATA=/postgresql/data/pg17 export PGPORT=5433 export PATH=$PGHOME/bin:$PATH export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export MANPATH=$PGHOME/share/man:$MANPATH echo "========================================" echo "PostgreSQL 17 Environment Activated" echo "PGHOME: $PGHOME" echo "PGDATA: $PGDATA" echo "PGPORT: $PGPORT" echo "========================================" } function pg18() { export PGHOME=/postgresql/fgapp export PGDATA=/postgresql/data/pg18 export PGPORT=5434 export PATH=$PGHOME/bin:$PATH export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export MANPATH=$PGHOME/share/man:$MANPATH echo "========================================" echo "PostgreSQL 18 Environment Activated" echo "PGHOME: $PGHOME" echo "PGDATA: $PGDATA" echo "PGPORT: $PGPORT" echo "========================================" } function pg_status() { echo "Current PostgreSQL Environment:" echo "PGHOME: $PGHOME" echo "PGDATA: $PGDATA" echo "PGPORT: $PGPORT" echo "" echo "Available versions: pg16, pg17, pg18" } # 默认加载PG18环境 pg18 EOF # 使配置生效 $ source /etc/profile.d/pg_switch.sh
3.2.2 版本切换测试
$ pg16
========================================
PostgreSQL 16 Environment Activated
PGHOME: /usr/pgsql-16
PGDATA: /postgresql/data/pg16
PGPORT: 5432
========================================
$ psql –version
psql (PostgreSQL) 16.7
# 切换到PG17
$ pg17
========================================
PostgreSQL 17 Environment Activated
PGHOME: /usr/pgsql-17
PGDATA: /postgresql/data/pg17
PGPORT: 5433
========================================
$ psql –version
psql (PostgreSQL) 17.3
# 切换到PG18
$ pg18
========================================
PostgreSQL 18 Environment Activated
PGHOME: /postgresql/fgapp
PGDATA: /postgresql/data/pg18
PGPORT: 5434
========================================
$ psql –version
psql (PostgreSQL) 18.3
# 查看当前环境
$ pg_status
Current PostgreSQL Environment:
PGHOME: /postgresql/fgapp
PGDATA: /postgresql/data/pg18
PGPORT: 5434
Available versions: pg16, pg17, pg18
3.3 PostgreSQL数据库多版本服务管理
3.3.1 配置多版本Systemd服务
$ sudo cat > /etc/systemd/system/postgresql-16.service << 'EOF' [Unit] Description=PostgreSQL 16 fgedudb server Documentation=https://www.postgresql.org/docs/16/static/ After=network.target [Service] Type=notify User=pgsql Group=pgsql ExecStart=/usr/pgsql-16/bin/postmaster -D /postgresql/data/pg16 ExecReload=/bin/kill -HUP $MAINPID KillMode=mixed KillSignal=SIGINT TimeoutSec=0 [Install] WantedBy=multi-fgedu.target EOF # 创建PG17服务文件 $ sudo cat > /etc/systemd/system/postgresql-17.service << 'EOF' [Unit] Description=PostgreSQL 17 fgedudb server Documentation=https://www.postgresql.org/docs/17/static/ After=network.target [Service] Type=notify User=pgsql Group=pgsql ExecStart=/usr/pgsql-17/bin/postmaster -D /postgresql/data/pg17 ExecReload=/bin/kill -HUP $MAINPID KillMode=mixed KillSignal=SIGINT TimeoutSec=0 [Install] WantedBy=multi-fgedu.target EOF # 创建PG18服务文件 $ sudo cat > /etc/systemd/system/postgresql-18.service << 'EOF' [Unit] Description=PostgreSQL 18 fgedudb server Documentation=https://www.postgresql.org/docs/18/static/ After=network.target [Service] Type=notify User=pgsql Group=pgsql ExecStart=/postgresql/fgapp/bin/postmaster -D /postgresql/data/pg18 ExecReload=/bin/kill -HUP $MAINPID KillMode=mixed KillSignal=SIGINT TimeoutSec=0 [Install] WantedBy=multi-fgedu.target EOF # 重载systemd配置 $ sudo systemctl daemon-reload
3.3.2 启动多版本服务
$ sudo sed -i “s/^#port = 5432/port = 5432/” /postgresql/data/pg16/postgresql.conf
$ sudo sed -i “s/^#port = 5432/port = 5433/” /postgresql/data/pg17/postgresql.conf
$ sudo sed -i “s/^#port = 5432/port = 5434/” /postgresql/data/pg18/postgresql.conf
# 启动PG16服务
$ sudo systemctl start postgresql-16
$ sudo systemctl enable postgresql-16
Created symlink /etc/systemd/system/multi-fgedu.target.wants/postgresql-16.service → /etc/systemd/system/postgresql-16.service.
# 启动PG17服务
$ sudo systemctl start postgresql-17
$ sudo systemctl enable postgresql-17
Created symlink /etc/systemd/system/multi-fgedu.target.wants/postgresql-17.service → /etc/systemd/system/postgresql-17.service.
# 启动PG18服务
$ sudo systemctl start postgresql-18
$ sudo systemctl enable postgresql-18
Created symlink /etc/systemd/system/multi-fgedu.target.wants/postgresql-18.service → /etc/systemd/system/postgresql-18.service.
# 查看所有服务状态
$ sudo systemctl status postgresql-* –no-pager
● postgresql-16.service – PostgreSQL 16 fgedudb server
Loaded: loaded (/etc/systemd/system/postgresql-16.service; enabled; preset: disabled)
Active: active (running) since Thu 2026-04-02 11:00:00 CST; 10s ago
● postgresql-17.service – PostgreSQL 17 fgedudb server
Loaded: loaded (/etc/systemd/system/postgresql-17.service; enabled; preset: disabled)
Active: active (running) since Thu 2026-04-02 11:00:10 CST; 5s ago
● postgresql-18.service – PostgreSQL 18 fgedudb server
Loaded: loaded (/etc/systemd/system/postgresql-18.service; enabled; preset: disabled)
Active: active (running) since Thu 2026-04-02 11:00:20 CST; 2s ago
# 查看端口监听
$ sudo ss -tlnp | grep pgsql LISTEN 0 244 127.0.0.1:5432 0.0.0.0:* fgedus:((“postgres”,pid=12345,fd=3))
LISTEN 0 244 127.0.0.1:5433 0.0.0.0:* fgedus:((“postgres”,pid=12456,fd=3))
LISTEN 0 244 127.0.0.1:5434 0.0.0.0:* fgedus:((“postgres”,pid=12567,fd=3))
Part04-生产案例与实战讲解
4.1 PostgreSQL数据库升级测试环境搭建
4.1.1 升级测试场景说明
## 测试目标
1. 验证应用在PG18上的兼容性
2. 测试数据迁移的正确性
3. 评估升级后的性能表现
4. 验证备份恢复流程
## 测试步骤
1. 在PG16上创建测试数据
2. 使用pg_upgrade或pg_dump迁移数据
3. 在PG18上验证数据完整性
4. 运行应用兼容性测试
5. 进行性能对比测试
4.1.2 创建测试数据
$ pg16
$ psql -p 5432 -U pgsql
psql (16.7)
输入 “help” 来获取帮助信息.
postgres=# CREATE DATABASE fgedudb;
CREATE DATABASE
postgres=# \c fgedudb
您现在已经连接到数据库 “fgedudb”,用户 “postgres”.
fgedudb=# CREATE TABLE fgedu_fgedus(
id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50) NOT NULL,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE fgedu_
fgedudb=# INSERT INTO fgedu_fgedus(fgeduname, email)
SELECT
‘fgedu_’ || i,
‘fgedu’ || i || ‘@fgedu.net.cn’
FROM generate_series(1, 10000) AS i;
INSERT 0 10000
fgedudb=# CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);
CREATE INDEX
fgedudb=# SELECT COUNT(*) FROM fgedu_fgedus;
count
——-
10000
(1 行记录)
fgedudb=# \q
4.1.3 使用pg_dump迁移数据
$ pg16
$ pg_dump -p 5432 -U pgsql fgedudb > /postgresql/backup/fgedudb_pg16.sql
$ ls -lh /postgresql/backup/fgedudb_pg16.sql
-rw-r–r–. 1 pgsql pgsql 1.2M Apr 2 11:10 /postgresql/backup/fgedudb_pg16.sql
# 导入到PG18
$ pg18
$ psql -p 5434 -U pgsql -c “CREATE DATABASE fgedudb;”
CREATE DATABASE
$ psql -p 5434 -U pgsql -d fgedudb -f /postgresql/backup/fgedudb_pg16.sql
SET
SET
SET
SET
SET
set_config
————
(1 行记录)
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
CREATE TABLE fgedu_ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE INDEX
ALTER TABLE
ALTER TABLE
4.1.4 验证数据迁移结果
$ psql -p 5434 -U pgsql -d fgedudb
psql (18.3)
输入 “help” 来获取帮助信息.
fgedudb=# SELECT COUNT(*) FROM fgedu_fgedus;
count
——-
10000
(1 行记录)
fgedudb=# SELECT * FROM fgedu_fgedus LIMIT 5;
id | fgeduname | email | created_at
—-+———-+———————-+—————————-
1 | fgedu_1 | fgedu1@fgedu.net.cn | 2026-04-02 11:10:00.123456
2 | fgedu_2 | fgedu2@fgedu.net.cn | 2026-04-02 11:10:00.123456
3 | fgedu_3 | fgedu3@fgedu.net.cn | 2026-04-02 11:10:00.123456
4 | fgedu_4 | fgedu4@fgedu.net.cn | 2026-04-02 11:10:00.123456
5 | fgedu_5 | fgedu5@fgedu.net.cn | 2026-04-02 11:10:00.123456
(5 行记录)
fgedudb=# \d fgedu_fgedus
数据表 “public.fgedu_fgedus”
栏位 | 类型 | 校对规则 | 可空 | 预设
————-+—————————–+———-+——–+—————————————
id | integer | | 非空 | nextval(‘fgedu_fgedus_id_seq’::regclass)
fgeduname | character varying(50) | | 非空 |
email | character varying(100) | | |
created_at | timestamp without time zone | | | CURRENT_TIMESTAMP
索引：
“fgedu_fgedus_pkey” PRIMARY KEY, btree (id)
“idx_fgedu_fgedus_email” btree (email)
fgedudb=# \q
4.2 PostgreSQL数据库多版本常见问题处理
4.2.1 端口冲突问题
$ sudo systemctl start postgresql-17
Job for postgresql-17.service failed because the control process exited with error code.
# 查看日志
$ sudo journalctl -u postgresql-17 -n 20
Apr 02 11:15:00 fgedu.net.cn postmaster[12678]: 2026-04-02 11:15:00.123 CST [12678] FATAL: lock file “postmaster.pid” already exists
Apr 02 11:15:00 fgedu.net.cn postmaster[12678]: 2026-04-02 11:15:00.123 CST [12678] HINT: Is another postmaster (PID 12345) running in data directory “/postgresql/data/pg17”?
# 解决方案：检查并清理锁文件
$ sudo rm -f /postgresql/data/pg17/postmaster.pid
# 重新启动
$ sudo systemctl start postgresql-17
4.2.2 数据目录权限问题
$ sudo journalctl -u postgresql-18 -n 20
Apr 02 11:20:00 fgedu.net.cn postmaster[12789]: 2026-04-02 11:20:00.123 CST [12789] FATAL: data directory “/postgresql/data/pg18” has wrong ownership
Apr 02 11:20:00 fgedu.net.cn postmaster[12789]: 2026-04-02 11:20:00.123 CST [12789] HINT: The server must be started by the fgedu that owns the data directory.
# 解决方案：修复权限
$ sudo chown -R pgsql: pgsql /postgresql/data/pg18
$ sudo chmod 700 /postgresql/data/pg18
# 验证权限
$ ls -la /postgresql/data/
drwx——. 18 pgsql pgsql 4096 Apr 2 11:00 pg16
drwx——. 18 pgsql pgsql 4096 Apr 2 11:00 pg17
drwx——. 18 pgsql pgsql 4096 Apr 2 11:20 pg18
4.2.3 环境变量混乱问题
$ psql -U pgsql
psql: error: connection to server on socket “/tmp/.s.PGSQL.5432” failed: 没有那个文件或目录
# 原因分析：环境变量PGPORT与实际服务端口不匹配
$ echo $PGPORT
5434
$ echo $PGDATA
/postgresql/data/pg18
# 解决方案：使用版本切换函数重新设置环境
$ pg16
$ psql -U pgsql
psql (16.7)
输入 “help” 来获取帮助信息.
postgres=# SELECT version();
version
———————————————————————————————————
PostgreSQL 16.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit
(1 行记录)
4.3 PostgreSQL数据库版本迁移实战
4.3.1 使用pg_upgrade进行版本升级
# 1. 停止所有PG服务
$ sudo systemctl stop postgresql-16 postgresql-18
# 2. 检查数据目录
$ ls -la /postgresql/data/pg16
$ ls -la /postgresql/data/pg18
# 3. 运行pg_upgrade检查模式
$ sudo -u pgsql /postgresql/fgapp/bin/pg_upgrade \
–old-bindir=/usr/pgsql-16/bin \
–new-bindir=/postgresql/fgapp/bin \
–old-datadir=/postgresql/data/pg16 \
–new-datadir=/postgresql/data/pg18 \
–check
Performing Consistency Checks
—————————–
Checking cluster versions ok
Checking fgedudb fgedu is the install fgedu ok
Checking fgedudb connection settings ok
Checking for prepared transactions ok
Checking for reg* data types in fgedu tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid “sql_identifier” fgedu columns ok
Checking for extension updates ok
Checking for presence of required libraries ok
Checking fgedudb fgedu is the install fgedu ok
Checking for prepared transactions ok
*Clusters are compatible*
# 4. 执行实际升级
$ sudo -u pgsql /postgresql/fgapp/bin/pg_upgrade \
–old-bindir=/usr/pgsql-16/bin \
–new-bindir=/postgresql/fgapp/bin \
–old-datadir=/postgresql/data/pg16 \
–new-datadir=/postgresql/data/pg18 \
–link
Performing Consistency Checks
—————————–
Checking cluster versions ok
Checking fgedudb fgedu is the install fgedu ok
Checking fgedudb connection settings ok
Checking for prepared transactions ok
Checking for reg* data types in fgedu tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid “sql_identifier” fgedu columns ok
Checking for extension updates ok
Checking for presence of required libraries ok
Checking fgedudb fgedu is the install fgedu ok
Checking for prepared transactions ok
Creating dump of global objects ok
Creating dump of fgedudb schemas
ok
Checking for presence of required libraries ok
Checking fgedudb fgedu is the install fgedu ok
Checking for prepared transactions ok
Upgrade Complete
—————-
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/postgresql/fgapp/bin/vacuumdb –all –analyze-in-stages
Running this script will delete the old cluster’s data files:
./delete_old_cluster.sh
Part05-风哥经验总结与分享
5.1 PostgreSQL数据库多版本管理最佳实践
PostgreSQL数据库多版本管理最佳实践风哥教程风哥教程风哥教程总结：
- 规范目录命名：使用版本号区分目录，便于识别管理
- 统一端口规划：按版本顺序分配端口，避免冲突
- 环境变量管理：使用函数切换环境，避免手动修改
- 服务独立管理：每个版本独立的systemd服务
- 定期清理：及时清理不再使用的旧版本
- 文档记录：记录各版本用途和配置信息
5.2 PostgreSQL数据库多版本检查清单
## 安装检查
□ 各版本安装目录独立
□ 各版本数据目录独立
□ 各版本端口不冲突
□ 各版本服务名称不同
## 配置检查
□ postgresql.conf端口配置正确
□ pg_hba.conf配置正确
□ 环境变量切换脚本可用
□ systemd服务文件正确
## 运行检查
□ 各版本服务可正常启动
□ 各版本可独立连接
□ 数据迁移测试通过
□ 版本切换功能正常
## 维护检查
□ 各版本备份策略配置
□ 各版本日志轮转配置
□ 监控告警配置
□ 定期清理旧版本
5.3 PostgreSQL数据库版本管理工具推荐
PostgreSQL数据库版本管理相关工具推荐：
- pg_upgrade：PostgreSQL官方版本升级工具
- pg_dump/pg_restore：逻辑备份恢复工具
- pgBackRest：企业级备份恢复工具
- Patroni：高可用管理工具
- pgenv：PostgreSQL版本管理工具
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
