Tutorial on how to store the structure of a PostgreSQL database

If you do not start the Postgres process after you upgrade the database, and check the log to find similar information like the following:

Fatal:database files are incompatible with server Detail:the Data directory is initialized by PostgreSQL version 9.2, W Hich is isn't compatible with this version 9.3.2.

That means that the upgraded PostgreSQL changed the data storage structure, resulting in the old version of the database completely out of recognition.

Why the upgrade does not support old database files

The PostgreSQL version number is three digits and is in x.y.z format. For example, when writing this blog, the latest version is 9.3.2. Looks like semantic version, doesn't it? Think the second is minor version upgrade is not much impact, right? If you think so, the tragedy begins ...

The official document on the version is described in detail. All in all, for PostgreSQL, X.Y is major version, and Z is minor version. Minor version does not change the database storage structure, and major version may change. That's why I'm upgrading from 9.2 to 9.3 and it's also going to cause problems.

Solving Method

The following troubleshooting process is based on my development environment, so if your environment is different (such as Ubuntu), remember to change some parameters or directories. But the general idea is still the same.

My environment: Operating system MAC OS X 10.9.1 (Maverick), using Homebrew as the Package Manager to install PostgreSQL. The old version is 9.2.4 and the new version is 9.3.2.

If your database is just test data, and do not care about the problem of data restore, it can be deleted directly from the old database files, and then use the INITDB command to create a new one. Homebrew the PostgreSQL directory installed by default is/usr/local/var/postgres.

Rm-rf/usr/local/var/postgres
Initdb-d/usr/local/var/postgres

Or simply use Homebrew unloading load, also can not spend much time.

If you have previously used pg_dump to back up your database, you can use Pg_restore to re-import the data after you have done the above steps. In fact, this is the official recommendation of the PostgreSQL, the upgrade before the backup, upgrade and restore back.

If you don't have any database backups, and the existing database needs to be used in the new PostgreSQL, or just like I'm trying to toss it, try using the Pg_upgrade command to upgrade the database storage structure. This command requires four parameters:

Old version of the database directory
New version of the database directory
Old version of Bin directory
New version of the bin directory

For the bin directory, Homebrew keeps each version, by version number in the/usr/local/cellar/postgresql directory, such as mine:

# old Edition

/usr/local/cellar/postgresql/9.2.4/bin
# New Edition

/usr/local/cellar/postgresql/9.3.2/bin

However, the database directory is different, the first time you install PostgreSQL, it creates a new database directory and puts it in/usr/local/var/postgres. Future upgrades will also continue to use this directory. So this directory is the old version of the database directory that we need.

What about the new database catalog? We have to build a new directory by ourselves, using the Initdb command mentioned above and using the same name as the old directory. So we have to change the old version of the directory first name, and then create a new directory.

Mv/usr/local/var/postgres/usr/local/var/postgres_old
Initdb-d/usr/local/var/postgres

When the new directory is created, the preparation is done. Then you can use the Pg_upgrade.

# Plus parameter v just displays the details and you can see which SQL the command runs

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

This process takes a little time, depending on your database size. When the command is finished, the data structure is upgraded.

The rest is still a bit of cleanup work. One is to kill the old version of the database directory. When running the Pg_upgrade command, it has generated a file called delete_old_cluster.sh for us in the current directory. Its role is to remove/usr/local/var/postgres_old. You can run this script, or you can manually delete it yourself. Finally, don't forget to delete this script as well.

./delete_old_cluster.sh
Rm./delete_old_cluster.sh

In addition, there is a script analyze_new_cluster.sh for optimization. You can think about it yourself.

Finally, reboot the PostgreSQL and add it to the boot boot. Mac OS systems are launchctl.

Launchctl Unload ~/library/launchagents/homebrew.mxcl.postgresql.plist
Launchctl Load ~/library/launchagents/homebrew.mxcl.postgresql.plist

Completed! Using the Psql test, you can connect and check the data, and you're done.

If you are using Ruby on Rails, remember to upgrade the PG gem at the same time, the older version is likely to cause the database connection to not be established.

Summary

Upgrading the database is a prudent thing to do. It is best to check the information beforehand, understand the new version and incompatible, before the upgrade to make a good backup of data. Upgrades are not a problem for a widely used database such as PostgreSQL. Even if the structure of the database changes, according to the official documents can be well resolved. Upgrading your database structure is best implemented by Pg_dump plus Pg_restore, which is officially recommended and guaranteed to be a problem-free way. Pg_upgrade to a larger version of the interval may not be going too well.

For Mac and Homebrew users, it is necessary to use the Brew info PostgreSQL to view package information, which will include answers to installation and upgrade questions. will also give some data links.