postgresql利用pg_upgrade大版本升级（8.4到9.5）

本文利用pg_upgrade实现将8.4.18版本升级到9.5.0版本，8.4.18版本为RedHat系统自带pg数据库版本。

环境：Red Hat Enterprise Linux Server release 6.5 (Santiago) X86-64

查看手册看到可以利用pg_upgrade实现从8.4到9.5版本直接升级：

pg_upgrade supports upgrades from 8.3.X and later to the current major release of PostgreSQL,including snapshot and alpha releases.

下载postgresql9.5.0版本：

1.解压：

tar jxvf postgresql-9.5.0.tar.bz2

2.编译：

./configure--prefix=/usr/local/pgsql9.5.0--with-pgport=5434--with-wal-blocksize=16

这里在编译时带with-wal-blocksize=16配置参数导致后面升级出现错误，具体到后面解释。同时注意指定目录，方便后面管理

编译过程可能碰到错误：

configure:error:readlinelibrarynotfound
Ifyouhavereadlinealreadyinstalled,seeconfig.logfordetailsonthe
failure.Itispossiblethecompilerisntlookingintheproperdirectory.
Use--
without-readline
todisablereadlinesupport.

解决方法：

rpm-qa|grepreadline
yumsearchreadline
yum-yinstall-yreadline-deve

安装完毕readline，重新执行上面编译命令。

3.安装：

gmakeworld
gmakeinstall-world//此处要加world

4.初始化数据库：

4.1 创建data数据目录：（可另指定其他位置，此处将指定在pg安装目录）

cd/usr/local/pgsql9.5.0
mkdirdata

4.2 修改data目录权限，将其赋给postgres：

[root@localhostpgsql9.5.0]#chown-Rpostgres:postgres/usr/local/pgsql9.5.0/data
[root@localhostpgsql9.5.0]#chmod-R700/usr/local/pgsql9.5.0/data

4.3 初始化：

-bash-4.1$/usr/local/pgsql9.5.0/bin/initdb-EUTF8-D/usr/local/pgsql9.5.0/data--locale=C-Upostgres-W
//locale=C这里在升级时报错，后续具体解释

执行后：

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".
Data page checksums are disabled.
....
初始化过程中会要求设置一个新的密码：postgres
...
Success. You can now start the database server using:
 /usr/local/pgsql9.5.0/bin/pg_ctl -D /usr/local/pgsql9.5.0/data -l logfile start

注意:

初始化需要在postgres用户下进行，不能再root下：

[root@localhostpgsql9.5.0]#/usr/local/pgsql9.5.0/bin/initdb-EUTF8-D/usr/local/pgsql9.5.0/data--locale=C-Upostgres-W
initdb:cannotberunasroot
Pleaselogin(using,e.g.,"su")asthe(unprivileged)userthatwill
owntheserverprocess.

4.3 安装pg_upgrade插件：

cdcontrib
gamke
gmakeinstall

：其他插件类似安装，以安装第三方插件函数调试工具pldebugger为示例：http://my.oschina.net/ensn/blog/644727

5.修改postgresql.conf和pg_hba.conf文件：

因为升级需要多次连接新老集群数据库实例,所以修改为使用本地trust认证.
host all all 127.0.0.1/32 trust

端口使用注意使用不同的监听端口.（使用编译时候的指定端口：5434）

6.停老库：(新库没有启动，故这里不停)

-bash-4.1$pg_ctlstop-mfast-D/var/lib/pgsql/data

waitingforservertoshutdown....done
serverstopped

7.更新：

首先创建一个用户执行升级的目录，该目录权限给postgres用户（执行更新时，会产一些升级文件，创建upgrade目录存储该些文件）

su-root
cd/usr/local/pgsql9.5.0
mkdirupgrade
chownpostgres：postgresupgrade
su-postgres
cd/usr/local/pgsql9.5.0/upgrade

7.1检验更新：

-bash-4.1$/usr/local/pgsql9.5.0/bin/pg_upgrade-c-b/usr/bin-B/usr/local/pgsql9.5.0/bin-d/var/lib/pgsql/data-D/usr/local/pgsql9.5.0/data-p5432-P5434-Upostgres
//-c表示检验，-b/-B表示老/新库bin目录，-d/-D表示老/新库数据目录，具体命令参考手册

检验时碰见以下问题：

1).wal-blocksize 问题

