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.

Hound automatically reviews Ruby, JavaScript, and CoffeeScript code in your GitHub pull requests and comments on style violations. It is free for open source repos and $12/month per private repo.