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:
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.
Bugfix versions (22.214.171.124 to 126.96.36.199) are really straightforward: quit the app, and replace Postgres.app in your Applications directory; that’s it.
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.
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
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.
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:
Restart your shell by typing:
exec $SHELL -l
And now you can restore your databases.
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:
And now your are good to go. Enjoy.