環境:
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.
這個時候需要對重庫重做了.