1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL：使用 Pg_upgrade 升级大版本

• 数据库

pg_upgrade 是 PostgreSQL 提供的一个大版本升级工具，例如将 PG 从版本 9.0 升级到 9.1, 可以跨多个大版本升级， 当然也可以用 pg_dump ，之后再 pg_restore 到新版本 PG 软件的方法， 当数据库比较大时，比如 1 TB 以上，pg_dump/pg_restore 的方法太费时了，这里介绍使用 pg_upgrade 将 PostgreSQL 版本从 9.1.14 升级到 9.4.1 的步骤，跨越了 3 个大版本。

安装 PostgreSQL 9.4.1 软件

下载 postgresql-9.4.1

1
 
wget -c https://ftp.postgresql.org/pub/source/v9.4.1/postgresql-9.4.1.tar.bz2

解压,编译,

1
2
3
 
tar jxvf postgresql-9.4.1.tar.bz2  
cd postgresql-9.4.1  
./configure --prefix=/opt/pgsql_9.4.1  --with-pgport=1922 --with-wal-blocksize=16

安装

1
2
 
gmake world  
gmake install-world

初始化： initdb

1
 
/opt/pgsql_9.4.1/bin/initdb -E UTF8 -D /database/pg91_9.4/pg_root --locale=C -U postgres -W

使用 Pg_upgrade 升级大版本

停老库

1
2
3
 
[pg92@db1 pg_root]$ pg_ctl stop -m fast -D /database/pg91/pg_root  
waiting for server to shut down....  done  
server stopped

pg_upgrade 前检查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
[pg91@db1 pg91_9.4]$ /opt/pgsql_9.4.1/bin/pg_upgrade -c --link -b /opt/pgsql_9.1.14/bin -B /opt/pgsql_9.4.1/bin -d /database/pg91/pg_root -D /database/pg91_9.4/pg_root  
  
*failure*  
Consult the last few lines of "pg_upgrade_server.log"  for  
the probable cause of the failure.  
Performing  Consistency  Checks on Old  Live  Server  
------------------------------------------------  
Checking cluster versions ok  
Checking database user is a superuser ok  
Checking  for prepared transactions ok  
Checking  for reg* system OID user data types ok  
Checking  for contrib/isn with bigint-passing mismatch ok  
Checking  for invalid "line" user columns 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:  
 loadable_libraries.txt  
  
Failure, exiting

备注： -b, -B 分别表示老版本 PG bin 目录，新版本 PG bin目录， -d, -D 分别表示老版本PG 数据目录，新版本 PG 数据目录， -c 表示仅检查，并不会做任何更改， 根据提示查看文件 loadable_libraries.txt 。

报错信息

1
2
3
 
[pg91@db1 pg91_9.4]$ cat loadable_libraries.txt   
Could  not load library "$libdir/pg_tokenize"  
ERROR:could not access file "$libdir/pg_tokenize":  No such file or directory

备注：说明新版软件 9.4 没有安装 pg_tokenize 插件，这个插件是在安装中文全文索引 nlpbamboo 时需要安装的。

解决方法: 安装 nlpbamboo 相关插件

1
2
3
4
5
 
[root@db1 pg_tokenize]#export PGHOME=/opt/pgsql_9.4.1  
[root@db1 pg_tokenize]#export PATH=$PGHOME/bin:$PATH:.  
[root@db1 pg_tokenize]#export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib  
[root@db1 pg_tokenize]# make   
[root@db1 pg_tokenize]# make install

再次编译通过

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
[pg91@db1 ~]$ /opt/pgsql_9.4.1/bin/pg_upgrade -c --link -b /opt/pgsql_9.1.14/bin -B /opt/pgsql_9.4.1/bin -d /database/pg91/pg_root -D /database/pg91_9.4/pg_root  
  
*failure*  
Consult the last few lines of "pg_upgrade_server.log"  for  
the probable cause of the failure.  
Performing  Consistency  Checks on Old  Live  Server  
------------------------------------------------  
Checking cluster versions ok  
Checking database user is a superuser ok  
Checking  for prepared transactions ok  
Checking  for reg* system OID user data types ok  
Checking  for contrib/isn with bigint-passing mismatch ok  
Checking  for invalid "line" user columns ok  
Checking  for presence of required libraries ok  
Checking database user is a superuser ok  
Checking  for prepared transactions ok  
  
