PostgreSQL升级之pg_upgrade升级

PostgreSQL中的升级，如果针对小版本的升级，比如9.6.1升级到9.6.2(当前的最新版本)，只需要用9.6.2版本的软件替换9.6.1版本的软件即可，不需要做额外的操作，因为整个大版本是相互兼容的，内部存储形式也是兼容的。但如果涉及到跨大版本升级比如9.4.11升级到9.6.2，这种直接替换软件就不行了，因为跨版本的内部存储形式发生了变化。

官方给了三种升级的方式来解决跨版本升级：

• pg_dumpall
  

• pg_upgrade

• 通过复制

pg_dumpall是一种把数据从旧版本逻辑导出，再导入新版本的方法，就是一个导出导入的过程。

通过复制的方式是创建一个高版本的从库，等数据同步完后主变备，备变主，达到升级的目的。

再一种是通过pg_upgrade命令的升级方式，它是一种快速升级的方法，通过创建新的系统表并使用旧的用户表的方式进行升级。它又分为两种方式：原地升级和非原地升级，原地升级需要指定--link参数。

下面介绍一下使用pg_upgrade做升级的大体步骤：

示例是从9.4.11升级到9.6.2。

1、安装新版本软件

新版本的软件需要保证与旧版本的软件在配置上兼容，pg_upgrade会在升级前检查pg_controldata，确保所有的设置是兼容的。

2、用新版本初始化一个新的数据库

[postgres@rhel7~]$/opt/pgsql-9.6.2/bin/initdb-D/pgdata-new/
Thefilesbelongingtothisdatabasesystemwillbeownedbyuser"postgres".
Thisusermustalsoowntheserverprocess.

Thedatabaseclusterwillbeinitializedwithlocale"en_US.UTF-8".
Thedefaultdatabaseencodinghasaccordinglybeensetto"UTF8".
Thedefaulttextsearchconfigurationwillbesetto"english".

Datapagechecksumsaredisabled.

fixingpermissionsonexistingdirectory/pgdata-new...ok
creatingsubdirectories...ok
selectingdefaultmax_connections...100
selectingdefaultshared_buffers...128MB
selectingdynamicsharedmemoryimplementation...posix
creatingconfigurationfiles...ok
runningbootstrapscript...ok
performingpost-bootstrapinitialization...ok
syncingdatatodisk...ok

WARNING:enabling"trust"authenticationforlocalconnections
Youcanchangethisbyeditingpg_hba.conforusingtheoption-A,or
--auth-localand--auth-host,thenexttimeyouruninitdb.

Success.Youcannowstartthedatabaseserverusing:

/opt/pgsql-9.6.2/bin/pg_ctl-D/pgdata-new/-llogfilestart

3、设置pg_hba.conf，保证pg_upgrade通过连接新旧两个库

4、停止旧库

#创建测试表
[postgres@rhel7~]$psql
psql(9.4.11)
Type"help"forhelp.
^
postgres=#createtablezx(idint);
CREATETABLE
postgres=#d
Listofrelations
Schema|Name|Type|Owner
--------+------+-------+----------
public|zx|table|postgres
(1row)

postgres=#insertintozxvalues(1);
INSERT01
postgres=#select*fromzx;
id
----
1
(1row)
#停止旧库
[postgres@rhel7~]$/opt/pgsql-9.4/bin/pg_ctlstop-D/usr/local/pgsql/data/
waitingforservertoshutdown....done
serverstopped

5、使用pg_upgrade执行升级

[postgres@rhel7~]$/opt/pgsql-9.6.2/bin/pg_upgrade-d/usr/local/pgsql/data/-D/pgdata-new/-b/opt/pgsql-9.4/bin/-B/opt/pgsql-9.6.2/bin/
PerformingConsistencyChecks
-----------------------------
Checkingclusterversionsok
Checkingdatabaseuseristheinstalluserok
Checkingdatabaseconnectionsettingsok
Checkingforpreparedtransactionsok
Checkingforreg*systemOIDuserdatatypesok
Checkingforcontrib/isnwithbigint-passingmismatchok
Checkingforrolesstartingwith'pg_'ok
Creatingdumpofglobalobjectsok
Creatingdumpofdatabaseschemas
ok
Checkingforpresenceofrequiredlibrariesok
Checkingdatabaseuseristheinstalluserok
Checkingforpreparedtransactionsok

Ifpg_upgradefailsafterthispoint,youmustre-initdbthe
newclusterbeforecontinuing.

PerformingUpgrade
------------------
Analyzingallrowsinthenewclusterok
Freezingallrowsonthenewclusterok
Deletingfilesfromnewpg_clogok
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

UpgradeComplete
----------------
Optimizerstatisticsarenottransferredbypg_upgradeso,onceyoustartthenewserver,considerrunning:
./analyze_new_cluster.sh

Runningthisscriptwilldeletetheoldcluster'sdatafiles:
./delete_old_cluster.sh

介绍下使用的参数-b指定旧版本软件的bin目录-B指定新版本软件的bin目录，-d指定旧版本对应的数据目录，-D指定新版本对应的数据目录。

6、启动新版本数据库并做检查

[postgres@rhel7~]$/opt/pgsql-9.6.2/bin/pg_ctlstart-D/pgdata-new/-llogfile
serverstarting
[postgres@rhel7~]$psql
psql(9.6.2)
Type"help"forhelp.

postgres=#d
Listofrelations
Schema|Name|Type|Owner
--------+------+-------+----------
public|zx|table|postgres
(1row)

postgres=#select*fromzx;
id
----
1
(1row)

7、恢复配置文件如pg_hba.conf、postgresql.conf等

8、收集统计信息

由于升级过程中不会把统计信息传到新库系统表中，需要重新收集统计信息。pg_upgrade的最给出了收集统计信息的脚本：

[postgres@rhel7~]$./analyze_new_cluster.sh
Thisscriptwillgenerateminimaloptimizerstatisticsrapidly
soyoursystemisusable,andthengatherstatisticstwicemore
withincreasingaccuracy.Whenitisdone,yoursystemwill
havethedefaultlevelofoptimizerstatistics.

IfyouhaveusedALTERTABLEtomodifythestatisticstargetfor
anytables,youmightwanttoremovethemandrestorethemafter
runningthisscriptbecausetheywilldelayfaststatisticsgeneration.

Ifyouwouldlikedefaultstatisticsasquicklyaspossible,cancel
thisscriptandrun:
"/opt/pgsql-9.6.2/bin/vacuumdb"--all--analyze-only

vacuumdb:processingdatabase"postgres":Generatingminimaloptimizerstatistics(1target)
vacuumdb:processingdatabase"template1":Generatingminimaloptimizerstatistics(1target)
vacuumdb:processingdatabase"postgres":Generatingmediumoptimizerstatistics(10targets)
vacuumdb:processingdatabase"template1":Generatingmediumoptimizerstatistics(10targets)
vacuumdb:processingdatabase"postgres":Generatingdefault(full)optimizerstatistics
vacuumdb:processingdatabase"template1":Generatingdefault(full)optimizerstatistics

Done

9、升级成功后删除旧版本软件和数据。

官方文档：https://www.postgresql.org/docs/9.6/static/pgupgrade.html

https://www.postgresql.org/docs/9.6/static/upgrading.html

（编辑：李大同）

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