How to upgrade PostgreSQL from version 9.6 to vers

Question

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.

Answer 1

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

1. Stop current Postgres server:

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

2. Initialize a new 10.1 database:

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

3. 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/
   

4. Move new data into place:

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

5. Restart Postgres:

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

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

7. 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.

Answer 2

Looks like the solution has been baked into Homebrew now:

$ brew info postgresql
...
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database
....

Answer 3

I think this is best link for your solution to update postgres to 9.6

https://sandymadaan.wordpress.com/2017/02/21/upgrade-postgresql9-3-9-6-in-ubuntu-retaining-the-databases/

Answer 4

Standing on the shoulders of the other poor creatures trodding through this muck, I was able to follow these steps to get back up and running after an upgrade to Yosemite:

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

1. Stop current Postgres server:

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

2. Initialize a new 9.4 database:

   initdb /usr/local/var/postgres9.4 -E utf8

3. Install postgres 9.3 (as it was no longer present on my machine):

   brew install homebrew/versions/postgresql93

4. Add directories removed during Yosemite upgrade:

   mkdir -p /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp}/touch /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat_tmp}/.keep

5. run pg_upgrade:

   pg_upgrade -v -d /usr/local/var/postgres -D /usr/local/var/postgres9.4 -b /usr/local/Cellar/postgresql93/9.3.5/bin/ -B /usr/local/Cellar/postgresql/9.4.0/bin/

6. Move new data into place:

   cd /usr/local/var
   mv postgres postgres9.3
   mv postgres9.4 postgres
   

7. Restart Postgres:

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

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

9. Finally, re-install related libraries?

   pip install --upgrade psycopg2
   gem uninstall pg
   gem install pg
   

Answer 5

For Mac via homebrew:

brew tap petere/postgresql,

brew install <formula> (eg: brew install petere/postgresql/postgresql-9.6)

Remove old Postgres:

brew unlink postgresql

brew link -f postgresql-9.6

If any error happen, don't forget to read and follow brew instruction in each step.

Check this out for more: https://github.com/petere/homebrew-postgresql

Answer 6

The user manual covers this topic in depth. You can:

• pg_upgrade in-place; or

• pg_dump and pg_restore.

If in doubt, do it with dumps. Don't delete the old data directory, just keep it in case something goes wrong / you make a mistake; that way you can just go back to your unchanged 9.3 install.

For details, see the manual.

If you're stuck, post a detailed question explaining how you're stuck, where, and what you tried first. It depends a bit on how you installed PostgreSQL too, as there are several different "distributions" of PostgreSQL for OS X (unfortunately). So you'd need to provide that info.