pg_upgrade
（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