[PostgreSQL]使用pg_upgrade工具为PostgreSQL进行版本升级

公司的测试环境和生产环境都是9.2.4版本，而开发环境是9.0.18版本。

 

PS：

不知道公司里的其它同事，是如何做数据库迁移的，利用第三方可视化工具备份数据库，然后恢复到其它高版本的环境？反正我没成功过。

我是用cmd命令调用PostgreSQL的pg_dump+pgsql工具操作才成功的。

在自己电脑上，切换到PostgreSQL的bin目录
cd /d C:\Program Files\PostgreSQL\9.6\bin
备份指定数据库，确认后输入密码(口令)完成备份
pg_dump -h 192.168.23.62 -U postgres GL > d:\gl.bak
还原到指定数据库，确认后输入密码(口令)完成还原
psql -h 192.168.42.3 -U postgres -d GL < d:\gl.bak

其中-U后面跟着的是数据库账户名，这里我用的具有管理员权限的账户。

而-d后面跟着的是要备份/恢复的数据库名称。

再后面就是要备份/恢复的数据库备份文件路径了。

 

OK，啰嗦这么多，进入正文：

现在想要为开发环境的PostgreSQL数据库进行版本升级，按照以往的Microsoft SQL Server (MSSQL) 经验，我都是下载更高版本的安装包/或更新补丁包，一路下一步升级完成版本更新，整个过程无须创建另外一个数据库引擎实例什么的，而是在原有的数据库引擎实例的基础上进行无缝升级。

所以，当我把PostgreSQL 9.2.4安装包文件postgresql-9.2.4-1-windows-x64.exe扔到开发环境中，点击运行进行安装时，一脸懵逼....

居然没有更新升级操作，只有全新安装(等于在同一个服务器上安装了两个端口号不一样，版本不一样的PostgreSQL数据库引擎实例)？？？这很不科学，我不信，我不听...

没办法我还是让它完成安装，之后在官网文档找解释，然后我扒到了这个说明：https://www.postgresql.org/docs/9.2/static/pgupgrade.html

官网介绍可以使用目标更新版本的pg_upgrade工具命令进行升级。

命令格式如下：

pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir [option...]

我没有完全按照官网的流程来走，比如我没有执行以下命令，因为我没有名为postgres的Windows系统账户：

RUNAS /USER:postgres "CMD.EXE"
SET PATH=%PATH%;C:\Program Files\PostgreSQL\9.2\bin;

而是另外建了一个名为pg_upgrade的bat批处理文件，然后右键以管理员身份运行这个bat批处理文件。

pg_upgrade.bat文件内容是：

set Path = %Path%;C:\Program Files\PostgreSQL\9.2\bin
cd /d "C:\Program Files\PostgreSQL\9.2\bin"
"C:\Program Files\PostgreSQL\9.2\bin\pg_upgrade.exe" --old-datadir "C:\Program Files\PostgreSQL\9.0\data" --new-datadir "C:\Program Files\PostgreSQL\9.2\data" --old-bindir "C:\Program Files\PostgreSQL\9.0\bin" --new-bindir "C:\Program Files\PostgreSQL\9.2\bin"

但是运行后，发生错误，其中命令提示符(控制台)显示的错误是：

> Performing Consistency Checks
> -----------------------------
> Checking cluster versions                                   ok
>
> *failure*
> Consult the last few lines of "pg_upgrade_server_start.log" or "pg_upgrade_server.log" for
> the probable cause of the failure.
>
> connection to database failed: could not connect to server: Connection refused (0x0000274D/10061)
>     Is the server running on host "localhost" (::1) and accepting
>     TCP/IP connections on port 50432?
> could not connect to server: Connection refused (0x0000274D/10061)
>     Is the server running on host "localhost" (127.0.0.1) and accepting
>     TCP/IP connections on port 50432?
>
> could not connect to old postmaster started with the command:
> "C:\Program Files\PostgreSQL\9.0\bin\pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.0\data" -o "-p 50432 -b " start

pg_upgrade_server_start.log关键错误如下：

command: "C:\Program Files\PostgreSQL\9.0\bin\pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.0\data" -o "-p 50432 -b " start >> "pg_upgrade_server_start.log" 2>&1
Access is denied.
waiting for server to start........ stopped waiting

经过第一次百度，找到了这个链接：https://zhidao.baidu.com/question/2011319586576805908.html

根据这个链接的指导，我选取一些认为没有按照步骤做的地方，认真检查一遍，并且修改过来（PS：当时看到“未使用管理员权限运行命令提示符（CMD）”却没有在意，因为我是以管理员身份运行的，我认为我给了它最大权限去运行，这也就导致我花费较长的时间才解决这个数据库升级的问题）。

1.确保9.0和9.2的Windows服务已经停了。

postgresql-x64-9.0和postgresql-x64-9.2这两个服务。

 

2.在C:\Program Files\PostgreSQL\9.0\data和C:\Program Files\PostgreSQL\9.2\data目录下找到pg_hba.conf文件，把这两边的pg_hba.conf文件配置里的"md5"改成"trust"，注意这里要小写，我之前写成首字母大写Trust，坑逼...

PostgreSQL 9.0 pg_hba.conf原配置：

PostgreSQL 9.0 pg_hba.conf改后配置：

 

PostgreSQL 9.2 pg_hba.conf原配置：

PostgreSQL 9.2 pg_hba.conf改后配置：

这里需要注意的是，高亮的那条0.0.0.0/0是原配置里没有，我是等到后面折腾出新问题加上去的。

之所以要加上去的原因是，端口监听问题，外部总是无法连接到9.2这个实例。所以你现在提前加上去最好。

