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

Solution 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/
   

   -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

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.

Solution 2:

Despite all answers above, here goes my 5 cents.

It works on any OS and from any-to-any postgres version.

• Stop any running postgres instance;
• Install the new version and start it; Check if you can connect to the new version as well;
• Change old version's postgresql.conf -> port from 5432 to 5433;
• Start the old version postgres instance;
• Open a terminal and cd to the new version bin folder;
• Run pg_dumpall -p 5433 -U <username> | psql -p 5432 -U <username>
• Stop old postgres running instance;