Upgrading Postgres.app without Shooting Yourself in The Foot

The?Postgres.app is “The easiest way to get started with PostgreSQL on the Mac”, as stated on their web page. It provides a full-featured PostgreSQL installation in a single package that just works.

Installing is as simple as:

• download
• unzip
• drag to Applications folder
• double-click

But what happens when you need to upgrade? Depending on the type of upgrade you are performing, there are?different?steps to take before and after.

Upgrading Between Bugfix Versions

Bugfix?versions?(9.3.1.0 to 9.3.1.1) are really straightforward: quit the app, and replace Postgres.app in your Applications directory; that’s it.

Upgrading Between Minor Releases

Minor releases (9.3.x to 9.4.x for example) involve?some steps that you?need to plan first to avoid problems with your data and a potential waste of time, let’s review:

The Postgres.app site is pretty clear about the issues, but you have to read the docs:

When updating between minor PostgreSQL releases (eg. 9.3.x to 9.4.x), Postgres.app will create a new, empty data directory. You are responsible for migrating the data yourself.

Backup Your Data

As obvious as it seems, we can never underestimate the importance of backing up our databases (and not only when upgrading versions). If you have several databases, the best way to do it is using?pg_dumpall. This is equivalent to using?pg_dump for each of your databases. From your command line, just type:

pg_dumpall > name_of_file.out

Backup Your Current Postgres.app Version

Copying the Postgres application bundle from Applications directory is the big part of backing up your current Postgres.app version, but it’s not the only one; there is another folder you should copy to the?backup folder you created to copy your app file: The default data directory:?~/Library/Application Support/Postgres/var-9.x.?Note: the var-9.x will be specific to a current version, so it could be var-9.1, var-9.3 or something else.

From the command line, you can copy the folder as follows:

cp -R Library/Application\ Support/Postgres/var-9.3 Workspace/PostgresApp\ your_backup_folder/var

The?-R?option will allow you to recursively copy the directory.

Install Your New Postgres.app Version

As stated at the beginning of this?article, just drop your new version’s file into your Applications folder and double click on it. This will create a new, empty data directory. But there’s more: in order to be able to use the command line tools, you need to modify your $PATH:

export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.4/bin

Restart your shell by typing:

exec $SHELL -l

And now you can restore your databases.

Restore Your Data

To restore all your databases in a single step, from the command line type:

psql -f name_of_file.out

Once it finishes, you can verify your data by opening psql and listing your databases:

your_computer#= \l

And now your are good to go. Enjoy.

Tags: postgresql, psql