小编给大家分享一下更新postgresql数据库的方法，相信大部分人都还不怎么了解，因此分享这边文章给大家学习，希望大家阅读完这篇文章后大所收获，下面让我们一起去学习方法吧！

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、升级过程

首先确认的是，原来的数据库版本是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数据库的方法的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！