postgresql(14-15)升级(源库需要停机)
环境:
OS:Centos 7
旧版本:pg14
新版本:pg15
已经安装的插件
mysql_fdw_14.x86_64
postgis33_14.x86_64
1.查看当前的版本
[root@dsc1 ~]# psql -h localhost -U postgres -p5432
psql (14.11)
Type "help" for help.
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)
2.查看安装的插件
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
------------------------------+---------+------------+----------------------------------------------------------------
-----------------------------------------------------
address_standardizer | 3.3.3 | public | Used to parse an address into constituent elements. Generally u
sed 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)
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.升级检查
su - postgres
使用新版本的pg_upgrade进行升级
/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
-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/20240513T104855.735/loadable_libraries.txt
Failure, exiting
-bash-4.2$ more /opt/pg15/data/pg_upgrade_output.d/20240513T104855.735/loadable_libraries.txt
could not load library "$libdir/mysql_fdw": ERROR: could not access file "$libdir/mysql_fdw": No such file or direct
ory
In database: postgres
could not load library "$libdir/postgis-3": ERROR: could not access file "$libdir/postgis-3": No such file or direct
ory
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 s
uch 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
3.再次检查
su - postgres
使用新版本的pg_upgrade进行升级
-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 ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
*Clusters are compatible*
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
-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/
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
执行脚本(该脚本内容是删除旧版本的数据目录,可以不执行):
-bash-4.2$ ls
14 15 delete_old_cluster.sh
-bash-4.2$ ./delete_old_cluster.sh
[postgres@localhost ~]$ more delete_old_cluster.sh
#!/bin/sh
rm -rf '/opt/pg14/data'
该脚本需要启动版本的pg15才能执行
-bash-4.2$ /usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages
vacuumdb: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
5.重新配置pg_hba.conf和postgresql.conf
上面步骤已经拷贝了旧数据库的配置文件到新数据库了,可以忽略.
若升级完成后不执行delete_old_cluster.sh的话可以将原来的配置文件拷贝到新版本data目录,适当修改数据目录,这样就不需要从新配置
6.启动新版本数据库
[root@localhost ~]#systemctl start postgresql-15
7.登录检查
[root@dsc1 ~]# psql -h localhost -U postgres -p5432
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
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)
##切换到具体库查看数据
postgres=# \c db_hxl
You are now connected to database "db_hxl" as user "postgres".
db_hxl=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | tb_test | table | postgres
(1 row)
db_hxl=# select * from tb_test;
id | name | createtime | modifytime
----+-------+----------------------------+----------------------------
1 | name1 | 2024-05-13 10:07:16.7901 | 2024-05-13 10:07:16.7901
2 | name2 | 2024-05-13 10:07:16.832084 | 2024-05-13 10:07:16.832084
3 | name3 | 2024-05-13 10:07:16.852864 | 2024-05-13 10:07:16.852864
4 | name4 | 2024-05-13 10:07:16.863887 | 2024-05-13 10:07:16.863887
5 | name5 | 2024-05-13 10:07:17.419715 | 2024-05-13 10:07:17.419715
(5 rows)
8.修改环境变量
su - postgres
-bash-4.2$ more .bash_profile
[ -f /etc/profile ] && source /etc/profile
PGDATA=/opt/pg15/data
export PGDATA
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
9.执行如下命令(密码为postgres)--该步骤可选
-bash-4.2$ /usr/pgsql-15/bin/vacuumdb -h 192.168.1.100 -p 5432 -U postgres --all --analyze-in-stages
Password:
vacuumdb: processing database "db_hxl": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "db_hxl": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "db_hxl": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
