upgarde postgresql

发表于

PostgreSQL发布9.3了， brew upgrade postgresql 升级到9.3， 竟然启动不起来， 查看日志发现原来9.2的数据格式不兼容，需要迁移一下数据， 碰到这个问题的同学可以看一下 :-)

错误日志, 数据不兼容

/usr/local(master ✔) tail -f /usr/local/var/postgres/server.log
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 9.3.1.

解决办法

PostgreSQL提供了一个升级迁移脚本 pg_upgrade, 用来迁移数据 

pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir [option…]

1. 新建一个PostgreSQL9.3的数据目录

/usr/local/var(master ✔) mv postgres postgres9.2
/usr/local/var(master ✔) initdb /usr/local/var/postgres -E utf8

2. 迁移数据到新目录中

/usr/local/var(master ✔) pg_upgrade \
-b /usr/local/Cellar/postgresql/9.2.4/bin/ \
-B /usr/local/Cellar/postgresql/9.3.1/bin/ \
-d /usr/local/var/postgres9.2 \
-D /usr/local/var/postgres \
-v

最后迁移完成打印下面的信息就代表迁移成功了

…
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

3. 启动PostgreSQL9.3

查看版本和数据

/usr/local/var(master ✔) run_postgresql
server starting
/usr/local/var(master ✔) psql postgres
psql (9.3.1)
Type "help" for help.

postgres=# \l

4. 删除老版本和数据

删除数据和刚刚执行pg_upgrade产生的两个脚本

/usr/local/var(master ✔) rm -rf analyze_new_cluster.sh delete_old_cluster.sh postgres9.2

卸载PostgreSQL9.2.4

brew cleanup postgresql