*Clusters are compatible*

pg_upgrade 升级

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
 
[pg91@db1 ~]$ /opt/pgsql_9.4.1/bin/pg_upgrade --link -b /opt/pgsql_9.1.14/bin -B /opt/pgsql_9.4.1/bin -d /database/pg91/pg_root -D /database/pg91_9.4/pg_root  
Performing  Consistency  Checks  
-----------------------------  
Checking cluster versions ok  
Checking database user is a superuser ok  
Checking  for prepared transactions ok  
Checking  for reg* system OID user data types ok  
Checking  for contrib/isn with bigint-passing mismatch ok  
Checking  for invalid "line" user columns ok  
Creating  dump of global objects ok  
Creating  dump of database schemas  
 ok  
Checking  for presence of required libraries ok  
Checking database user is a superuser ok  
Checking  for prepared transactions 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 on the new cluster ok  
Deleting files from  new pg_clog ok  
Copying old pg_clog to new server ok  
Setting  next transaction ID and epoch for  new cluster ok  
Deleting files from  new pg_multixact/offsets ok  
Setting oldest multixact ID on new cluster ok  
Resetting WAL archives ok  
Setting frozenxid and minmxid counters in  new cluster ok  
Restoring  global objects in the new cluster ok  
Adding support functions to new cluster ok  
Restoring database schemas in the new cluster  
 ok  
Setting minmxid counter in  new cluster ok  
Creating newly-required TOAST tables ok  
Removing support functions from  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  /database/pg91/pg_root/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 analyze new cluster ok  
Creating script to delete old cluster 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

备注：这里使用了 –link 模式, 升级完成后提示运行分析脚本 analyze_new_cluster.sh。

用新版本软件起新库

1
2
3
4
5
 
[pg91@db1 ~]$ /opt/pgsql_9.4.1/bin/pg_ctl --version  
pg_ctl (PostgreSQL)  9.4.1  
  
[pg91@db1 ~]$ /opt/pgsql_9.4.1/bin/pg_ctl start -D /database/pg91_9.4/pg_root  
server starting

备注：能正常启动。

版本验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
[pg91@db1 ~]$ /opt/pgsql_9.4.1/bin/psql  
psql (9.4.1)  
Type  "help"  for help.  
  
postgres=# select version();  
 version   
-------------------------------------------------------------------------------------------------------  
PostgreSQL  9.4.1 on i686-pc-linux-gnu, compiled by gcc (GCC)  4.4.6  20110731  (Red  Hat  4.4.6-3),  32-bit  
(1 row)  
  
postgres=# select * from test_1 limit 3;  
id | name   
----+------  
 1  | a_1  
 2  | a_2  
 3  | a_3  
(3 rows)  
  
postgres=# select count(*) from test_1;  
count   
-------  
 100  
(1 row)

备注：升级成功。

大版本升级后的操作

运行分析脚本

1
 
[pg91@db1 ~]$ ./analyze_new_cluster.sh

备注： 这个脚本其实就一条 vacuumdb 命令，收集新库统计信息。

删除老版本软件

1
2
3
4
 
[pg91@db1 ~]$ cat delete_old_cluster.sh   
#!/bin/sh  
  
rm -rf /database/pg91/pg_root

执行脚本

[pg91@db1 ~]$ ./delete_old_cluster.sh

备注：新库一切正常后，删除老版本软件。

修改 postgresql.conf, pg_hba.conf 等配置文件，根据生产需要，调整这两个文件的配置，在生产环境升级时，为了减少停机维护时间，可以事先写好这两个文件。

注意事项

  • 此篇 blog 仅演示 pg_upgrade 的基本使用，生产环境下操作请自行做好备份;
  • 此篇 blog 使用了 pg_upgrade 的 –link 模式，新版本软件共享老版本软件数据目录，用新版本软件启动数据目录后，再次用老版本软件启动目录会有问题。
  • 如果老版本软件安装了相关插件，使用 pg_upgrade 升级前，新版本软件也需要安装相关插件。

参考

原创文章，作者：254126420，如若转载，请注明出处：https://blog.ytso.com/239618.html

赞 (0)
0
上一篇 2022年2月12日
下一篇 2022年2月12日

相关推荐

发表回复

登录后才能评论