PostgreSQL 大版本升级 14.13 升级到 15.8

–备份之前的安装目录、数据目录、参数文件

scp -r /postgresql/app/postgresql postgresql14.13
scp -r /postgresql/pgdata /postgresql/pgdatabak

最好做一次全备
pg_basebackup -h192.168.1.201 -p5432 -Upostgres -Xs -Ft -z -Pv -Z9 -D /bakup/backup_20241015

–关库

pg_ctl stop

–解压，在解压目录下编译，编译到新的目录下

[pgsql@pg15 app]$ cd /postgresql/soft/
[pgsql@pg15 soft]$ tar -zxvf postgresql-15.8.tar.gz
[pgsql@pg15 soft]$ cd postgresql-15.8

–编译安装

mkdir -p /postgresql/app/postgresql15.8

./configure --prefix=/postgresql/app/postgresql15.8 \
--with-icu \
--with-perl \
--with-tcl \
--with-tclconfig=/usr/lib64 \
--with-openssl \
--with-includes=/usr/include/openssl \
--with-readline \
--with-pam \
--with-gssapi \
--with-libraries=/usr/lib64 \
--enable-nls \
--with-libxml \
--with-libxslt \
--with-ldap \
--with-selinux \
--with-systemd \
--with-system-tzdata=/usr/share/zoneinfo \
--with-blocksize=32 \
--with-wal-blocksize=32

make -j 16 && make install

–在新的安装目录下初始化

cd /postgresql/app/postgresql15.8/
./bin/initdb -D /postgresql/pgdata15 -E UTF8 --lc-collate=C --lc-ctype=en_US.utf8 -U postgres

–兼容性测试

/postgresql/app/postgresql15.8/bin/pg_upgrade -b /postgresql/app/postgresql/bin -B /postgresql/app/postgresql15.8/bin -d /postgresql/pgdata -D /postgresql/pgdata15 -c

–升级

/postgresql/app/postgresql15.8/bin/pg_upgrade -b /postgresql/app/postgresql/bin -B /postgresql/app/postgresql15.8/bin -d /postgresql/pgdata -D /postgresql/pgdata15

–拷贝几个参数文件

cd /postgresql/pgdata
mv pg_hba.conf postgresql.conf postgresql.auto.conf /postgresql/pgdata15/

–修改环境变量

vim ~/.bash_profile
export PGDATA=/postgresql/pgdata15
export PGHOME=/postgresql/app/postgresql15.8

source ~/.bash_profile

–查看

env |grep PG
PGPORT=5432
PGUSER=postgres
PGDATABASE=postgres
PGHOST=/postgresql/pgdata15
PGHOME=/postgresql/app/postgresql15.8
PGDATA=/postgresql/pgdata15

–启动

[postgres@postgresql01 bin]$ pg_ctl start

–更新统计信息

 /postgresql/app/postgresql15.8/bin/vacuumdb --all --analyze-in-stages