pg_upgrade PG 数据库快捷升级的利器,在一定的大版本跨度下,可以实现二进制数据文件的直接升级
避免 dump/restore 的升级方式,节省时间。
主要的升级步骤:
1. 需要先停掉旧版本数据库 olddb
2.如果olddb 安装目录为/usr/local/pgsql 这样,需要改名 PGDATA目录,注意如果有‘额外的表空间,需要注意pg_tblspace 里的符号链接
或者 新版本不用同样的安装目录
如果新旧版本 是类似这样 /usr/local/olddb /usr/local/newdb 则不存在这个问题。
3.安装newdb 并init 注意新旧两个库的port
4.如果olddb 有安装社区组件,需要在newdb上安装。
5.stop newdb
6.先检查是否兼容,或者报错。
我们测试环境的一个例子输出:[code]
[postgres@cmsdb-11-211 pgsql9.2.3]$ bin/pg_upgrade --old-datadir=/usr/local/pgsql9.1.4/data --new-datadir=/usr/local/pgsql9.2.3/data --old-bindir=/usr/local/pgsql9.1.4/bin --new-bindir=/usr/local/pgsql9.2.3/bin -c
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
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 invalid indexes from concurrent index builds ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok
*Clusters are compatible*
[/code]7. 没有报错,执行升级
我们环境的测试:[code]
[postgres@cmsdb-11-211 pgsql9.2.3]$ bin/pg_upgrade --old-datadir=/usr/local/pgsql9.1.4/data --new-datadir=/usr/local/pgsql9.2.3/data --old-bindir=/usr/local/pgsql9.1.4/bin --new-bindir=/usr/local/pgsql9.2.3/bin -u postgres -p 5435 -P 5436
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
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 invalid indexes from concurrent index builds ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating catalog dump 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 for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster ok
Removing support functions from new cluster ok
Copying user relation files
ok
Setting next OID for new cluster 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
[postgres@cmsdb-11-211 pgsql9.2.3]$
[/code]
注意事项:
切记不要运行 上面提示的delete_old_cluster.sh
说明 : 如果数据库里有手工添加的表空间,那么实际上升级完成后,newdb 里只有一个到olddb 的表空间的符号链接,
这个应该要手工把这个表空间目录下的文件copy到newdb 中
并修改pg_tblspc 里对应的符号链接。
8.启动新库 调整参数,然后运行 analyze_new_cluster.sh
9.确保系统正常后,可以考虑手工删除旧库的数据目录。
10 后记:
pg 在小版本之间的升级,例如从9.2.2 升级到9.2.3 一般情况下是不需要使用pg_upgrade来升级的,直接编译安装9.2.3,然后覆盖掉9.2.2的二进制执行代码就可以了。
这个时候,需要看release note 。
我在做9.2.2 到9.2.3 的pg_upgrade 的时候报了一个错误。
如下。
[code]
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
Cannot upgrade to/from the same system catalog version when
using tablespaces.
Failure, exiting
[/code]
避免 dump/restore 的升级方式,节省时间。
主要的升级步骤:
1. 需要先停掉旧版本数据库 olddb
2.如果olddb 安装目录为/usr/local/pgsql 这样,需要改名 PGDATA目录,注意如果有‘额外的表空间,需要注意pg_tblspace 里的符号链接
或者 新版本不用同样的安装目录
如果新旧版本 是类似这样 /usr/local/olddb /usr/local/newdb 则不存在这个问题。
3.安装newdb 并init 注意新旧两个库的port
4.如果olddb 有安装社区组件,需要在newdb上安装。
5.stop newdb
6.先检查是否兼容,或者报错。
我们测试环境的一个例子输出:[code]
[postgres@cmsdb-11-211 pgsql9.2.3]$ bin/pg_upgrade --old-datadir=/usr/local/pgsql9.1.4/data --new-datadir=/usr/local/pgsql9.2.3/data --old-bindir=/usr/local/pgsql9.1.4/bin --new-bindir=/usr/local/pgsql9.2.3/bin -c
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
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 invalid indexes from concurrent index builds ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok
*Clusters are compatible*
[/code]7. 没有报错,执行升级
我们环境的测试:[code]
[postgres@cmsdb-11-211 pgsql9.2.3]$ bin/pg_upgrade --old-datadir=/usr/local/pgsql9.1.4/data --new-datadir=/usr/local/pgsql9.2.3/data --old-bindir=/usr/local/pgsql9.1.4/bin --new-bindir=/usr/local/pgsql9.2.3/bin -u postgres -p 5435 -P 5436
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
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 invalid indexes from concurrent index builds ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating catalog dump 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 for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster ok
Removing support functions from new cluster ok
Copying user relation files
ok
Setting next OID for new cluster 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
[postgres@cmsdb-11-211 pgsql9.2.3]$
[/code]
注意事项:
切记不要运行 上面提示的delete_old_cluster.sh
说明 : 如果数据库里有手工添加的表空间,那么实际上升级完成后,newdb 里只有一个到olddb 的表空间的符号链接,
这个应该要手工把这个表空间目录下的文件copy到newdb 中
并修改pg_tblspc 里对应的符号链接。
8.启动新库 调整参数,然后运行 analyze_new_cluster.sh
9.确保系统正常后,可以考虑手工删除旧库的数据目录。
10 后记:
pg 在小版本之间的升级,例如从9.2.2 升级到9.2.3 一般情况下是不需要使用pg_upgrade来升级的,直接编译安装9.2.3,然后覆盖掉9.2.2的二进制执行代码就可以了。
这个时候,需要看release note 。
我在做9.2.2 到9.2.3 的pg_upgrade 的时候报了一个错误。
如下。
[code]
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
Cannot upgrade to/from the same system catalog version when
using tablespaces.
Failure, exiting
[/code]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/133735/viewspace-754387/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/133735/viewspace-754387/