Upgrading Postgres on Ubuntu

Since I had a post on how to update Postgres on OS X, I thought I might as well create one for Ubuntu for my own reference, as well.

First of all, take a look at this page to see how you can use the official Postgres Apt packages. It’s important to note that if you use these packages (maybe the ones from Canonical, too, but I’ve never used those), the data directory for Postgres will be put in different locations, and the old version will continue to start if you use the /etc/init.d script. Each version will start on a different port, beginning at 5432 (the default) for the oldest version.

Be sure to change the version numbers below:

sudo apt-get install -y postgresql-$new postgresql-server-dev-$new postgresql-contrib-$new postgresql-client-$new postgresql-doc-$new
sudo -u postgres psql -p 5432 -c "select version()" #make sure your old version is still on port 5432
sudo -u postgres psql -p 5433 -c "select version()" #make sure the new version is on port 5433
sudo -u postgres pg_dumpall -p 5432 > ~/pg_backup.sql #default home path is /var/lib/postgresql
sudo -u postgres psql -p 5433 < ~/pg_backup.sql
sudo -u postgres psql -p 5433 -l #make sure your databases were created successfully
sudo apt-get remove -y postgresql-$old postgresql-server-dev-$old postgresql-contrib-$old postgresql-client-$old postgresql-doc-$old
sudo /etc/init.d/postgresql restart
sudo -u postgres psql -c "select version()" #make sure the new version is on port 5432

Upgrading Brew-Installed PostgreSQL on OSX

Here’s the tl;dr: If you want to see the full instructions, look further down:

new_pg=9.3.3 #CHANGE THIS!!
psql postgres -c "select version()" #make sure you've still got the old version running
pg_dumpall > pg_backup.sql
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
sudo mv /usr/local/var/postgres /usr/local/var/postgres.old
sudo brew switch postgresql $new_pg
initdb /usr/local/var/postgres -E utf8
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
createdb #run this if you get an error when trying to import the dump below
psql < pg_backup.sql
psql postgres -c "select version()" #make sure you've got the new version running
psql -l #see if all of your databases are still there
vacuumdb --all --analyze-only #optional
sudo brew cleanup postgresql #this will remove the older versions brew installed
sudo rm -rf /usr/local/var/postgres.old #if you're feeling really confident / don't care

Somehow, whenever I sit down to do some tinkering on my computer, it seems like a new version of PostgreSQL is ready to be installed via Brew. Since I always forget what the steps are to upgrade between major versions of Postgres, I figured I’d throw it into a blog post. Mostly for my own pleasure, but others might find it useful, as well.

First, you realize that Brew is installing the new version, and you panic! “What about my data!?” you might say. Don’t worry, the old version is still running. You can verify this by opening psql, and running select version(). You should see your old version. We need to dump your databases so that you can restore them to your new version:

pg_dumpall > pg_backup.sql

Now, we need to stop the old version, move the old cluster to a safe place (in case we need to revert for some reason), tell brew we want to use the new version as the default, and start the new one. It sounds complicated, but it’s not:

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
sudo mv /usr/local/var/postgres /usr/local/var/postgres.old
sudo brew switch postgresql 9.3.3
initdb /usr/local/var/postgres -E utf8
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Now, restore the dump into the new version:

psql < pg_backup.sql

If you get an error saying that the database (usually named the same as your login) could not be found, just crate it, using the following command:


You should now be good to go! As a bonus, if you get this error (which I did)

➜ ~ psql
dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib
Referenced from: /usr/local/bin/psql
Reason: image not found
[1] 39772 trace trap psql

You either need to install readline from brew, or it needs to be re-linked:

sudo brew install readline
sudo brew link --force readline

If you had to install readline, then you will need to re-install postgres:

sudo brew reinstall postgresql