Upgrading Postgres.app without Shooting Yourself in The Foot

Upgrading Postgres.app without Shooting Yourself in The Foot

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: ,

leave a comment

Your email address will not be published.

This content shows up if there are no widgets defined in the backend.