pg_upgrade大版本升级
pg_upgrade
海丰ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为成都创新互联的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：18980820575（备注：SSL证书合作）期待与您的合作！
（1）PostgreSQL提供大版本升级的一个工具，比如说从9.1到9.2，也可以一次跨多个大版本，直接从9.1到9.5等，它的优点是不需要把数据导入导出，这在数据量比较大的时候，非常方便。
（2）不适合小版本升级，比如说从9.0.1到9.0.4
（3）PostgreSQL8.4.x之后才能使用pg_upgrade
（4）如果数据量不大，可以使用pg_dump/pg_restore升级数据库
环境
Old:postgresql-9.5.9
New:postgresql-9.6.5
安装postgresql-9.5.9
[root@Darren2 ~]# mkdir -p /usr/local/pgsql/pgsql9.5.9/{data,arch}
[root@Darren2 pgsql]# groupadd dba
[root@Darren2 pgsql]# useradd -g dba -G root postgres -d /usr/local/pgsql
[root@Darren2 pgsql9.5.9]# tar xf postgresql-9.5.9.tar.gz
[root@Darren2 pgsql]# chmod -R 755 /usr/local/pgsql
[root@Darren2 pgsql]# chown -R postgres:dba /usr/local/pgsql
[root@Darren2 pgsql]# chmod -R 700 /usr/local/pgsql/pgsql9.5.9/data/
Darren2:postgres:/usr/local/pgsql:>cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PGHOME=/usr/local/pgsql/pgsql9.5.9
export PGDATA=$PGHOME/data
export PATH=$PATH:$PGHOME/bin
#stty erase
set umask to 022
umask 022
PS1=`uname -n`":"'$USER'":"'$PWD'":>"; export PS1
[root@Darren2 postgresql-9.5.9]# ./configure --prefix=/usr/local/pgsql/pgsql9.5.9/
[root@Darren2 postgresql-9.5.9]# make world && make install-world
Darren2:postgres:/usr/local/pgsql:>initdb -D $PGDATA -U postgres -E UTF8 -W
Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data:>vim pg_hba.conf
host all all 0.0.0.0/0 md5
Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data:>vim postgresql.conf
listen_addresses = '*'
wal_level = archive
port = 5432
max_connections = 300
shared_buffers = 128MB
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
archive_mode = on
archive_command = 'test ! -f /usr/local/pgsql/pgsql9.5.9/arch/%f && cp %p /usr/local/pgsql/pgsql9.5.9/arch/%f'
#启动数据库
Darren2:postgres:/usr/local/pgsql:>pg_ctl start
#创建测试数据
Darren2:postgres:/usr/local/pgsql/pgsql9.5.9:>cd /usr/local/pgsql/pgsql9.5.9
Darren2:postgres:/usr/local/pgsql/pgsql9.5.9:>mkdir tbs1
Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data:>psql
postgres=# create role cdhu1 login encrypted password '147258';
postgres=# create tablespace tbs1 location '/usr/local/pgsql/pgsql9.5.9/tbs1';
postgres=# create database testdb1 template template0 encoding 'UTF8' tablespace tbs1;
postgres=# grant all on database testdb1 to cdhu1;
postgres=# grant all on tablespace tbs1 to cdhu1;
Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data:>psql -d testdb1 -U cdhu1
testdb1=> create table t1(id int primary key, info text);
testdb1=> insert into t1 select generate_series(1,2000000),'helloWorld';
安装新版本postgresql-9.6.5
[root@Darren2 ~]# mkdir -p /usr/local/pgsql/pgsql9.6.5/{data,arch}
[root@Darren2 pgsql9.6.5]# tar xf postgresql-9.6.5.tar.gz
[root@Darren2 pgsql]# chmod -R 755 /usr/local/pgsql
[root@Darren2 pgsql]# chown -R postgres:dba /usr/local/pgsql
[root@Darren2 pgsql]# chmod -R 700 /usr/local/pgsql/pgsql9.6.5/data/
[root@Darren2 postgresql-9.5.9]# ./configure --prefix=/usr/local/pgsql/pgsql9.6.5/
[root@Darren2 postgresql-9.5.9]# make world && make install-world
Darren2:postgres:/usr/local/pgsql/pgsql9.6.5/bin:>cd /usr/local/pgsql/pgsql9.6.5/bin
Darren2:postgres:/usr/local/pgsql:>./initdb -D /usr/local/pgsql/pgsql9.6.5/data -U postgres -E UTF8 -W
Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data:>vim pg_hba.conf
host all all 0.0.0.0/0 md5
Darren2:postgres:/usr/local/pgsql/pgsql9.6.5/data:>vim postgresql.conf
listen_addresses = '*'
wal_level = replica
port = 5431 #必须和上面端口号的不一样
max_connections = 300
shared_buffers = 128MB
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
archive_mode = on
archive_command = 'test ! -f /usr/local/pgsql/pgsql9.6.5/arch/%f && cp %p /usr/local/pgsql/pgsql9.6.5/arch/%f'
#多实例的启动数据库服务
Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.5.9/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.5.9/data/
Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.6.5/data
#多实例的停止数据服务（升级前需要停止数据库服务）
Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.5.9/bin/pg_ctl stop -m fast -D /usr/local/pgsql/pgsql9.5.9/data
Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl stop -m fast -D /usr/local/pgsql/pgsql9.6.5/data
pg_upgrade参数解析
-b, --old-bindir=BINDIR old cluster executable directory
-B, --new-bindir=BINDIR new cluster executable directory
-d, --old-datadir=DATADIR old cluster data directory
-D, --new-datadir=DATADIR new cluster data directory
-p, --old-port=PORT old cluster port number (default 50432)
-P, --new-port=PORT new cluster port number (default 50432)
-c, --check check clusters only, don't change any data
-j, --jobs number of simultaneous processes or threads to use
-k, --link link instead of copying files to new cluster
-r, --retain retain SQL and log files after success
-U, --username=NAME cluster superuser (default "postgres")
-v, --verbose enable verbose internal logging
pg_upgrade有两种升级方式，一个是缺省的通过拷贝数据文件到新的data目录下，一个是创建硬链接。拷贝的方式升级较慢，但是原库还可用；硬链接的方式升级较快，但是原库不可用。
使用9.6.5的pg_upgrade检测兼容性
Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_upgrade -b /usr/local/pgsql/pgsql9.5.9/bin -B /usr/local/pgsql/pgsql9.6.5/bin -d /usr/local/pgsql/pgsql9.5.9/data -D /usr/local/pgsql/pgsql9.6.5/data -p 5432 -P 5431 -U postgres -j 8 -k -c
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 roles starting with 'pg_' ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
正式升级
Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_upgrade -b /usr/local/pgsql/pgsql9.5.9/bin -B /usr/local/pgsql/pgsql9.6.5/bin -d /usr/local/pgsql/pgsql9.5.9/data -D /usr/local/pgsql/pgsql9.6.5/data -p 5432 -P 5431 -U postgres -j 8 -k -r -v
......
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
#生成2个脚本, 用于收集统计信息和删除老集群，和一些有关升级过程的日志信息
Darren2:postgres:/usr/local/pgsql:>ls -ltr
-rw-r--r--. 1 postgres dba 2215 Oct 18 01:37 pg_upgrade_dump_globals.sql
-rw-------. 1 postgres dba 2237 Oct 18 01:37 pg_upgrade_dump_13241.custom
-rw-------. 1 postgres dba 2228 Oct 18 01:37 pg_upgrade_dump_1.custom
-rw-------. 1 postgres dba 3733 Oct 18 01:37 pg_upgrade_dump_16386.custom
-rw-------. 1 postgres dba 2422 Oct 18 01:37 pg_upgrade_dump_13241.log
-rw-------. 1 postgres dba 2409 Oct 18 01:37 pg_upgrade_dump_1.log
-rw-------. 1 postgres dba 2686 Oct 18 01:37 pg_upgrade_dump_16386.log
-rw-------. 1 postgres dba 240810 Oct 18 01:37 pg_upgrade_utility.log
-rwx------. 1 postgres dba 112 Oct 18 01:37 delete_old_cluster.sh
-rwx------. 1 postgres dba 809 Oct 18 01:37 analyze_new_cluster.sh
-rw-------. 1 postgres dba 2956 Oct 18 01:37 pg_upgrade_server.log
-rw-------. 1 postgres dba 42227 Oct 18 01:37 pg_upgrade_internal.log
#启动新的数据库集群
Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.6.5/data
#根据脚本analyze_new_cluster.sh执行统计信息收集
Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/vacuumdb -U 'postgres' --all --analyze-in-stages -p 5431
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "testdb1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "testdb1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
vacuumdb: processing database "testdb1": Generating default (full) optimizer statistics
#查看数据是否存在
Darren2:postgres:/usr/local/pgsql:>psql -p 5431
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
testdb1=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+-------+-------------
public | t1 | table | cdhu1 | 85 MB |
#通过上述脚本删除老数据
Darren2:postgres:/usr/local/pgsql:>cat delete_old_cluster.sh
#!/bin/sh
rm -rf '/usr/local/pgsql/pgsql9.5.9/data'
rm -rf '/usr/local/pgsql/pgsql9.5.9/tbs1/PG_9.5_201510051'
Darren2:postgres:/usr/local/pgsql:>bash delete_old_cluster.sh
最后再修改环境变量和端口号等和原来版本保持一致。
Error
Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.6.5/data
pg_ctl: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory
解决方法:
[root@Darren2 postgresql-9.6.5]# ln -s /usr/local/pgsql/pgsql9.6.5/lib/libpq.so.5 /usr/lib64/
网页名称：pg_upgrade大版本升级
文章地址：http://ybzwz.com/article/jhoods.html
