描述
pg_upgrade(以前称为pg_migrator)允许存储在PostgreSQL数据文件中的数据升级到更高的PostgreSQL主版本,而不需要进行主版本升级所需的数据的dump/restore,例如把版本从10.7升级到11.2。
PostgreSQL大版本会定期添加新特性,这些新特性通常会改变系统表的布局,但内部数据存储格式很少改变。pg_upgrade通过创建新的系统表和重用旧的用户数据文件来执行快速升级。如果将来的主要版本改变了数据存储格式,使旧的数据格式变得不可读,pg_upgrade将不能用于这种升级。
pg_upgrade 是postgresql 大版本升级的得力工具。
主要有两种用法:
1、使用pg_upgrade拷贝升级。
2、使用pg_upgrade链接升级。
本次升级使用的第一种方式拷贝升级。
环境
OS:Red Hat Enterprise Linux Server release 7.4 (Maipo)
老版本数据库:postgresql9.2.21
新版本数据库:postgresql11.14
升级步骤
1、上传PG11的RPM包
postgresql11-11.14-1PGDG.rhel7.x86_64.rpm
postgresql11-libs-11.14-1PGDG.rhel7.x86_64.rpm
postgresql11-server-11.14-1PGDG.rhel7.x86_64.rpm
2、安装新版本数据库
rpm -ivh postgresql11-libs-11.14-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql11-11.14-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql11-server-11.14-1PGDG.rhel7.x86_64.rpm
[root@localhost ~]# rpm -ivh postgresql11-libs-11.14-1PGDG.rhel7.x86_64.rpm
warning: postgresql11-libs-11.14-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql11-libs-11.14-1PGDG.rhe################################# [100%]
[root@localhost ~]# rpm -ivh postgresql11-11.14-1PGDG.rhel7.x86_64.rpm
warning: postgresql11-11.14-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql11-11.14-1PGDG.rhel7 ################################# [100%]
failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink
failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink
failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb: /usr/bin/createdb exists and it is not a symlink
failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser: /usr/bin/createuser exists and it is not a symlink
failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb: /usr/bin/dropdb exists and it is not a symlink
failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser: /usr/bin/dropuser exists and it is not a symlink
failed to link /usr/bin/pg_basebackup -> /etc/alternatives/pgsql-pg_basebackup: /usr/bin/pg_basebackup exists and it is not a symlink
failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump: /usr/bin/pg_dump exists and it is not a symlink
failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall: /usr/bin/pg_dumpall exists and it is not a symlink
failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore: /usr/bin/pg_restore exists and it is not a symlink
failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb: /usr/bin/reindexdb exists and it is not a symlink
failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb: /usr/bin/vacuumdb exists and it is not a symlink
[root@localhost ~]# rpm -ivh postgresql11-server-11.14-1PGDG.rhel7.x86_64.rpm
warning: postgresql11-server-11.14-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql11-server-11.14-1PGDG.r################################# [100%]
[root@localhost ~]#
3、初始化新版本数据库
注意:初始化后不要启动新版本数据库
/usr/pgsql-11/bin/postgresql-11-setup initdb
[root@localhost ~]# /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK
4、备份老版本数据库
su - postgres
/usr/bin/pg_dumpall -f /tmp/allbackup.bak
5、关闭老版本数据库并进行物理备份
systemctl stop postgresql.service
ps -ef|grep postgres
cp -rp /var/lib/pgsql/data/ /var/lib/pgsql/data.bak/
[root@localhost ~]# systemctl stop postgresql.service
[root@localhost ~]# ps -ef|grep postgres
root 11545 7099 0 12:49 pts/0 00:00:00 su - postgres
postgres 11546 11545 0 12:49 pts/0 00:00:00 -bash
root 12289 12003 0 13:56 pts/2 00:00:00 su - postgres
postgres 12290 12289 0 13:56 pts/2 00:00:00 -bash
root 12400 12028 0 14:04 pts/3 00:00:00 grep --color=auto postgres
[root@localhost ~]# cp -rp /var/lib/pgsql/data/ /var/lib/pgsql/data.bak/
[root@localhost ~]#
6、修改pg_hba.conf,postgresql.conf(Upgrade过程需要连接库)
cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/11/data/
-bash-4.2$ cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/11/data/
-bash-4.2$
7、root下修改老版本pg_ctl
mv /usr/bin/pg_ctl{,-orig}
echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl
8、执行升级操作
参数
-b (–old-bindir=bindir): 旧的集群可执行目录。
-B (–new-bindir=bindir): 新的集群可执行目录。
-d (–old-datadir=configdir): 旧的集群数据目录。
-D (–new-datadir=configdir): 旧的集群数据目录
-k (–link):link模式,相对于克隆模式,克隆模式需要将文件复制到新的集群。
-c (–check):只做“检查”,不改变任何数据。它将进行许多一致性检查。在进行大版本升级前,总是首先使用这个命令来进行升级前的预检。
更多信息可通过pg_upgrade --help进行查询。
/usr/pgsql-11/bin/pg_upgrade -d /var/lib/pgsql/data -D /var/lib/pgsql/11/data -b /usr/bin -B /usr/pgsql-11/bin
-bash-4.2$ /usr/pgsql-11/bin/pg_upgrade -d /var/lib/pgsql/data -D /var/lib/pgsql/11/data -b /usr/bin -B /usr/pgsql-11/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
Checking for invalid "unknown" user columns ok
Checking for roles starting with "pg_" ok
Checking for incompatible "line" data type 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_clog 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
Setting oldest multixact ID in 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
Setting minmxid counter in new cluster ok
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok
Checking for hash indexes ok
Checking for extension updates ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
9、启动新版本数据库,收集统计信息
systemctl start postgresql-11
./analyze_new_cluster.sh
-bash-4.2$ ./analyze_new_cluster.sh
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy. When it is done, your system will
have the default level of optimizer statistics.
If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.
If you would like default statistics as quickly as possible, cancel
this script and run:
"/usr/pgsql-11/bin/vacuumdb" --all --analyze-only
vacuumdb: processing database "ambari": 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 "ambari": 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 "ambari": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
Done
10、删除老版本数据库文件
./delete_old_cluster.sh
mv -f /usr/bin/pg_ctl{-orig,}
11、检查新库状态及数据
/usr/pgsql-11/bin/psql
-bash-4.2$ /usr/pgsql-11/bin/psql
psql (11.14)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
ambari | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
| | | | | =Tc/postgres +
| | | | | ambari=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
| | | | | =c/postgres
(4 rows)
postgres=# select name,setting,unit from pg_catalog.pg_settings where context='internal';
name | setting | unit
-----------------------+-------------+------
block_size | 8192 |
data_checksums | off |
data_directory_mode | 0700 |
debug_assertions | off |
integer_datetimes | on |
lc_collate | en_US.UTF-8 |
lc_ctype | en_US.UTF-8 |
max_function_args | 100 |
max_identifier_length | 63 |
max_index_keys | 32 |
segment_size | 131072 | 8kB
server_encoding | UTF8 |
server_version | 11.14 |
server_version_num | 110014 |
wal_block_size | 8192 |
wal_segment_size | 16777216 | B
(16 rows)
12、卸载老版本数据库
rpm -e postgresql-server-9.2.21-1.el7.x86_64
rpm -e postgresql-9.2.21-1.el7.x86_64
rpm -e postgresql-libs-9.2.21-1.el7.x86_64
[root@localhost ~]# rpm -e postgresql-server-9.2.21-1.el7.x86_64
warning: file /var/lib/pgsql/data: remove failed: No such file or directory
warning: /var/lib/pgsql/.bash_profile saved as /var/lib/pgsql/.bash_profile.rpmsave
[root@localhost ~]# rpm -e postgresql-9.2.21-1.el7.x86_64
[root@localhost ~]# rpm -e postgresql-libs-9.2.21-1.el7.x86_64
13、配置环境变量
vi /etc/profile
PGDATA=/var/lib/pgsql/11/data
export PGDATA
export PATH=/usr/pgsql-11/bin:$PATH
source /etc/profile
14、配置新版本开机自启动
cp /usr/lib/systemd/system/postgresql-11.service /usr/lib/systemd/system/postgresql.service
systemctl stop postgresql-11
systemctl start postgresql
systemctl status postgresql
systemctl enable postgresql
[root@localhost ~]# systemctl status postgresql
● postgresql.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2022-01-11 14:18:26 CST; 12s ago
Docs: https://www.postgresql.org/docs/11/static/
Main PID: 12755 (postmaster)
CGroup: /system.slice/postgresql.service
├─12755 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─12757 postgres: logger
├─12759 postgres: checkpointer
├─12760 postgres: background writer
├─12761 postgres: walwriter
├─12762 postgres: autovacuum launcher
├─12763 postgres: stats collector
└─12764 postgres: logical replication launcher
Jan 11 14:18:26 localhost.localdomain systemd[1]: Starting PostgreSQL 11 database server...
Jan 11 14:18:26 localhost.localdomain postmaster[12755]: 2022-01-11 14:18:26.902 CST [12755] LOG: listening on IPv6 address "::1", port 5432
Jan 11 14:18:26 localhost.localdomain postmaster[12755]: 2022-01-11 14:18:26.902 CST [12755] LOG: listening on IPv4 address "127.0.0.1", port 5432
Jan 11 14:18:26 localhost.localdomain postmaster[12755]: 2022-01-11 14:18:26.902 CST [12755] LOG: listening on Unix socket "/var/run/postgresql/...L.5432"
Jan 11 14:18:26 localhost.localdomain postmaster[12755]: 2022-01-11 14:18:26.902 CST [12755] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Jan 11 14:18:26 localhost.localdomain postmaster[12755]: 2022-01-11 14:18:26.907 CST [12755] LOG: redirecting log output to logging collector process
Jan 11 14:18:26 localhost.localdomain postmaster[12755]: 2022-01-11 14:18:26.907 CST [12755] HINT: Future log output will appear in directory "log".
Jan 11 14:18:26 localhost.localdomain systemd[1]: Started PostgreSQL 11 database server.