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