pg升级(rsync方式pg14-pg15)主从环境
环境:
OS:Centos 7
旧版本的db:pg14
新版本的db:pg15
升级策略:必须先升级主库,然后使用rsync升级从库
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
重复做升级的话会报如下错误:
New cluster database "postgres" is not empty: found relation "public.spatial_ref_sys"
Failure, exiting
需要删除data目录后再做初始化
-bash-4.2$ rm -rf /opt/pg15/data
-bash-4.2$ /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.执行升级
采用link方式升级也是可以的:
/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/ --link
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.
############################备库处理########################
1.停掉原来旧版本的备库
systemctl stop postgresql-14
2.安装新版本的pg
[root@dsc2 data]# rpm -qa|grep postgresql15
postgresql15-libs-15.6-1PGDG.rhel7.x86_64
postgresql15-server-15.6-1PGDG.rhel7.x86_64
postgresql15-contrib-15.6-1PGDG.rhel7.x86_64
postgresql15-15.6-1PGDG.rhel7.x86_64
并进行初始化话
su - postgres
/usr/pgsql-15/bin/initdb -D /opt/pg15/data
确保新版本的数据库能够启动
systemctl start postgresql-15
然后停掉删除目录
systemctl start postgresql-15
rm -rf /opt/pg15/data
这里上确保data目录为空
3.安装新版本的插件
源库有什么插件就安装什么插件
[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
-bash-4.2$ rsync -avz postgres@192.168.1.102:/opt/pg15/data /opt/pg15/
注意rsync同步的过程中，主库上可以写入数据的，主库的归档不能删除,后面同步会用到归档日志同步在rsync同步过程新增的数据.
5.拷贝旧环境的standby.signal和 postgresql.auto.conf 到新目录
su - postgres
cp /opt/pg14/data/standby.signal /opt/pg15/data/
cp /opt/pg14/data/postgresql.auto.conf /opt/pg15/data/
同时检查 postgresql.conf 和 pg_hba.conf是否正常(这两个文件rsync操作也会同步过来了的)
6.启动从库
systemctl start postgresql-15
7.验证
主库写入数据查看从库是否应用
