postgresql数据库如何存储结构的教程

如果你升级数据库后无法启动 postgres 进程，并且检查日志发现类似如下信息：

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.2.

那就说明升级后的 PostgreSQL 改变了数据存储结构，导致旧版本的数据库完全不认了。

为什么升级会不支持旧的数据库文件

PostgreSQL 的版本号为三位，格式为 X.Y.Z。比如写这篇博客时最新版为 9.3.2 。看起来很像 semantic version 吧？以为第二位是 minor version 升级也没有多大影响吧？如果你这样想，那悲剧就开始了……

关于版本 这篇官方文档 有很详细的介绍。总而言之，对 PostgreSQL 而言，X.Y 都是 major version ，Z 才是 minor version 。minor version 不会改变数据库存储结构，而 major version 有可能会改。这就是为什么我从 9.2 升级到 9.3 也会出问题的原因。

解决方法

以下解决过程基于我的开发环境，所以如果你的环境有所不同（比如 Ubuntu），记住更改一些参数或目录。但大体思路还是一致的。

本人环境：操作系统 Mac OS X 10.9.1 (Maverick), 使用 Homebrew 作为包管理器安装 PostgreSQL 。旧版为 9.2.4 ，新版为 9.3.2 。

如果你的数据库里面都只是测试数据，并不在乎数据还原的问题，那大可直接删除掉旧的数据库文件，然后使用 initdb 命令创建一份新的。Homebrew 默认安装的 PostgreSQL 目录是 /usr/local/var/postgres 。

rm -rf /usr/local/var/postgres
initdb -D /usr/local/var/postgres

或者干脆用 Homebrew 卸载重装，也花不了多少时间。

如果你事先使用 pg_dump 备份过数据库，那可以执行完以上步骤后使用 pg_restore 重新导入数据。事实上这是 PostgreSQL 官方推荐的做法，升级前先备份，升级后还原回来。

如果你没有任何数据库备份，而且已有的数据库需要在新版 PostgreSQL 里使用，或者像我这样只是想折腾，那可以试试使用 pg_upgrade 命令升级数据库存储结构。这个命令需要四个参数：

    旧版的数据库目录
    新版的数据库目录
    旧版的 bin 目录
    新版的 bin 目录

对于 bin 目录，Homebrew 把每个版本都保留了下来，按版本号放在 /usr/local/Cellar/postgresql 目录下，比如我的：

# 旧版

/usr/local/Cellar/postgresql/9.2.4/bin
# 新版

/usr/local/Cellar/postgresql/9.3.2/bin

但数据库目录则不同，第一次安装 PostgreSQL 时，它新建数据库目录并放在 /usr/local/var/postgres 。以后的升级也会继续使用这个目录。所以说这个目录就是我们需要的旧版数据库目录。

那新版数据库目录呢？我们得自己去建立一个新目录，使用上文提到的 initdb 命令，并跟旧目录使用同一个名字。所以我们要先把旧版目录换个名字，再建立新目录。

mv /usr/local/var/postgres /usr/local/var/postgres_old
initdb -D /usr/local/var/postgres

当新目录创建好以后，准备工作就做完了。这时就可以使用 pg_upgrade 了。

# 加参数 v 只是显示详细信息，可以看到这个命令运行了哪些 SQL

pg_upgrade -b /usr/local/Cellar/postgresql/9.2.4/bin -B /usr/local/Cellar/postgresql/9.3.2/bin -d /usr/local/var/postgres_old -D /usr/local/var/postgres -v

这个过程稍微花点时间，取决你的数据库大小。命令执行完了就完成数据结构升级了。

剩下来还有点扫尾工作。一是把旧版数据库目录干掉。运行 pg_upgrade 命令时，它已经为我们在当前目录生成了一个文件叫 delete_old_cluster.sh 。它的作用就是删除掉 /usr/local/var/postgres_old 。你可以运行这个脚本，也可以自己手动去删除。最后别忘了把这个脚本也删除掉。

./delete_old_cluster.sh
rm ./delete_old_cluster.sh

另外还有一个用于优化的脚本 analyze_new_cluster.sh 。可以自行考虑要不要用。

最后，重启 PostgreSQL 并把它加入到开机启动中就行。Mac OS 系统是用的 launchctl 。

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

完工！使用 psql 测试，能连接并且查数据，就大功告成了。

如果使用 Ruby on Rails 的同学，记得同时升级 pg gem，老版本很可能导致无法建立数据库连接。

小结

升级数据库是一件谨慎的事情。最好事先查查资料，了解下新版兼不兼容，升级前作好数据备份。对 PostgreSQL 这样广泛使用的数据库而言，勤于升级是不会遇到问题的。即使是数据库结构改变，按照官方的文档也能很好的解决。升级数据库结构最好是采用 pg_dump 加 pg_restore 的方式升级，毕竟是官方推荐并保证没有问题的方式。而 pg_upgrade 对间隔较大的版本升级可能并不会太顺利。

对于 Mac 和 Homebrew 用户而言，使用 brew info postgresql 查看包信息是很有必要的，它会包含一些安装、升级问题的解答。也会给一些资料链接。