postgresql版本升级

从postgresql 12升级至postgresql 14。

• 同一大版本的小版本无需更新数据目录，直接更新程序目录，但是要注意重新编译安装插件。

• 个人建议大版本中的小版本更新至5以上再使用，例如发稿时最新稳定版为16.4，建议至16.5以后再考虑升级。

• 升级成功后，旧的程序目录和数据目录暂时不要删除，待运行一段时候稳定后再删除。

• 升级成功后，需要重新配置postgresql.conf和pg_hba.conf。

前提条件：

• postgresql 12 程序目录C:/pgsql1，数据目录D:\data\pgdata
• postgresql 14 程序目录C:/pgsql，数据目录D:\data14\pgdata

1.在开始之前必须停止新旧数据库

C:/pgsql/bin/pg_ctl -D D:\data14\pgdata stop -m fast
C:/pgsql1/bin/pg_ctl -D D:\data\pgdata stop -m fast

2.在运行pg_upgrade之前为新版本pg初始化新数据目录

注意postgres用户密码保持和以前一至，如果不一至第3步设置pg_hba.conf的验证方式使用trust

C:/pgsql/bin/initdb.exe -D D:\data14\pgdata -E UTF-8 --locale=Chinese_China.936 -A scram-sha-256 -W -U postgres

3.修改新数据目录中的postgresql.conf

保证新数据目录postgresql.conf中shared_preload_libraries和旧数据目录中的一至
注意：因为插件可能存在PostgreSQL版本兼容问题，最好使用新的PostgreSQL重新编译安装

#postgresql.conf,这个是我的配置
shared_preload_libraries = 'pg_kmcb,pg_kuncb_plugin'

4. 修改新数据目录中的pg_hba.conf和旧数据目录中的pg_hba保持一至.

注意打开IPv4和IPv6两个的本地连接，如果新旧密码不一至pg_hba.conf的本地验证方式使用trust，升级完成后再注释。
postgresql 12数据目录中pg_hba.conf的配置，IPv4 local connections:和IPv6 local connections:是未使用的

# IPv4 local connections:
#host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
#host    all             all             ::1/128                 trust

#我的配置，IPv4 local connections:和IPv6 local connections:是未使用的
#postgres用户只能在本机使用，提高安全性
host    all             postgres          127.0.0.1/32            scram-sha-256  
host    kmcb            kmcb      	      0.0.0.0/0               scram-sha-256
host    kmcb            hainan      	  0.0.0.0/0               scram-sha-256

修改后新旧数据目录中pg_hba.conf的配置。如果使用了trust则无需再配置环境变量PGPASSWORD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

#我的配置，IPv4 local connections:和IPv6 local connections:是未使用的
#postgres用户只能在本机使用，提高安全性
host    all             postgres          127.0.0.1/32            scram-sha-256  
host    kmcb            kmcb      	      0.0.0.0/0               scram-sha-256
host    kmcb            hainan      	  0.0.0.0/0               scram-sha-256

5.使用新版本pg启动新数据目录看能否则正常启动

C:\pgsql\bin\pg_ctl -D D:\data14\pgdata start
C:\pgsql\bin\pg_ctl -D D:\data14\pgdata stop -m fast

6.先检查新旧数据目录是否兼容

在开始之前check，–check不会修改任何数据

#pg_hba.conf如果验证方式为trust，不需要配置环境变量PGPASSWORD
set PGPASSWORD=123
C:\pgsql\bin\pg_upgrade --old-bindir=C:\pgsql1\bin  --old-datadir=D:\data\pgdata  --new-bindir=C:\pgsql\bin --new-datadir=D:\data14\pgdata  --username=postgres --check

7.如果check全部OK则可以升级

C:\pgsql\bin\pg_upgrade --old-bindir=C:\pgsql1\bin  --old-datadir=D:\data\pgdata  --new-bindir=C:\pgsql\bin --new-datadir=D:\data14\pgdata  --username=postgres

8.如果一切都没问题

#启动数据
C:/pgsql/bin/pg_ctl -D D:\data14\pgdata start
#优化数据库
C:/pgsql/bin/vacuumdb -U postgres --all --analyze-in-stages

如果旧数据库密码的加密方式是md5，新库的密码加密设置的是其它，还必须为每个用户重新设置密码。

alter user postgres password '123';

9 其它

如果是windows并注册了服务,以管理员身份打开cmd重新注册服务

c:\pgsql1\bin\pg_ctl unregister  -N PostgreSQL  -D D:\data\pgdata

c:\pgsql\bin\pg_ctl register  -N PostgreSQL  -D D:\data14\pgdata

如果是linux自动启动则需要重新启动文件的程序目录和数据目录。

至此将postgresql 12升级到postgresql 14。

10 如果要简化操作，可以使用配置环境变量方式

#set PGDATAOLD=旧集群/data
#set PGDATANEW=新集群/data
#set PGBINOLD=旧集群/bin
#set PGBINNEW=新集群/bin


set PGDATAOLD=D:\data\pgdata
set PGDATANEW=D:\data14\pgdata
set PGBINOLD=C:/pgsql1/bin
set PGBINNEW=C:/pgsql/bin

配置环境变量后pg_upgrade不需要–old-bindir=C:\pgsql1\bin --old-datadir=D:\data\pgdata --new-bindir=C:\pgsql\bin --new-datadir=D:\data14\pgdata这4个参数

C:\pgsql\bin\pg_upgrade --username=postgres --check
C:\pgsql\bin\pg_upgrade --username=postgres