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;