How to upgrade PostgreSQL from version 9.6 to version 10.1 without losing data?

postgresql, upgrade

Solution

Assuming you've used home-brew to install and upgrade Postgres, you can perform the following steps.

Stop current Postgres server:

`launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist`

Initialize a new 10.1 database:

`initdb /usr/local/var/postgres10.1 -E utf8`

run `pg_upgrade` (note: change bin version if you're upgrading from something other than below):

pg_upgrade -v \
    -d /usr/local/var/postgres \
    -D /usr/local/var/postgres10.1 \
    -b /usr/local/Cellar/postgresql/9.6.5/bin/ \
    -B /usr/local/Cellar/postgresql/10.1/bin/

`-v` to enable verbose internal logging

`-d` the old database cluster configuration directory

`-D` the new database cluster configuration directory

`-b` the old PostgreSQL executable directory

`-B` the new PostgreSQL executable directory

Move new data into place:

cd /usr/local/var
mv postgres postgres9.6
mv postgres10.1 postgres

Restart Postgres:

`launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist`

Check `/usr/local/var/postgres/server.log` for details and to make sure the new server started properly.

Finally, re-install the rails `pg` gem

gem uninstall pg
gem install pg

I suggest you take some time to read the PostgreSQL documentation to understand exactly what you're doing in the above steps to minimize frustrations.

Problem

I'm using the PostgreSQL database for my Ruby on Rails application (on Mac OS X 10.9). Are there any detailed instructions on how to upgrade PostgreSQL database? I'm afraid I will destroy the data in the database or mess it up.

Original source