Windows上使用pg_upgrade升級postgres (postgresql11 升級到postgresql16）

小寫是原來版本的參數，大寫是新版本的參數。

升級完成
c:\Program Files\PostgreSQL\16\bin>pg_upgrade -b “c:\Program Files\PostgreSQL\11\bin” -B “C:\Program Files\PostgreSQL\16\bin” -d “c:\Program Files\PostgreSQL\11\data” -D “c:\Program Files\PostgreSQL\16\data” -U postgres -p 5432 -P 5433
Performing Consistency Checks
—————————–
Checking cluster versions 成功
Checking database user is the install user 成功
Checking database connection settings 成功
Checking for prepared transactions 成功
Checking for system-defined composite types in user tables 成功
Checking for reg* data types in user tables 成功
Checking for contrib/isn with bigint-passing mismatch 成功
Checking for incompatible “aclitem” data type in user tables 成功
Checking for removed “abstime” data type in user tables 成功
Checking for removed “reltime” data type in user tables 成功
Checking for removed “tinterval” data type in user tables 成功
Checking for user-defined encoding conversions 成功
Checking for user-defined postfix operators 成功
Checking for incompatible polymorphic functions 成功
Checking for tables WITH OIDS 成功
Checking for invalid “sql_identifier” user columns 成功
Creating dump of global objects 成功
Creating dump of database schemas
成功
Checking for presence of required libraries 成功
Checking database user is the install user 成功
Checking for prepared transactions 成功
Checking for new cluster tablespace directories 成功

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

Performing Upgrade
——————
Setting locale and encoding for new cluster 成功
Analyzing all rows in the new cluster 成功
Freezing all rows in the new cluster 成功
Deleting files from new pg_xact 成功
Copying old pg_xact to new server 成功
Setting oldest XID for new cluster 成功
Setting next transaction ID and epoch for new cluster 成功
Deleting files from new pg_multixact/offsets 成功
Copying old pg_multixact/offsets to new server 成功
Deleting files from new pg_multixact/members 成功
Copying old pg_multixact/members to new server 成功
Setting next multixact ID and offset for new cluster 成功
Resetting WAL archives 成功
Setting frozenxid and minmxid counters in new cluster 成功
Restoring global objects in the new cluster 成功
Restoring database schemas in the new cluster
成功
Copying user relation files
成功
Setting next OID for new cluster 成功
Sync data directory to disk 成功
Creating script to delete old cluster 成功
Checking for extension updates 通知

Your installation contains extensions that should be updated
with the ALTER EXTENSION command. The file
update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
—————-
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
C:/Program Files/PostgreSQL/16/bin/vacuumdb -U postgres –all –analyze-in-stages
Running this script will delete the old cluster’s data files:
delete_old_cluster.bat

c:\Program Files\PostgreSQL\16\bin>

三、遇到的問題以及解決
1.報錯connection to database failed: fe_sendauth: no password supplied

這是由於需要密碼認證導致的， 修改/data/pg_hba.conf文件，將md5改成trust即可

（重點:把每一個md5 都要改成trust）

2.報錯The source cluster was not shut down cleanly.

這是由於postgres沒有被完全關閉導致的，一般遇到的概率比較小，在關閉時可以使用如下命令即可

/pg_ctl.exe -D “d:\Program Files\PostgreSQL\11\data” stop -s -m fast

(個人建議升級前使用此命令關閉pg資料庫)
3.許可權問題

由於本次是在windows下更新postgres，所以還需要註冊postgres用戶，並給postgres用戶相應的許可權(也試過不創建用戶，通過腳本來操作，但是貌似不行)；所以建議大家按照官方文檔來操作，我開始是使用 git base來按照linux命令來更新的，花了好多時間都沒更新成功，最後還是按照官方文檔來，成功更新,關於這一步，我倒沒有創建postgres用戶，只是用了一個管理員賬號來操作，並不是用Administrator操作。而是另外一個管理員賬號。

4.日誌查看

在使用pg_upgrade命令進行更新時，會在執行命令的當前目錄下產生四個日誌文件，裡面會存儲報錯信息，如果出現更新失敗的話，就看下這四個日誌文件，然後解決下錯誤即可

5.遇到報錯「xx/data/base : No such file or directory」

這是由於執行命令的用戶沒有base目錄的許可權導致的，給該用戶base目錄許可權即可解決問題

然後在新版本實例下刪除過程中產生的一些無用文件即可，如
\data\pg_upgrade_output.d （文件夾）
\bin\delete_old_cluster.bat （文件）
\bin\update_extensions.sql （文件）

詳細的安裝成功日誌如下，操作系統是 win10 。

Microsoft Windows [版本 10.0.19045.4529]
(c) Microsoft Corporation。保留所有權利。

C:\Windows\System32>netstat -na | findstr “543”

C:\Windows\System32>cd c:\Program Files\PostgreSQL\16\bin

c:\Program Files\PostgreSQL\16\bin>pg_upgrade -b “c:\Program Files\PostgreSQL\11\bin” -B “C:\Program Files\PostgreSQL\16\bin” -d “c:\Program Files\PostgreSQL\11\data” -D “c:\Program Files\PostgreSQL\16\data” -U postgres -p 5432 -P 5433
Performing Consistency Checks
—————————–
Checking cluster versions 成功

The source cluster was not shut down cleanly.
Failure, exiting

c:\Program Files\PostgreSQL\16\bin>pg_upgrade -b “c:\Program Files\PostgreSQL\11\bin” -B “C:\Program Files\PostgreSQL\16\bin” -d “c:\Program Files\PostgreSQL\11\data” -D “c:\Program Files\PostgreSQL\16\data” -U postgres -p 5432 -P 5433
Performing Consistency Checks
—————————–
Checking cluster versions 成功

The source cluster was not shut down cleanly.
Failure, exiting

c:\Program Files\PostgreSQL\16\bin>pg_ctl.exe -D “c:\Program Files\PostgreSQL\11\data” stop -s -m fast
pg_ctl: PID 文件 “c:/Program Files/PostgreSQL/11/data/postmaster.pid” 不存在
伺服器進程是否正在運行?

c:\Program Files\PostgreSQL\16\bin>netstat -na | findstr “543”
TCP 0.0.0.0:5432 0.0.0.0:0 LISTENING
TCP 0.0.0.0:5433 0.0.0.0:0 LISTENING
TCP [::]:5432 [::]:0 LISTENING
TCP [::]:5433 [::]:0 LISTENING

c:\Program Files\PostgreSQL\16\bin>pg_upgrade -b “c:\Program Files\PostgreSQL\11\bin” -B “C:\Program Files\PostgreSQL\16\bin” -d “c:\Program Files\PostgreSQL\11\data” -D “c:\Program Files\PostgreSQL\16\data” -U postgres -p 5432 -P 5433

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
故障，退出

c:\Program Files\PostgreSQL\16\bin>pg_ctl.exe -D “c:\Program Files\PostgreSQL\16\data” stop -s -m fast

c:\Program Files\PostgreSQL\16\bin>pg_upgrade -b “c:\Program Files\PostgreSQL\11\bin” -B “C:\Program Files\PostgreSQL\16\bin” -d “c:\Program Files\PostgreSQL\11\data” -D “c:\Program Files\PostgreSQL\16\data” -U postgres -p 5432 -P 5433
Performing Consistency Checks
—————————–
Checking cluster versions 成功

The source cluster was not shut down cleanly.
Failure, exiting

c:\Program Files\PostgreSQL\16\bin>pg_ctl.exe -D “c:\Program Files\PostgreSQL\11\data” stop -s -m fast
pg_ctl: PID 文件 “c:/Program Files/PostgreSQL/11/data/postmaster.pid” 不存在
伺服器進程是否正在運行?

c:\Program Files\PostgreSQL\16\bin>pg_ctl.exe -D “c:\Program Files\PostgreSQL\11\data” stop -s -m fast

c:\Program Files\PostgreSQL\16\bin>pg_upgrade -b “c:\Program Files\PostgreSQL\11\bin” -B “C:\Program Files\PostgreSQL\16\bin” -d “c:\Program Files\PostgreSQL\11\data” -D “c:\Program Files\PostgreSQL\16\data” -U postgres -p 5432 -P 5433
Performing Consistency Checks
—————————–
Checking cluster versions 成功
Checking database user is the install user 成功
Checking database connection settings 成功
Checking for prepared transactions 成功
Checking for system-defined composite types in user tables 成功
Checking for reg* data types in user tables 成功
Checking for contrib/isn with bigint-passing mismatch 成功
Checking for incompatible “aclitem” data type in user tables 成功
Checking for removed “abstime” data type in user tables 成功
Checking for removed “reltime” data type in user tables 成功
Checking for removed “tinterval” data type in user tables 成功
Checking for user-defined encoding conversions 成功
Checking for user-defined postfix operators 成功
Checking for incompatible polymorphic functions 成功
Checking for tables WITH OIDS 成功
Checking for invalid “sql_identifier” user columns 成功
Creating dump of global objects 成功
Creating dump of database schemas
成功
Checking for presence of required libraries 成功
Checking database user is the install user 成功
Checking for prepared transactions 成功
Checking for new cluster tablespace directories 成功

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

Performing Upgrade
——————
Setting locale and encoding for new cluster 成功
Analyzing all rows in the new cluster 成功
Freezing all rows in the new cluster 成功
Deleting files from new pg_xact 成功
Copying old pg_xact to new server 成功
Setting oldest XID for new cluster 成功
Setting next transaction ID and epoch for new cluster 成功
Deleting files from new pg_multixact/offsets 成功
Copying old pg_multixact/offsets to new server 成功
Deleting files from new pg_multixact/members 成功
Copying old pg_multixact/members to new server 成功
Setting next multixact ID and offset for new cluster 成功
Resetting WAL archives 成功
Setting frozenxid and minmxid counters in new cluster 成功
Restoring global objects in the new cluster 成功
Restoring database schemas in the new cluster
成功
Copying user relation files
成功
Setting next OID for new cluster 成功
Sync data directory to disk 成功
Creating script to delete old cluster 成功
Checking for extension updates 通知

Your installation contains extensions that should be updated
with the ALTER EXTENSION command. The file
update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
—————-
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
C:/Program Files/PostgreSQL/16/bin/vacuumdb -U postgres –all –analyze-in-stages
Running this script will delete the old cluster’s data files:
delete_old_cluster.bat