postgresql夸版本升级方案

postgresql夸版本升级方案

测试PG数据库夸版本升级方案，从PG9.5升级到最新的PG12。
方案主要包括三种：
1，pg_upgrade方案，使用pg_upgrade升级工具进行升级
2，pg_dumpall方案，使用pg_dumpall工具对9.5的旧库全备份，然后还原到12
3，pglogical 复制方案

方案1 pg_upgrade升级工具

• 停止旧库和新库的运行

bash-4.2$ ./pg_ctl stop -D /usr/pgsql-9.5/data/
waiting for server to shut down.... done
server stopped
bash-4.2$ exit

bash-4.2$ ./pg_ctl stop -D /usr/pgsql-12/data/
waiting for server to shut down.... done
server stopped

• 执行升级检查

cd /usr/pgsql-12/bin/
su postgres

#看一下pg_upgrade的帮助
./pg_upgrade --help
pg_upgrade upgrades a PostgreSQL cluster to a different major version.

Usage:
 pg_upgrade [OPTION]...

Options:
 -b, --old-bindir=BINDIR       old cluster executable directory
 -B, --new-bindir=BINDIR       new cluster executable directory
 -c, --check                   check clusters only, don't change any data
 -d, --old-datadir=DATADIR     old cluster data directory
 -D, --new-datadir=DATADIR     new cluster data directory
 -j, --jobs                    number of simultaneous processes or threads to use
 -k, --link                    link instead of copying files to new cluster
 -o, --old-options=OPTIONS     old cluster options to pass to the server
 -O, --new-options=OPTIONS     new cluster options to pass to the server
 -p, --old-port=PORT           old cluster port number (default 50432)
 -P, --new-port=PORT           new cluster port number (default 50432)
 -r, --retain                  retain SQL and log files after success
 -s, --socketdir=DIR           socket directory to use (default current dir.)
 -U, --username=NAME           cluster superuser (default "postgres")
 -v, --verbose                 enable verbose internal logging
 -V, --version                 display version information, then exit
 --clone                       clone instead of copying files to new cluster
 -?, --help                    show this help, then exit

Before running pg_upgrade you must:
 create a new database cluster (using the new version of initdb)
 shutdown the postmaster servicing the old cluster
 shutdown the postmaster servicing the new cluster

When you run pg_upgrade, you must provide the following information:
 the data directory for the old cluster  (-d DATADIR)
 the data directory for the new cluster  (-D DATADIR)
 the "bin" directory for the old version (-b BINDIR)
 the "bin" directory for the new version (-B BINDIR)

For example:
 pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin -B newCluster/bin
or
 $ export PGDATAOLD=oldCluster/data
 $ export PGDATANEW=newCluster/data
 $ export PGBINOLD=oldCluster/bin
 $ export PGBINNEW=newCluster/bin
 $ pg_upgrade

Report bugs to <pgsql-bugs@lists.postgresql.org>.

#执行检查
./pg_upgrade -c -b /usr/pgsql-9.5/bin/ -B /usr/pgsql-12/bin/ -d /usr/pgsql-9.5/data/ -D /usr/pgsql-12/data/ -p 5432 -P 5433
Performing Consistency