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

Here is the solution for Ubuntu users

First we have to stop postgresql

sudo /etc/init.d/postgresql stop

Create a new file called /etc/apt/sources.list.d/pgdg.list and add below line

deb http://apt.postgresql.org/pub/repos/apt/ utopic-pgdg main

Follow below commands

wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.4
sudo pg_dropcluster --stop 9.4 main 
sudo /etc/init.d/postgresql start

Now we have everything, just need to upgrade it as below

sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

That's it. Mostly upgraded cluster will run on port number 5433. Check it with below command

sudo pg_lsclusters

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.


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;