pg升级(14-15)link方式
环境:
OS:Centos 7
旧版本:pg14
新版本:pg15
1.查看当前的数据库版本
su - postgres
-bash-4.2$ psql -h localhost -U postgres -p5432
psql (15.6, server 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 | 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插件,那么也要安装新版本pg15的插件
3.安装版本的数据库软件并进行初始化
安装软件步骤省略,下面是初始化数据库
su - postgres
/usr/pgsql-15/bin/initdb -D /opt/pg15/data
确保新版本的可以运行
4.安装插件
[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
5.停掉旧版本
systemctl stop postgresql-14
6.升级检查
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 --link
Performing Consistency Checks on Old Live Server
------------------------------------------------
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*
7.升级
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/ --link
There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting
提升需要停掉旧版本数据库
systemctl stop postgresql-14
-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/ --link
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
Adding ".old" suffix to old global/pg_control ok
If you want to start the old cluster, you will need to remove
the ".old" suffix from /opt/pg14/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking 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
上面有提示
If you want to start the old cluster, you will need to remove
the ".old" suffix from /opt/pg14/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
若想重新启动旧版本的数据库,那么需要将
/opt/pg14/data/global/pg_control.old
改名为
/opt/pg14/data/global/pg_control
6.拷贝配置文件到新版本的数据库目录
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
7.启动新版数据库
systemctl start postgresql-15
