小编给大家分享一下升级postgresql数据库的方法，希望大家阅读完这篇文章后大所收获，下面让我们一起去探讨吧！

1、背景

一般来说，数据库的升级很少遇到，除非确实出现了新的业务需求或者系统bug无法解决的情况下才选择升级。本文基本测试了一下pg9.6升级到10.5的过程，没有遇到太大的问题。

2、升级

之前博客中有介绍，其实postgresql和mysql的安装逻辑结构很相似，数据和程序时分开的，启动时候指定启动的数据目录，当然数据目录是可以放在配置文件中。今天测试了一下使用pg10.5的版本去打开pg9.6版本初始化的数据库出现了错误，错误中显示的是数据库文件不兼容。

[postgres@pgmaster~]$pg_ctl-D/data/pgdata/startwaitingforservertostart....2019-10-0816:56:20.203CST[35441]FATAL:databasefilesareincompatiblewithserver2019-10-0816:56:20.203CST[35441]DETAIL:ThedatadirectorywasinitializedbyPostgreSQLversion9.6,whichisnotcompatiblewiththisversion10.5.stoppedwaitingpg_ctl:couldnotstartserverExaminethelogoutput.

2.1 升级工具

在程序的bin目录下，提供了很多的数据库工具，有一个pg_upgrade的工具就是专门用于数据库升级的。关于该工具可以使用帮助命令来查看具体的用法：

[postgres@pgmasterpgdata]$pg_upgrade--helppg_upgradeupgradesaPostgreSQLclustertoadifferentmajorversion.Usage:pg_upgrade[OPTION]...Options:-b,--old-bindir=BINDIRoldclusterexecutabledirectory-B,--new-bindir=BINDIRnewclusterexecutabledirectory-c,--checkcheckclustersonly,don'tchangeanydata-d,--old-datadir=DATADIRoldclusterdatadirectory-D,--new-datadir=DATADIRnewclusterdatadirectory-j,--jobsnumberofsimultaneousprocessesorthreadstouse-k,--linklinkinsteadofcopyingfilestonewcluster-o,--old-options=OPTIONSoldclusteroptionstopasstotheserver-O,--new-options=OPTIONSnewclusteroptionstopasstotheserver-p,--old-port=PORToldclusterportnumber(default50432)-P,--new-port=PORTnewclusterportnumber(default50432)-r,--retainretainSQLandlogfilesaftersuccess-U,--username=NAMEclustersuperuser(default"postgres")-v,--verboseenableverboseinternallogging-V,--versiondisplayversioninformation,thenexit-?,--helpshowthishelp,thenexitBeforerunningpg_upgradeyoumust:createanewdatabasecluster(usingthenewversionofinitdb)shutdownthepostmasterservicingtheoldclustershutdownthepostmasterservicingthenewclusterWhenyourunpg_upgrade,youmustprovidethefollowinginformation:thedatadirectoryfortheoldcluster(-dDATADIR)thedatadirectoryforthenewcluster(-DDATADIR)the"bin"directoryfortheoldversion(-bBINDIR)the"bin"directoryforthenewversion(-BBINDIR)Forexample:pg_upgrade-doldCluster/data-DnewCluster/data-boldCluster/bin-BnewCluster/binor$exportPGDATAOLD=oldCluster/data$exportPGDATANEW=newCluster/data$exportPGBINOLD=oldCluster/bin$exportPGBINNEW=newCluster/bin$pg_upgradeReportbugsto<pgsql-bugs@postgresql.org>.

帮助文件中，提到了使用pg_upgrade工具前，必须创建一个新的数据库，并且是已经初始化的，同时关闭原来的数据库和新的数据库。使用pg_upgrade时候，必须要加上前后版本的data目录和bin目录。

2.2 升级过程

首先确认的是，原来的数据库版本是pg9.6，数据目录在/data/pgdata。然后，安装完pg10.5后，不要初始化目录。

将原来的9.6版本数据目录重命名为pgdata.old

mv/data/pgdata/data/pgdata.old

在/data/下创建一个pgdata目录，作为新版本的数据库数据目录，需要注意的是，这个目录权限是700，owner是postgres

cd/data/mkdirpgdatachmod700pgdatachown-Rpostgres.postgrespgdata

使用pg10.5的initdb初始化/data/pgdata目录

initdb-D/data/pgdata

进行升级check，注意后面加上-c，这一步只是检查不会实际执行升级。所有项都是ok即认为是可以升级。

[postgres@pgmaster~]$pg_upgrade-b/usr/local/pgsql-9.6/bin-B/usr/local/pgsql/bin/-d/data/pgdata.old/-D/data/pgdata-p5432-P5432-cPerformingConsistencyChecks-----------------------------CheckingclusterversionsokCheckingdatabaseuseristheinstalluserokCheckingdatabaseconnectionsettingsokCheckingforpreparedtransactionsokCheckingforreg*datatypesinusertablesokCheckingforcontrib/isnwithbigint-passingmismatchokCheckingforinvalid"unknown"usercolumnsokCheckingforhashindexesokCheckingforpresenceofrequiredlibrariesokCheckingdatabaseuseristheinstalluserokCheckingforpreparedtransactionsok*Clustersarecompatible*

