注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL:使用 pg_upgrade 升级大版本  

2015-02-09 17:07:06|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

           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

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


--解压,编译,

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


--安装

gmake world
gmake install-world


--初始化: initdb

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



二  pg_upgrade 升级
--停老库

[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 前检查

[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 。

--报错信息

[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 相关插件

[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


--再次编译通过

[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 升级

[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。
 
 
--用新版本软件起新库

[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

备注:能正常启动。

--验证

[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)

备注:升级成功。

三 升级后的操作
--运行分析脚本

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

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

--删除老版本软件

[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 升级前,新版本软件也需要安装相关插件。

五参考
  评论这张
 
阅读(3131)| 评论(3)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016