至于最后面那两条不用改，因为它们是被#号注释掉的，不会起作用，所以没影响，当然你太闲非要改，也没人拦你。

 

3.把Path变量赋值顺序对调一下。

set Path = C:\Program Files\PostgreSQL\9.2\bin;%Path%
cd /d "C:\Program Files\PostgreSQL\9.2\bin"
"C:\Program Files\PostgreSQL\9.2\bin\pg_upgrade.exe" --old-datadir "C:\Program Files\PostgreSQL\9.0\data" --new-datadir "C:\Program Files\PostgreSQL\9.2\data" --old-bindir "C:\Program Files\PostgreSQL\9.0\bin" --new-bindir "C:\Program Files\PostgreSQL\9.2\bin"

 

这是看百度知道里的第二点解释，所以才这样做的。

 

重新以管理员身份运行这个改动后的pg_upgrade.bat文件。

依旧是报刚才那些错误。

再次百度无果，于是FQ上谷歌找答案，看到了这两个链接：

https://www.postgresql.org/message-id/flat/CAEB4t-OHNE95=n5U4ySsYkWipQsWeQuTBSJkaYJ63_1VzkzkhA@mail.gmail.com#CAEB4t-OHNE95=n5U4ySsYkWipQsWeQuTBSJkaYJ63_1VzkzkhA@mail.gmail.com

https://www.postgresql.org/message-id/54C141D4.6050909@getrailo.org

其中一个链接有解释：

大体意思就是在Windows Server上以系统管理员身份运行pg_upgrade工具更新会有BUG，而这个BUG出现在PostgreSQL 9.2,9.3,9.4这几个版本。

补救办法，就是另外新建一个非管理员账户，然后在以系统管理员账户登录Windows后，以非管理员账户运行这些命令。

所以我新建了一个名为postgres的非管理员(普通)账户，然后Win+R键打开运行窗口，输入命令：

RUNAS /USER:postgres "CMD.EXE"

 

输入该账户的密码后，以postgres用户身份打开命令提示符窗口，然后在该窗口内，运行pg_upgrade.bat

cd /d C:\
pg_upgrade.bat

 

这次，报另外一个错误：

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.

 

再次GOOGLE，找到这个链接：

https://www.postgresql.org/message-id/20130119150242.GE2857%40momjian.us

大体意思就是，到PostgreSQL安装目录下的data目录下，把postmaster.pid文件删掉即可。

我留意到，这个文件是在PostgreSQL的Windows服务启动后生成的，停止这个Windows服务就会被删除(消失)。

可能是我之前的pg_upgrade操作不当，导致这个postmaster.pid没有来得及被PostgreSQL清理掉。

而pg_upgrade的工作需要在PostgreSQL的Windows服务(数据库引擎实例)停止状态下进行。

所以当pg_upgrade运行检测到postmaster.pid文件存在后，误以为Windows服务还启动着，然后抛出这个错误，但其实Windows服务已经是停止的了。

 

到9.0和9.2的data目录下删掉postmaster.pid文件后，继续运行pg_upgrade.bat，这次等待十几分钟，终于运行成功。

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "line" user columns                    ok
Checking for large objects                                  ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Setting minmxid counter in new cluster                      ok
Creating newly-required TOAST tables                        ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok
Checking for large objects                                  ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.bat

Running this script will delete the old cluster's data files:
    delete_old_cluster.bat

 

然后它提示建议我们要运行analyze_new_cluster.bat和delete_old_cluster.bat。

但我只运行analyze_new_cluster.bat，至于delete_old_cluster.bat命令你自己看着运行。

输入命令：

cd /d C:\Program Files\PostgreSQL\9.2\bin
analyze_new_cluster.bat

 

等待完成后，用Navicat Premium连接到9.2，查看数据库是否都在，全部都在。

算是完成了99%了，剩下1%是奇葩的端口号问题。

相信大家也看到前面提到的端口监听问题，当时走到这一步后，在保持9.0的Windows服务(端口5432)停止的状态下，启动9.2的Windows服务(端口5433)。

然后在客户端用Navicat Premium通过(端口5433)连接9.2死活连接不上，改成5432端口后连接成功了。

但连接后，显示的PostgreSQL版本有问题，居然是9.0版本，诡异...

这不科学，难道把9.0的数据库迁移到9.2后，发生版本错乱了吗？

查原因(没查到)，改配置文件和注册表中的PostgreSQL端口配置，甚至把9.0的端口配置改成其它的端口号(比如9876)，都无法解决问题。

后面搞得无论用5432还是5433端口，都无法连接，郁闷...

之后静下心来，仔细比较9.0和9.2的pg_hba.conf文件的差异，发现9.0有0.0.0.0/0这一行配置项，而9.2没有，于是在9.2的pg_hba.conf中加进去。

然后重启9.2的Windows服务，结果通过5433端口成功连接上去了，并且这次显示的PostgreSQL版本是正确的，9.2.4版本。

检查数据库是否有迁移后漏掉的，或者数据编码有没有问题，都没有问题，这次的迁移算是成功的。

然后改动9.2目录下的data目录里的postgresql.conf文件(C:\Program Files\PostgreSQL\9.2\data\postgresql.conf)，找到port项，把端口号改成和9.0一样的(PostgreSQL默认端口号是5432).

再次改动pg_hba.conf文件(C:\Program Files\PostgreSQL\9.2\data\pg_hba.conf)，把trust改回原来的md5格式。

重新用Navicat Premium通过5432端口连接，检查是否正常连接，连接成功就没问题了。

 

PS：

全部完成之后，我把9.0全部删掉，数据也不要了。

因为已经迁移到9.2，所以不再需要保留9.0上的旧数据。

当然如果你还要保留9.0上的旧数据也行，你开心就好。