Postgresql-大版本升级经验谈__为了使用让Postgresql运行起来性能更好，使用一些更高级的功能特性，我们经常需要升级版本。 PostgreSQL小版本可以实现平滑升级，但大版本升级就比较折腾，本文分享一些Postgresql大版本升级方法。: 世界上功能最强大的开源数据库...

Postgresql-大版本升级经验谈

作者: 阿弟

欢迎大家踊跃投稿，投稿邮箱：press@postgres.cn

为了使用让Postgresql运行起来性能更好，使用一些更高级的功能特性，我们经常需要升级版本，小版本可以实现平滑升级，但大版本升级就比较折腾，下面分享一些Postgresql升级方法。

一、os环境及postgresql版本

硬件及os环境

os：centos 7.0
[root@dywl ad]# uname -a
Linux dywl 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 611:36:42 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

/etc/profile配置

PGDATA=/home/postgres/data
PGHOST=127.0.0.1
PGDATABASE=dbname #你的业务库名
PGUSER=postgres
PGPORT=5432
PATH=/usr/local/pgsql/bin
export PATH
export PGDATA PGHOST PGDATABASE PGUSER PGPORT

Postgresql相关信息

项目版本号安装路径

旧版本 9.4.1 /usr/local/pgsql9.4.1

新版本 9.6.1 /usr/local/pgsql9.6.1

项目	版本号	安装路径
旧版本	9.4.1	/usr/local/pgsql9.4.1
新版本	9.6.1	/usr/local/pgsql9.6.1

二、新版本postgresql安装部署工作

检查旧版本的编译参数
使用pg_config查询编译时所带的参数

 
[root@dywl ad]#pg_config
BINDIR =/usr/local/pgsql9.4.1/bin
...
...
CONFIGURE ='--prefix=/usr/local/pgsql9.4.1' '--with-perl' '--with-tcl' '--with-python''--with-openssl' 
           '--with-pam' '--without-ldap' '--with-libxml' '--with-libxslt'
...
VERSION =PostgreSQL 9.4.1

上面红色部分就是旧版本当时编译时使用的参数

检查旧版本库使用了那些插件
使用psql连接上业务库，然后输入\dx即可查看到使用了那些插件

mydb=#\dx
                                          Listof installed extensions
        Name        | Version |   Schema   |                             Description                   
--------------------+---------+------------+------------------------------------------------------------------
 pg_stat_statements |1.2      | public     | track execution statistics of all SQLstatements executed
 pg_trgm            | 1.1     | public     | text similarity measurement and indexsearching based on trigrams
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedurallanguage
 postgis            | 2.1.6   | public     | PostGIS geometry, geography, and raster spatial types and functions
(4 rows)

按照旧版本Postgresql配置要求编译新的postgresql版本
编译安装主程序

wgethttps://ftp.postgresql.org/pub/source/v9.6.1/postgresql-9.6.1.tar.gz
tar zxfpostgresql-9.6.1.tar.gz
cdpostgresql-9.6.1
./configure--prefix=/usr/local/pgsql9.6.1 --with-perl --with-tcl --with-python--with-openssl --with-pam 
                    --without-ldap --with-libxml --with-libxslt
gmake -j4
gmakeinstall

编译安装插件

 
[root@dywlcontrib]# pwd
/home/ad/source/postgresql-9.6.1/contrib
[root@dywlcontrib]# cd pg_trgm/
[root@dywlpg_trgm]# gmake -f Makefile
[root@dywl pg_trgm]# gmake install
 
[root@dywlpg_trgm]# cd ../pg_stat_statements
[root@dywlpg_stat_statements]# gmake -f Makefile
[root@dywlpg_trgm]# gmake install

编译安装postgis

 
[root@dywlsource]# wget http://download.osgeo.org/postgis/source/postgis-2.1.8.tar.gz
[root@dywlsource]# tar zxf postgis-2.1.8.tar.gz
[root@dywlsource]# cd postgis-2.1.8
[root@dywlpostgis-2.1.8]#
[root@dywlpostgis-2.1.8]# ./configure --with-prefix=/usr/local/postgis-2.1.8 
                   --with-pgconfig=/usr/local/pgsql9.6.1/bin/pg_config
                   --with-gdalconfig=/usr/local/gdal-1.11.2/bin/gdal-config
                   --with-geosconfig=/usr/local/geos-3.4.2/bin/geos-config 
                   --with-xml2config=/usr/local/libxml2-2.9.2/bin/xml2-config
                   --with-projdir=/usr/local/proj-4.9.1/ 
                   --with-jsondir=/usr/local/json/
