Postgres 9.4 has finally shipped. This long awaited release includes numerous upgrades, including native support for JSON, and improved scalability and index performance.
Upgrading to Postgres 9.4.0 is not as simple as merely replacing your current 9.3.x version with the newer
version. The release notes indicate that users who wish to upgrade must first migrate their existing data
from prior versions using one of two strategies:
There are typically two ways that OS X users install Postgres on their machine: via the OS X native Postgres.app (the one with the blue elephant icon) or using the package manager
I’ll provide instructions for Postgres.app using
pg_dumpall to upgrade on OS X.
If you installed with
homebrew follow the excellent instructions found here: Keita’s Blog: Homebrew and PostgreSQL 9.4
1: Export your existing databases to a SQL script file
Postgres stores databases in version specific directories. So before we get started we need to know what
the current Postgres data directory is for the installed version. Fire up your postgres console
and find out with
SHOW data_directory;. Your output may look different, but what we want is the path that
is returned. On my machine it looked like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Make a note of this directory. Once the data has been migrated and you’ve confirmed everything is working you’ll probably want to delete the old copy to free up space.
Next export the existing 9.3 databases out of that directory prior to upgrading to 9.4:
1 2 3
It’s worth noting that like many unix commands you will get no feedback from this command while it is running.
Depending on how many and/or how large your pg databases are this may take a while. When you are returned to
your standard shell prompt run
ls and if you see
db.out you are ready to go.
2: Download the latest Postgres.app
Click the elephant icon in the OS X menubar and choose
quit to stop the server.
Go to your Applications directory and find the Postgres app (the blue elephant icon). Rename it to Postgres.old.
Go to http://postgresapp.com and download the latest version. Double cick the downloaded zip file and drag the new Postgres app into your Applications folder.
Double click the icon and wait for the application to start. From either the elephant icon in the menu bar
psql command check the version number. It should be 9.4.0.
3: Import the SQL dump into the new server directory.
From the directory where you ran
pg_dumpall in step 1 run the following command:
Unlike the original
pg_dumpall command you will see a lot of activity on the screen, including some warnings
that look like errors. This is expected output, so be patient while the databases are recreated in the new 9.4
Once you get your command prompt back, go into the
psql console and check you databases are all there the
\l. At a minimum you should have output like the example below, where one database typically has
your OSX or postgres admin username, two are templates, and one is postgres:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
4: Clean up the export file and the old data directory
Verify that your applications can access your databases, and that everything is working as expected. Once you are comfortable with that you have some cleaning up to do.
Backup or delete the
If you are using
postgres.app delete the
postgres.old application from your Applications folder.
Finally using the
SHOW data_directory; command from step 1, verify that the new directory is not the same
as the old one, and then you can safely delete the old database directory. In my case the old directory was
~/Library/Application Support/Postgres/var-9.3, and the new one is now:
1 2 3 4 5 6 7 8 9 10 11
You can remove or backup the old directory at your discretion.
That completes your upgrade to 9.4.0. For any patch revisions, i.e.
9.4.x, that come later you can simply upgrade the postgres application by downloading the newer Postgres.app and installing it over the old version.