PG升级实验

大版本升级
从10.0到15.1的升级

--模拟数据
create database mydb;
\c mydb
create unlogged table t(id int);
insert into t select generate_series(1, 1000);
select count(*) from t;

安装新版本

cd software/
wget https://ftp.postgresql.org/pub/source/v15.1/postgresql-15.1.tar.bz2 --no-check-certificate
tar xf postgresql-15.1.tar.bz2
cd postgresql-15.1
./configure --prefix=/opt/pgsql_15.1 --with-pgport=1921

gmake world
sudo gmake install-world

停库升级

--停旧库
pg_ctl stop

--初始化新库
/opt/pgsql_15.1/bin/initdb -D /database/pg10/data.upgrade/ -E UTF8 --locale=C -U postgres -W

--升级检查
/opt/pgsql_15.1/bin/pg_upgrade -b /opt/pgsql_10.0/bin -B /opt/pgsql_15.1/bin -d /database/pg10/pg_root -D /database/pg10/data.upgrade

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /opt/pgsql_15.1/bin/vacuumdb --all --analyze-in-stages

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

--修改软链接
ls -lh /opt
sudo unlink /opt/pgsql
sudo ln -s /opt/pgsql_15.1 /opt/pgsql

--修改环境变量
vi .bash_profile
export PATH
export PGPORT=1921
export PGUSER=postgres
export PGDATA=/database/pg10/data.upgrade
export LANG=en_Us.utf8
export PGHOME=/opt/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'
alias ll='ls -lh'

--启动新库
pg_ctl -D /database/pg10/data.upgrade/ -l logfile start

--测试验证
psql mydb
select count(*) from t;