执行升级。即在上一步去掉-c，需要注意的是这一步根据数据库的大小执行时间长短不一，执行完毕后会产生两个脚本analyze_new_cluster.sh和delete_old_cluster.sh，根据实际需要来进行执行，一般都会执行第一个脚本，第二个不建议执行，以防需要回滚升级，保留原来的数据目录比较保险。

[postgres@pgmaster~]$pg_upgrade-b/usr/local/pgsql-9.6/bin-B/usr/local/pgsql/bin/-d/data/pgdata.old/-D/data/pgdata-p5432-P5432PerformingConsistencyChecks-----------------------------CheckingclusterversionsokCheckingdatabaseuseristheinstalluserokCheckingdatabaseconnectionsettingsokCheckingforpreparedtransactionsokCheckingforreg*datatypesinusertablesokCheckingforcontrib/isnwithbigint-passingmismatchokCheckingforinvalid"unknown"usercolumnsokCreatingdumpofglobalobjectsokCreatingdumpofdatabaseschemasokCheckingforpresenceofrequiredlibrariesokCheckingdatabaseuseristheinstalluserokCheckingforpreparedtransactionsokIfpg_upgradefailsafterthispoint,youmustre-initdbthenewclusterbeforecontinuing.PerformingUpgrade------------------AnalyzingallrowsinthenewclusterokFreezingallrowsinthenewclusterokDeletingfilesfromnewpg_xactokCopyingoldpg_clogtonewserverokSettingnexttransactionIDandepochfornewclusterokDeletingfilesfromnewpg_multixact/offsetsokCopyingoldpg_multixact/offsetstonewserverokDeletingfilesfromnewpg_multixact/membersokCopyingoldpg_multixact/memberstonewserverokSettingnextmultixactIDandoffsetfornewclusterokResettingWALarchivesokSettingfrozenxidandminmxidcountersinnewclusterokRestoringglobalobjectsinthenewclusterokRestoringdatabaseschemasinthenewclusterokCopyinguserrelationfilesokSettingnextOIDfornewclusterokSyncdatadirectorytodiskokCreatingscripttoanalyzenewclusterokCreatingscripttodeleteoldclusterokCheckingforhashindexesokUpgradeComplete----------------Optimizerstatisticsarenottransferredbypg_upgradeso,onceyoustartthenewserver,considerrunning:./analyze_new_cluster.shRunningthisscriptwilldeletetheoldcluster'sdatafiles:./delete_old_cluster.sh

执行脚本前，需要先启动数据库pg_ctl -D /data/pgdata start

[postgres@pgmaster~]$pg_ctl-D/data/pgdatastartwaitingforservertostart....2019-10-0817:18:51.402CST[35827]LOG:listeningonIPv6address"::1",port54322019-10-0817:18:51.402CST[35827]LOG:listeningonIPv4address"127.0.0.1",port54322019-10-0817:18:51.408CST[35827]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.5432"2019-10-0817:18:51.437CST[35828]LOG:databasesystemwasshutdownat2019-10-0817:16:11CST2019-10-0817:18:51.442CST[35827]LOG:databasesystemisreadytoacceptconnectionsdoneserverstarted

执行脚本./analyze_new_cluster.sh，从运行日志来看，主要是创建统计信息

[postgres@pgmaster~]$./analyze_new_cluster.shThisscriptwillgenerateminimaloptimizerstatisticsrapidlysoyoursystemisusable,andthengatherstatisticstwicemorewithincreasingaccuracy.Whenitisdone,yoursystemwillhavethedefaultlevelofoptimizerstatistics.IfyouhaveusedALTERTABLEtomodifythestatisticstargetforanytables,youmightwanttoremovethemandrestorethemafterrunningthisscriptbecausetheywilldelayfaststatisticsgeneration.Ifyouwouldlikedefaultstatisticsasquicklyaspossible,cancelthisscriptandrun:"/usr/local/pgsql/bin/vacuumdb"--all--analyze-onlyvacuumdb:processingdatabase"postgres":Generatingminimaloptimizerstatistics(1target)vacuumdb:processingdatabase"template1":Generatingminimaloptimizerstatistics(1target)vacuumdb:processingdatabase"test":Generatingminimaloptimizerstatistics(1target)vacuumdb:processingdatabase"postgres":Generatingmediumoptimizerstatistics(10targets)vacuumdb:processingdatabase"template1":Generatingmediumoptimizerstatistics(10targets)vacuumdb:processingdatabase"test":Generatingmediumoptimizerstatistics(10targets)vacuumdb:processingdatabase"postgres":Generatingdefault(full)optimizerstatisticsvacuumdb:processingdatabase"template1":Generatingdefault(full)optimizerstatisticsvacuumdb:processingdatabase"test":Generatingdefault(full)optimizerstatisticsDone

至此，查看version，发现已经由原来的9.6升级为10.5，升级结束。

postgres=#selectversion();version---------------------------------------------------------------------------------------------------------PostgreSQL10.5onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-39),64-bit(1row)

看完了这篇文章，相信你对升级postgresql数据库的方法有了一定的了解，想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！