环境:
OS:Centos 7
旧版本的db:pg14
新版本的db:pg15
升级策略:必须先升级主库,然后重做从库
1.查看主库安装的插件
-bash-4.2$ psql -h localhost -U postgres -p5432
psql (14.11)
Type "help" for help.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Descriptio
n
------------------------------+---------+------------+---------------------------------------------------------------
------------------------------------------------------
address_standardizer | 3.3.3 | public | Used to parse an address into constituent elements. Generally
used to support geocoding address normalization step.
address_standardizer_data_us | 3.3.3 | public | Address Standardizer US dataset example
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
fuzzystrmatch | 1.1 | public | determine similarities and distance between strings
mysql_fdw | 1.2 | public | Foreign data wrapper for querying a MySQL server
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.3.3 | public | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.3.3 | public | PostGIS raster types and functions
postgis_sfcgal | 3.3.3 | public | PostGIS SFCGAL functions
postgis_tiger_geocoder | 3.3.3 | tiger | PostGIS tiger geocoder and reverse geocoder
postgis_topology | 3.3.3 | topology | PostGIS topology spatial types and functions
postgres_fdw | 1.1 | public | foreign-data wrapper for remote PostgreSQL servers
(12 rows)
安装的插件有:
mysql_fdw
postgis
2.查看数据库版本
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 14.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
3.安装新版本的pg
我这里安装pg15,确保新版本的pg可以正常运行
具体安装方法参考如下连接(只参考安装pg部分,插件部分不需要安装)
https://www.cnblogs.com/hxlasky/p/16846972.html
我这里上pg13的例子,替换成安装pg15即可
3.1 创建数据存储目录
[root@localhost bin]#mkdir -p /opt/pg15/data
[root@localhost bin]#mkdir -p /opt/pg15/log
[root@localhost bin]#mkdir -p /opt/pg15/archivelog
[root@localhost bin]#chown -R postgres:postgres /opt/pg15
[root@localhost bin]#chmod 0700 /opt/pg15/data
3.2 安装
[root@dsc1 pg15]#rpm -ivh postgresql15-libs-15.6-1PGDG.rhel7.x86_64.rpm
[root@dsc1 pg15]#rpm -ivh postgresql15-15.6-1PGDG.rhel7.x86_64.rpm
[root@dsc1 pg15]#rpm -ivh postgresql15-server-15.6-1PGDG.rhel7.x86_64.rpm
[root@dsc1 pg15]#rpm -ivh postgresql15-contrib-15.6-1PGDG.rhel7.x86_64.rpm
3.3 初始化数据库
su - postgres
/usr/pgsql-15/bin/initdb -D /opt/pg15/data
3.4 修改系统服务启动参数
su - root
[root@localhost postgresql-15.service.d]# ls -al /usr/lib/systemd/system/postgresql-15.service
-rw-r--r--. 1 root root 1764 Aug 10 06:06 /usr/lib/systemd/system/postgresql-15.service
vi /usr/lib/systemd/system/postgresql-15.service
修改为Environment=PGDATA=/opt/pg15/data/
到这里可以尝试启动新版本的数据库,但是需要修改端口号(其他参数不修改),不能与原来的一样,否则报错
比如我这里修改成如下:
su - postgres
vi /opt/pg15/data/postgresql.conf
port = 15432
3.5 尝试启动
[root@localhost]#systemctl daemon-reload
[root@localhost]#systemctl start postgresql-15
确定可以启动后,将上面修改的参数修改回来,port = 5432
这里确保新版本的pg要正常启动.
########################升级主库处理###########################
1.停掉新旧版本的数据库
[root@localhost ~]#systemctl stop postgresql-14
[root@localhost ~]#systemctl stop postgresql-15
2.升级检查
使用新版本的pg_upgrade进行升级
su - postgres
-bash-4.2$ /usr/pgsql-15/bin/pg_upgrade --old-datadir /opt/pg14/data/ --new-datadir /opt/pg15/data --old-bindir /usr/pgsql-14/bin/ --new-bindir /usr/pgsql-15/bin/ --check
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 system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
/opt/pg15/data/pg_upgrade_output.d/20240514T162609.626/loadable_libraries.txt
Failure, exiting
-bash-4.2$ more /opt/pg15/data/pg_upgrade_output.d/20240514T162609.626/loadable_libraries.txt
could not load library "$libdir/mysql_fdw": ERROR: could not access file "$libdir/mysql_fdw": No such file or directory
In database: postgres
could not load library "$libdir/postgis-3": ERROR: could not access file "$libdir/postgis-3": No such file or directory
In database: postgres
could not load library "$libdir/postgis_raster-3": ERROR: could not access file "$libdir/postgis_raster-3": No such file or directory
In database: postgres
could not load library "$libdir/postgis_sfcgal-3": ERROR: could not access file "$libdir/postgis_sfcgal-3": No such file or directory
In database: postgres
could not load library "$libdir/postgis_topology-3": ERROR: could not access file "$libdir/postgis_topology-3": No such file or directory
In database: postgres
could not load library "$libdir/address_standardizer-3": ERROR: could not access file "$libdir/address_standardizer-3": No such file or directory
In database: postgres
这里原因是安装的插件没有升级导致的,下面进行插件的升级
升级mysql_fdw和postgis插件:
[root@localhost extension]#yum list mysql_fdw*
[root@dsc1 ~]#yum install mysql_fdw_15.x86_64
[root@localhost extension]#yum list postgis*
[root@localhost extension]#yum install postgis33_15.x86_64
4.拷贝旧版本的配置文件到新版
su - postgres
cp /opt/pg14/data/pg_hba.conf /opt/pg15/data/
cp /opt/pg14/data/postgresql.conf /opt/pg15/data/
拷贝过去注意修改如下配置项postgresql.conf
log_directory = '/opt/pg15/log'
archive_command = 'DATE=`date +%Y%m%d`;DIR="/opt/pg15/archivelog/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f
5.执行升级
su - postgres
/usr/pgsql-15/bin/pg_upgrade --old-datadir /opt/pg14/data/ --new-datadir /opt/pg15/data --old-bindir /usr/pgsql-14/bin/ --new-bindir /usr/pgsql-15/bin/
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 system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch 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
Checking for new cluster tablespace directories 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 in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for 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
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to delete old cluster ok
Checking for extension updates ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
我这里没有执行建议执行的2个命令
/usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages
./delete_old_cluster.sh
脚本内容:
-bash-4.2$ more delete_old_cluster.sh
#!/bin/sh
rm -rf '/opt/pg14/data'
6.启动新版本数据库
[root@localhost ~]#systemctl start postgresql-15
7.这个时候查看主从情况
-bash-4.2$ psql -h localhost -U postgres -p5432
postgres=# \x on
Expanded display is on.
postgres=# select * from pg_stat_replication;
发现从库报错误:
从库的日志: 2024-05-14 16:56:43 CST [9519]: [1-1] user=,db=,app=,client=FATAL: database system identifier differs between the primary and standby 2024-05-14 16:54:23 CST [9466]: [2-1] user=,db=,app=,client=DETAIL: The primary's identifier is 7368767584432480743, the standby's identifier is 7368670560794476245.
这个时候需要对重库重做了.
标签：ok,root,pg14,pg15,pg,new,data From： https://www.cnblogs.com/hxlasky/p/18193454