作者: 阿弟

为了使用让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

二、新版本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 #其它表空间物理文件
  

  如果你有归档的数据，那么归档的数据也要手动清理.建议有条件的情况下，最好是移到其它地方备份以防以后还需要使用。