使用pg upgrade升级数据库

作者：瀚高科技有限公司 连顺科
仅允许在不作任何修改的前提下转载。

说明：
从postgresql 9.0开始，pg大版本的升级可以使用pg_upgrade，而不需要（不是不可以）pg_dump/pg_restore。
手册中关于pg_upgrade的部分在如下链接：
9.0
9.1
9.2
注意手册中所说：
pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be migrated to a later PostgreSQL
major version without the data dump/reload typically required for major version upgrades, e.g. from 8.4.7 to the current major
release of PostgreSQL. It is not required for minor version upgrades, e.g. from 9.0.1 to 9.0.4.

小版本的升级，比如9.0.1到9.0.4不需要使用pg_upgrade。


简单的例子：
这个例子使用pg_upgrade将数据库从9.1.7升级到9.2.2。

安装pg 9.2.2并初始化
使用pg_upgrade前必须首先将新版本安装完毕并初始化一个空的cluster，不论是用源码还是用安装包安装。
另外需要注意的是，必须使用目标版本的pg_upgrade，在本例中要使用9.2.2的pg_upgrade，而不能使用9.1.7的。
{安装和初始化过程略。}
初始化9.2.2完毕后，两版本数据库的目录为：
PGBINOLD=/opt/psql/917/bin
PGBINNEW=/opt/psql/922/bin
PGDATAOLD=/work/data/pgdata917
PGDATANEW=/work/data/pgdata922
关闭原库和目标库：
执行pg_upgrade前必须关闭原库和目标库。
pg_ctl stop -d PGDATA917 -m f
pg_ctl stop -d PGDATA922 -m f

执行pg_upgrade
如果配置了环境变量，则可以直接执行：
pg_upgrade -d $PGDATAOLD -D $PGDATANEW -b $PGBINOLD -B $PGBINNEW

也可以不配制环境变量：
pg_upgrade --old-bindir=/opt/psql/917/bin \
--new-bindir=/opt/psql/922/bin \
--old-datadir=/work/data/pgdata917 \
--new-datadir=/work/data/pgdata922

如果加-c选项，则只做检查，而不真正做升级。

执行检查过程如下：
$ pg_upgrade -d $PGDATAOLD -D $PGDATANEW -b $PGBINOLD -B $PGBINNEW -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 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*

升级过程如下：
$ pg_upgrade -d $PGDATAOLD -D $PGDATANEW -b $PGBINOLD -B $PGBINNEW

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 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
/work/data/pgdata/base/16558/18514
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
注意升级过程中的提示信息：
If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing.
如果在checking后的升级过程中出现错误或者失败，那么必须re-initdb。

以上是9.2升级过程中的提示信息。如果使用9.1的pg_upgrade，那么除了重新初始化外，还需要将原数据库中global/pg_control.old改回原名。
| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from /work/data/pgdata/global/pg_control.old.

执行analyze_new_cluster.sh
pg_upgrade执行完毕后，会在执行该命令的当前目录下生成analyze_new_cluster.sh、delete_old_cluster.sh两个脚本。
其中脚本analyze_new_cluster.sh会按照不同级别收集数据信息，以便数据库能够升级后整场运行。脚本内容如下：
#!/bin/sh

echo 'This script will generate minimal optimizer statistics rapidly'
echo 'so your system is usable, and then gather statistics twice more'
echo 'with increasing accuracy. When it is done, your system will'
echo 'have the default level of optimizer statistics.'
echo
echo 'If you have used ALTER TABLE to modify the statistics target for'
echo 'any tables, you might want to remove them and restore them after'
echo 'running this script because they will delay fast statistics generation.'
echo

echo 'If you would like default statistics as quickly as possible, cancel'
echo 'this script and run:'
echo ' "/opt/psql/922/bin/vacuumdb" --all --analyze-only'
echo

sleep 2
PGOPTIONS='-c default_statistics_target=1 -c vacuum_cost_delay=0'
export PGOPTIONS
echo 'Generating minimal optimizer statistics (1 target)'
echo '--------------------------------------------------'
"/opt/psql/922/bin/vacuumdb" --all --analyze-only
echo
echo 'The server is now available with minimal optimizer statistics.'
echo 'Query performance will be optimal once this script completes.'
echo

sleep 2
PGOPTIONS='-c default_statistics_target=10'
echo 'Generating medium optimizer statistics (10 targets)'
echo '---------------------------------------------------'
"/opt/psql/922/bin/vacuumdb" --all --analyze-only
echo

unset PGOPTIONS
echo 'Generating default (full) optimizer statistics (100 targets?)'
echo '-------------------------------------------------------------'
"/opt/psql/922/bin/vacuumdb" --all --analyze-only
echo

echo 'Done'
注释简单明了，不多解释。
delete_old_cluster.sh
脚本delete_old_cluster.sh的目的是删除原来低版本数据库，内容更简单：
#!/bin/sh
rm -rf /work/data/pgdata
rm -rf /work/data/pgdata/tbs1/PG_9.1_201105231
rm -rf /work/data/pgdata/tbs2/PG_9.1_201105231
可以看到，除了删除数据库目录外，还删除表空间目录。