postgresql如何升级

1、背景

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

2、升级

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

[postgres@pgmaster~]$pg_ctl-D/data/pgdata/start
waitingforservertostart....2019-10-0816:56:20.203CST[35441]FATAL:databasefilesareincompatiblewithserver
2019-10-0816:56:20.203CST[35441]DETAIL:ThedatadirectorywasinitializedbyPostgreSQLversion9.6,whichis
notcompatiblewiththisversion10.5.
stoppedwaiting
pg_ctl:couldnotstartserver
Examinethelogoutput.

2.1 升级工具

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

[postgres@pgmasterpgdata]$pg_upgrade--help
pg_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,thenexit
Beforerunningpg_upgradeyoumust:
createanewdatabasecluster(usingthenewversionofinitdb)
shutdownthepostmasterservicingtheoldcluster
shutdownthepostmasterservicingthenewcluster
Whenyourunpg_upgrade,youmustprovidethefollowinginformation:
thedatadirectoryfortheoldcluster(-dDATADIR)
thedatadirectoryforthenewcluster(-DDATADIR)
the"bin"directoryfortheoldversion(-bBINDIR)
the"bin"directoryforthenewversion(-BBINDIR)
Forexample:
pg_upgrade-doldCluster/data-DnewCluster/data-boldCluster/bin-BnewCluster/bin
or
$exportPGDATAOLD=oldCluster/data
$exportPGDATANEW=newCluster/data
$exportPGBINOLD=oldCluster/bin
$exportPGBINNEW=newCluster/bin
$pg_upgrade
Reportbugsto<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/
mkdirpgdata
chmod700pgdata
chown-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-c
PerformingConsistencyChecks
-----------------------------
Checkingclusterversionsok
Checkingdatabaseuseristheinstalluserok
Checkingdatabaseconnectionsettingsok
Checkingforpreparedtransactionsok
Checkingforreg*datatypesinusertablesok
Checkingforcontrib/isnwithbigint-passingmismatchok
Checkingforinvalid"unknown"usercolumnsok
Checkingforhashindexesok
Checkingforpresenceofrequiredlibrariesok
Checkingdatabaseuseristheinstalluserok
Checkingforpreparedtransactionsok
*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-P5432
PerformingConsistencyChecks
-----------------------------
Checkingclusterversionsok
Checkingdatabaseuseristheinstalluserok
Checkingdatabaseconnectionsettingsok
Checkingforpreparedtransactionsok
Checkingforreg*datatypesinusertablesok
Checkingforcontrib/isnwithbigint-passingmismatchok
Checkingforinvalid"unknown"usercolumnsok
Creatingdumpofglobalobjectsok
Creatingdumpofdatabaseschemas
ok
Checkingforpresenceofrequiredlibrariesok
Checkingdatabaseuseristheinstalluserok
Checkingforpreparedtransactionsok
Ifpg_upgradefailsafterthispoint,youmustre-initdbthe
newclusterbeforecontinuing.
PerformingUpgrade
------------------
Analyzingallrowsinthenewclusterok
Freezingallrowsinthenewclusterok
Deletingfilesfromnewpg_xactok
Copyingoldpg_clogtonewserverok
SettingnexttransactionIDandepochfornewclusterok
Deletingfilesfromnewpg_multixact/offsetsok
Copyingoldpg_multixact/offsetstonewserverok
Deletingfilesfromnewpg_multixact/membersok
Copyingoldpg_multixact/memberstonewserverok
SettingnextmultixactIDandoffsetfornewclusterok
ResettingWALarchivesok
Settingfrozenxidandminmxidcountersinnewclusterok
Restoringglobalobjectsinthenewclusterok
Restoringdatabaseschemasinthenewcluster
ok
Copyinguserrelationfiles
ok
SettingnextOIDfornewclusterok
Syncdatadirectorytodiskok
Creatingscripttoanalyzenewclusterok
Creatingscripttodeleteoldclusterok
Checkingforhashindexesok
UpgradeComplete
----------------
Optimizerstatisticsarenottransferredbypg_upgradeso,
onceyoustartthenewserver,considerrunning:
./analyze_new_cluster.sh
Runningthisscriptwilldeletetheoldcluster'sdatafiles:
./delete_old_cluster.sh

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

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

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

[postgres@pgmaster~]$./analyze_new_cluster.sh
Thisscriptwillgenerateminimaloptimizerstatisticsrapidly
soyoursystemisusable,andthengatherstatisticstwicemore
withincreasingaccuracy.Whenitisdone,yoursystemwill
havethedefaultlevelofoptimizerstatistics.
IfyouhaveusedALTERTABLEtomodifythestatisticstargetfor
anytables,youmightwanttoremovethemandrestorethemafter
runningthisscriptbecausetheywilldelayfaststatisticsgeneration.
Ifyouwouldlikedefaultstatisticsasquicklyaspossible,cancel
thisscriptandrun:
"/usr/local/pgsql/bin/vacuumdb"--all--analyze-only
vacuumdb: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)optimizerstatistics
vacuumdb:processingdatabase"template1":Generatingdefault(full)optimizerstatistics
vacuumdb:processingdatabase"test":Generatingdefault(full)optimizerstatistics
Done

至此，查看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)

Python学习网，大量的免费PostgreSQL入门教程，欢迎在线学习！