该问题是由前后两个版本wal-blocksize不同，因为之前在安装新版本时指定了wal-blocksize的大小为16k，但老版本是默认安装的，默认大小为8k，该参数大小只能在安装时改变，故需要重新安装pg数据库来解决该问题。

2).lc_collate values for database "postgres" do not match: old "en_US.UTF-8",new "C" Failure,exiting

解决：删除data数据目录，重新做一次初始化，修改locale=en_US.UTF-8

/usr/local/pgsql9.5.0/bin/initdb-EUTF8-D/usr/local/pgsql9.5.0/data--locale=en_US.UTF-8-Upostgres-W

解决以上问题以后重新检验更新:

-bash-4.1$/usr/local/pgsql9.5.0/bin/pg_upgrade-c-b/usr/bin-B/usr/local/pgsql9.5.0/bin-d/var/lib/pgsql/data-D/usr/local/pgsql9.5.0/data-p5432-P5434-Upostgres

执行后：

PerformingConsistencyChecks
-----------------------------
Checkingclusterversionsok
Checkingdatabaseuseristheinstalluserok
Checkingdatabaseconnectionsettingsok
Checkingforpreparedtransactionsok
Checkingforreg*systemOIDuserdatatypesok
Checkingforcontrib/isnwithbigint-passingmismatchok
Checkingforinvalid"line"usercolumnsok
Checkingforlargeobjectsok
Checkingforpresenceofrequiredlibrariesok
Checkingdatabaseuseristheinstalluserok
Checkingforpreparedtransactionsok
*Clustersarecompatible*

以上说明可以执行更新。

7.2 执行更新

-bash-4.1$
/usr/local/pgsql9.5.0/bin/pg_upgrade-b/usr/bin-B
/usr/local/pgsql9.5.0/bin-d/var/lib/pgsql/data-D
/usr/local/pgsql9.5.0/data-p5432-P5434

pg_upgrade有两种升级方式，一个是缺省的通过拷贝数据文件到新的data目录下，一个是创建硬链接。拷贝的方式升级较慢，但是原库还可用；硬链接的方式升级较快，但是原库不可用。以上是缺省的方式，硬链接方式升级的命令只需要添加--link

PerformingConsistencyChecks
-----------------------------
Checkingclusterversionsok
Checkingdatabaseuseristheinstalluserok
Checkingdatabaseconnectionsettingsok
Checkingforpreparedtransactionsok
Checkingforreg*systemOIDuserdatatypesok
Checkingforcontrib/isnwithbigint-passingmismatchok
Checkingforinvalid"line"usercolumnsok
Checkingforlargeobjectsok
Creatingdumpofglobalobjectsok
Creatingdumpofdatabaseschemas
ok
Checkingforpresenceofrequiredlibrariesok
Checkingdatabaseuseristheinstalluserok
Checkingforpreparedtransactionsok
Ifpg_upgradefailsafterthispoint,youmustre-initdbthe
newclusterbeforecontinuing.
PerformingUpgrade
------------------
Analyzingallrowsinthenewclusterok
Freezingallrowsonthenewclusterok
Deletingfilesfromnewpg_clogok
Copyingoldpg_clogtonewserverok
SettingnexttransactionIDandepochfornewclusterok
Deletingfilesfromnewpg_multixact/offsetsok
SettingoldestmultixactIDonnewclusterok
ResettingWALarchivesok
Settingfrozenxidandminmxidcountersinnewclusterok
Restoringglobalobjectsinthenewclusterok
Restoringdatabaseschemasinthenewcluster
ok
Settingminmxidcounterinnewclusterok
Creatingnewly-requiredTOASTtablesok
Copyinguserrelationfiles
ok
SettingnextOIDfornewclusterok
Syncdatadirectorytodiskok
Creatingscripttoanalyzenewclusterok
Creatingscripttodeleteoldclusterok
Checkingforlargeobjectsok
UpgradeComplete
----------------
Optimizerstatisticsarenottransferredbypg_upgradeso,onceyoustartthenewserver,considerrunning:
./analyze_new_cluster.sh
Runningthisscriptwilldeletetheoldcluster'sdatafiles:

./delete_old_cluster.sh

升级过程是拷贝原来目录的数据到新版本指定的目录中，最后提示生成两个脚本，一个是analyze_new_cluster.sh，需要在新版本中执行，用 来收集统计信息，另一个是 delete_old_cluster.sh，用来删除旧版本集群数据，当然为了安全起见可以等系统运行几天没问题再来删除。