[root@dywlpostgis-2.1.8]gmake -j 4
[root@dywlpostgis-2.1.8]gmake -j install
[root@dywlpostgis-2.1.8]echo '/usr/local/pgsql9.6.1/lib'> /etc/ld.so.conf.d/pgsql9.6.1.conf
[root@dywlpostgis-2.1.8]ldconfig
[root@dywlpostgis-2.1.8]ldconfig -p | grep pgsql9.6.1 #确认动态库加载成功

新版本data初始化有参数修改
初始化data

[root@dywl bin]# su postgres
[postgres@dywl bin]$/usr/local/pgsql9.6.1/bin/initdb --no-locale -E utf8 -D/home/postgres/data9.6.1 -U postgres -W

修改postgresql.conf必要的参数

listen_addresses = '*'
port = 5433  #先与旧的port不一致，等恢复成功后再修改成旧版本使用的port
#下面的参数都是搞高恢复速度的参数
shared_buffers = 5120MB
maintenance_work_mem = 1024MB
max_wal_size = 2GB
wal_level = minimal
logging_collector = off #先不用启用日志
autovacuum = off #关闭垃圾回收服务
fsync = off  #关闭磁盘同步

其它参数按照旧版本设置即可为了提高恢复的速度，可以参考下面的文章来进行 http://www.postgres.cn/news/viewone/1/244
修改pg_hba.conf必要的参数
配置成跟旧的参数一致，包含主备的配置，最后只开启下面这一条，其它连接控制都要关闭掉

host    all             all             127.0.0.1/32            md5

启动新的postgresql版本

[postgres@dywl pgbouncer]$/usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1/

如果是主备集群，则备机上也需要提前做好部署工作，备机上的运行参数按照旧版的运行参数配置即可。

三、测试新版本与旧版本pg之ddl兼容性

这一步工作非常重要，一般这里如果能通过的话，后面数据导入工作基本就能顺利执行，要不然导入大容量的数据库到一半出错重来你会疯掉，而且也影响业务。

使用新版本客户端备份工具pg_dumpall把旧版本所有库的ddl备份出来

[postgres@dywl~]$ /usr/local/pgsql9.6.1/bin/pg_dumpall -h 127.0.0.1 -U postgres -p 5432 -s > /home/postgres/db.sql

使用新版本的客户端工具psql把备份出来的脚本导入到新的版本pg库中
```
[postgres@dywl~]$ /usr/local/pgsql9.6.1/bin/psql -h 127.0.0.1 -p 5433 -d template1 -f/home/postgres/db.sql | grep error
```
执行上面的语句如果只发现例如用户已经存在的错误提示，那么表示两个版本的ddl兼容性没问题，否则就要根据错误提示把问题解决掉，直到这样的检查没有再提示错误为止
```
ERROR:  role "postgres" already exists
```
使用新版本的pg_dumpall从备库中把数备份出来并导入到新的版本pg库中
这一步非必需做，但有条件的话最好测试一下

四、开始升级工作

移除刚才测试的data，并且再重新初始化一个data

