【PostgreSQL 9.6→12】利用pg_upgrade进行升级步骤

1 年 ago

新, 韵

2 minutes

首先

我们在2019年至2020年期间进行了服务器替换迁移工作（从Windows Server 2008R2 + Oracle 11g迁移到Windows Server 2016 + PostgreSQL 9.6）。随后，由于PostgreSQL 9.6的支持终止日期（EOL = End of Life）即将临近于2021/11，我们决定将其进行重大升级到某N公司的支持最新版（截至2021/07）PostgreSQL 12。

バージョン初期リリース日サポート終了期限142021年09月30日2026年11月12日132020年09月24日2025年11月13日122019年10月03日2024年11月14日112018年10月18日2023年11月09日102017年10月05日2022年11月10日9.62016年09月29日2021年11月11日

【2022/07/01附言】

进行 PostgreSQL 12 的迁移工作

注意事项

在将PostgreSQL 9.6升级到PostgreSQL 12的过程中，需要注意10和11之间存在的差异。虽然不必担心附加功能和性能改进，但需要关注废弃的功能、文件夹名称更改以及命令的变更等迁移工作所需的问题。

PostgreSQL 10

在PostgreSQL 10中，有一些目录名和函数名等发生了变化。
如果在维护脚本或监控工具中将下面的目录名和函数名硬编码，则需要将其修改为PostgreSQL10及后续版本中的名称。

* 将日志文件输出目录名称从pg_log更改为log。
* 在与WAL相关的目录/函数/命令中，将xlog更改为wal，将location更改为lsn。
* 将提交日志的输出目录名称从pg_clog更改为pg_xact。

PostgreSQL 9.6以前の名称PostgreSQL 10以降の名称pg_loglogpg_xlogpg_walpg_clogpg_xactpg_current_xlog_locationpg_current_wal_lsnpg_xlogdumppg_waldumppg_receivexlogpg_receivewal

PostgreSQL 11：PostgreSQL 11

没有特别的

PostgreSQL 12 – 《PostgreSQL 12》

リカバリ時はrecovery.signal、スタンバイ時はstandby.signal を置くようにする

升级pg

这次数据迁移工作我们使用了主要的升级工具”pg_upgrade”。pg_upgrade所需的文件夹结构如下。

種類フォルダ旧データベースクラスタF:/Program Files/PostgreSQL/9.6/data新データベースクラスタF:/Program Files/PostgreSQL/12/data旧プログラムフォルダD:/Program Files/PostgreSQL/9.6/bin新プログラムフォルダD:/Program Files/PostgreSQL/12/binログやバッチ生成フォルダD:\temp

停止PostgreSQL 9.6服务。

将旧版本的PostgreSQL 9.6服务停止。

net stop postgresql-x64-12

2. 安装 PostgreSQL 12

安装新版本的PostgreSQL 12（postgresql-12.7-2-windows-x64.exe），同时将端口暂时设置为“5433”。
更新完成后将端口恢复为默认的“5432”。

3. 停止 PostgreSQL 12 的服务。

停止提供新版PostgreSQL 12服务。

net stop postgresql-x64-12

只需要中文的一种选择：编辑pg_hba.conf文件

編輯 PostgreSQL 9.6 與 PostgreSQL 12 兩個版本的 pg_hba.conf 檔案，將資料庫的認證方式從「md5」等改為「trust」。

host    all    all    127.0.0.1/32      trust
host    all    all    0.0.0.0/0     trust

请确保注册正确的IP地址，其中0.0.0.0/0仅为示例。

执行5.pg_upgrade

在执行pg_upgrade工具的当前目录中，将生成日志文件、用于批量分析的批处理文件以及用于删除的bat文件。
创建一个可被pg_upgrade工具写入的文件夹（例如d:\temp），并授予Everyone权限。
以管理员权限运行。

if not exist "d:\temp" (
    mkdir d:\temp
)
icacls d:\temp /grant Everyone:"(OI)(CI)(F)"

这次我们将当前目录设置为”d:\temp”，并运行pg_upgrade工具。
pg_upgrade工具需要用postgres用户来运行。
小写英文字母选项适用于旧版本，而大写英文字母选项适用于新版本。

pg_upgrade.exe ^
-U postgres ^
-d "F:/Program Files/PostgreSQL/9.6/data" ^
-D "F:/Program Files/PostgreSQL/12/data" ^
-b "D:/Program Files/PostgreSQL/9.6/bin" ^
-B "D:/Program Files/PostgreSQL/12/bin"

6. 错误

在pg_upgrade工具中，会执行迁移前的检查程序。如果出现类似的问题导致检查失败，日志将被输出到上述目录”d:\temp”中。

connection to database failed: fe_sendauth: no password supplied のエラーが表示されたときおそらく、パスワード認証で失敗しています。その場合、pg_hba.confファイルを編集し、データベースの認証を「trust」にしてください。

7. 再执行

如果发生错误，我们将解决错误原因并重新执行。
例如，如果存在在旧版本的PostgreSQL 9.6中包含OID的表，我们将进行WITHOUT OIDS操作以删除OID。

ALTER TABLE mst00001 SET WITHOUT OIDS;

确认

如果升级成功，将显示如下信息。

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

将pg_hba.conf恢复为原始状态。

将pg_hba.conf文件中数据库的认证方式从“trust”恢复到原来的状态。

编辑postgresql.conf文件

由于新版本的PostgreSQL 12的端口不再是默认端口”5432″，所以需要将postgresql.conf文件中的端口号修改为默认端口”5432″。

port = 5432

改變其他設定，包括postgresql.conf，將會是一個很好的選擇。

将旧版本的PostgreSQL 9.6的端口更改为”5433″。

port = 5433

请重新启动新版本。

net start postgresql-x64-12

确认版本。

psql -V
psql (PostgreSQL) 12.7

确认数据

我会确认数据的正确迁移。

收集统计信息

为了收集优化器的统计信息，请运行由pg_upgrade工具在”d:\temp”文件夹中生成的”analyze_new_cluster.bat”文件，因为统计数据不会迁移。

analyze_new_cluster.bat

如果不将端口号更改为默认端口，则即使执行了批处理并收集了统计信息，其实却没有运行且变得很慢。

整理收拾

如果未使用，请删除旧版本的PostgreSQL 9.6数据库集群（data文件夹）。使用pg_upgrade工具执行“d:\temp”文件夹中生成的“delete_old_cluster.bat”文件非常简单。

delete_old_cluster.bat

最后

如果仅仅是数据迁移的话，从PostgreSQL 9.6升级到PostgreSQL 12可以通过使用pg_upgrade工具来顺利完成。
由于PostgreSQL 12不再支持使用OID，因此需要对使用了OID的应用程序进行修复工作。

首先