Migrating Data from an Upgraded Postgres

I recently upgraded Postgres on my development machine from 9.1.4 to 9.2.1.

Things didn’t go smoothly, however, because I forgot to migrate all my data. Since I seem to forget how to do this every time I upgrade between minor versions of Postgres (and I’m sure other developers do as well), I’m documenting the process and sharing my solution to reference later.

Note: these steps assume installation with Homebrew.

1. Initialize a new database, adding a .new suffix to the directory that Homebrew recommends.

   initdb /usr/local/var/postgres.new -E utf8

2. Run the upgrade script, providing the correct paths for the various flags.

   pg_upgrade \ -b /usr/local/Cellar/postgresql/9.1.4/bin \ -B /usr/local/Cellar/postgresql/9.2.1/bin \ -d /usr/local/var/postgres \ -D /usr/local/var/postgres.new

3. Put the data in the correct place.

   rm -rf /usr/local/var/postgres mv /usr/local/var/postgres.new /usr/local/var/postgres

If you’ve set up launchd to run Postgres automatically, everything should be up and running! Otherwise, check out the documentation with brew info postgres to read how to have OS X manage postgres for you.