8.启动新数据库

-bash-4.1$pg_ctlstart-D/usr/local/pgsql9.5.0/data

报错：

serverstarting
-bash-4.1$FATAL:unrecognizedconfigurationparameter"dynamic_shared_memory_type"

解决方法：

原因是因为版本不一致导致，可能是我的主机上面被我编译了两个版本导致的，之前８.４postgresql.conf文件中没有该参数，升级后9.5的配置文件中有该参数，故出现不一致，
通过绝对路径启动数据库：

[root@localhost~]#su-postgres
-bash-4.1$/usr/local/pgsql9.5.0/bin/pg_ctlstart-D/usr/local/pgsql9.5.0/data
serverstarting
-bash-4.1$LOG:databasesystemwasshutdownat2016-03-0810:55:18CST
LOG:MultiXactmemberwraparoundprotectionsarenowenabled
LOG:autovacuumlauncherstarted
LOG:databasesystemisreadytoacceptconnections

进入数据库，此时psql无法进入：

-bash-4.1$psql
psql:couldnotconnecttoserver:Nosuchfileordirectory
Istheserverrunninglocallyandaccepting
connectionsonUnixdomainsocket"/tmp/.s.PGSQL.5432"?

通过指定psql路径后进入：

-bash-4.1$/usr/local/pgsql9.5.0/bin/psql--指定路径
psql(9.5.0)
Type"help"forhelp.
postgres=#

9.执行收集统计信息的脚本：

-bash-4.1$/usr/local/pgsql9.5.0/upgrade/analyze_new_cluster.sh
Thisscriptwillgenerateminimaloptimizerstatisticsrapidlysoyoursystemisusable,andthengatherstatisticstwicemorewithincreasingaccuracy.
Whenitisdone,yoursystemwillhavethedefaultlevelofoptimizerstatistics.
IfyouhaveusedALTERTABLEtomodifythestatisticstargetforanytables,youmightwanttoremovethemandrestorethemafterrunningthisscriptbecausetheywill
delayfaststatisticsgeneration.Ifyouwouldlikedefaultstatisticsasquicklyaspossible,cancelthisscriptandrun:"/usr/local/pgsql9.5.0/bin/vacuumdb"
--all--analyze-onlyvacuumdb:processingdatabase"gedbs":Generatingminimaloptimizer
statistics(1target)vacuumdb:processingdatabase"postgres":Generatingminimaloptimizer
statistics(1target)vacuumdb:processingdatabase"template1":Generatingminimaloptimizer
statistics(1target)vacuumdb:processingdatabase"gedbs":Generatingmediumoptimizer
statistics(10targets)vacuumdb:processingdatabase"postgres":Generatingmediumoptimizer
statistics(10targets)vacuumdb:processingdatabase"template1":Generatingmediumoptimizer
statistics(10targets)vacuumdb:processingdatabase"gedbs":Generatingdefault(full)
optimizerstatisticsvacuumdb:processingdatabase"postgres":Generatingdefault(full)
optimizerstatisticsvacuumdb:processingdatabase"template1":Generatingdefault(full)
optimizerstatistics
Done

10.修改bash_profile文件中的PGDATA， PGPORT等参数（对postgres用户有效，客户端用其他用户登陆可能无效，修改/etc/profile对所有用户有效），修改PATH搜索路径，
不然在执行psql是会报版本不一致警告：

WARNING:psqlversion8.4,serverversion9.5.

11.启动数据库，pgadmin连接

报错：

configureFATAL:passwordauthenticationfailedforuser"postgres"

解决方法：

修改postgres用户密码，

ALTERUSERpostgresPASSWORD'newPassword';

参考：http://stackoverflow.com/questions/7695962/postgresql-password-authentication-failed-for-user-postgres

12.新数据库稳定后，删除老版本软件

[root@localhostupgrade]#catdelete_old_cluster.sh
#!/bin/sh
rm-rf'/var/lib/pgsql/data'

$./delete_old_cluster.sh

参考：

http://www.postgresql.org/docs/current/static/pgupgrade.html

http://my.oschina.net/liuyuanyuangogo/blog/500229
http://xmarker.blog.163.com/blog/static/22648405720145203428811/
http://906179271.iteye.com/blog/2263218
http://francs3.blog.163.com/blog/static/40576727201519521050/
http://wangwei.info/upgrade-postgresql-centos/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!