#停止9.6服务
[postgres@dywl ~]$/usr/local/pgsql9.6.1/bin/pg_ctl stop -D /home/postgres/data9.6.1 -m fast
waiting for server to shut down....done
server stopped
#目录移除
[postgres@dywl ~]$ mv/home/postgres/data9.6.1 /home/postgres/data9.6.1.bak
#初始化一个新的data
[postgres@dywl ~]$/usr/local/pgsql9.6.1/bin/initdb --no-locale -E utf8 -D/home/postgres/data9.6.1 -U postgres -W
#将原来的配置文件覆盖上来
[postgres@dywl ~]$ cp/home/postgres/data9.6.1.bak/*.conf /home/postgres/data9.6.1/ -rf
#删除掉刚才测试建立的表空间对应物理目录
[postgres@dywl ~]# rm/home/postgres/indexs/PG_9.6* -rf
#启动新版本服务
postgres@dywl~]/usr/local/pgsql9.6.1/bin/pg_ctl start -D /home/postgres/data9.6.1

修改旧版本,限制业务进行访问

--增加一个本次操作的超级用户
template=# create role backup_userwith login password '000000' superuser;
 
--修改pg_hba.conf,限制只有backup_user能访问
host    all             backup_user      127.0.0.1/32            md5
 
--restart服务，升级工作时间自己按业务要求进行
[postgres@dywl ~]$ /usr/local/pgsql9.4.1/bin/pg_ctlrestart -D /home/postgres/data9.4.1 -m fast

用新版本的pg_dumpall备份旧版本的全局对象

[postgres@dywl~]$ /usr/local/pgsql9.6.1/bin/pg_dumpall -h 127.0.0.1 -U backup_user 
                                    -p 5432 -g> /home/postgres/db.9.4.1.global.backup

用新版本的pg_dump备份旧版本的业务库

[postgres@dywl~]$ /usr/local/pgsql9.6.1/bin/pg_dump -h 127.0.0.1 -U backup_user -p 5432 mydb-Fc > /home/postgres/db.9.4.1.backup

用新版本的psql把全局对象恢复入库

[postgres@dywl~]$ /usr/local/pgsql9.6.1/bin/psql -h 127.0.0.1 -U postgres -p 5433 -d postgres-f /home/postgres/db.9.4.1.global.backup

用新版本的createdb建立新的业务库

[postgres@dywl~]$ /usr/local/pgsql9.6.1/bin/createdb -h 127.0.0.1 -U postgres -p 5433 mydb

用新版本的pg_restore把备份出来的数据恢复入库

[postgres@dywl~]$ /usr/local/pgsql9.6.1/bin/pg_restore -h 127.0.0.1 -U postgres -p 5433 -dmydb -j 4 /home/postgres/db.9.4.1.backup
#将数据刷到硬盘上,一定要执行
[postgres@dywl~]#sync

如果恢复数据过程中没有提示任何错误，那么升级工作就已经大功告成。

五、上线前准备工作

停止旧版本的pg服务

[postgres@dywl~]$ /usr/local/pgsql9.4.1/bin/pg_ctl stop -D /home/postgres/data9.4.1

对新版本的库做垃圾回收和数据信息统计

[postgres@dywl data9.6.1]$/usr/local/pgsql9.6.1/bin/vacuumdb -h 127.0.0.1 -U postgres -p 5433 -a -z

删除backup_user用户

[postgres@dywl data9.6.1]$/usr/local/pgsql9.6.1/bin/dropuser -h 127.0.0.1 -U postgres -p 5433 backup_user

修改旧版本的运行参数
postgresql.conf主要把这几个参数给修改过来，其它参数视你自己项目按要求修改即可
```
port = 5432
logging_collector = on #先不用启用日志
autovacuum = on #关闭垃圾回收服务
fsync = on  #关闭磁盘同步
```
pg_hba.conf按项目要求修改即可
注：postgresql.conf，pg_hba.conf具体要修改的内容可以在导入数据时提前修改并做另名保存，待导入数据成功后覆盖即可

重启新版本服务上线

[postgres@dywl ~]$ /usr/local/pgsql9.6.1/bin/pg_ctlrestart -D /home/postgres/data9.6.1

六、重新配置环境参数及清理旧版本物理文件

修改目录软连接指向

[postgres@dywl~]$ rm /home/postgres/data   #注意最后面没加“/”
[postgres@dywl~]$ ln -s /home/postgres/data9.6.1 /home/postgres/data
[postgres@dywl~]$ rm /usr/local/pgsql #注意最后面没加“/”
[postgres@dywl~]$ ln -s /usr/local/pgsql9.6.1 /usr/local/pgsql

删除掉旧版本物理文件
经过一段时间的线上运行之后，如果没什么问题，旧版本的物理文件就可以删除或移除了
```
[postgres@dywl~]$ rm /home/postgres/data9.4.1 -rf
[postgres@dywl~]$ rm /usr/local/pgsql9.4.1 -rf
[postgres@dywl~]$ rm /home/postgres/indexs/PG_9.4* -rf #其它表空间物理文件
```
如果你有归档的数据，那么归档的数据也要手动清理.建议有条件的情况下，最好是移到其它地方备份以防以后还需要使用。