Ubuntu upgrade postgres to version 10

Install PostgreSQL 10 on Ubuntu

In this tutorial we are going to upgrade PostgreSQL 9.X to 10. The following steps will work for Ubuntu 14.04, 17.04 and 16.04. You can check your current PostreSQL version with:

dpkg -l | grep postgres

Step 1: Remove current installed version

First check the current data directory, we will need to migrate the data to postgresql 10 later (step 4)

SHOW data_directory;

You will alse need the configuration directory for the migration from 9 to 10:

SHOW config_file;

Before removing postgresql old version we need to do a backup:

pg_dumpall > backup_all.db

We need to remove the installed version with apt-get, replace the X with the current installed version:

sudo apt-get --purge remove postgresql postgresql-9.X postgresql-client-9.X postgresql-client-common  postgresql-common postgresql-contrib-9.X

Step 2: Add PostgreSQL apt repository

We need to import the repository signing key from postgresql.org:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

The current default Ubuntu repositories only have up to postgresql-9.6, we'll add the official postgres apt repository:

  • Ubuntu 14.04: sudo add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main'
  • Ubuntu 16.04: sudo add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main'
  • Ubuntu 17.04: sudo add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ zesty-pgdg main'

Step 3: Install PostgreSQL

sudo apt update
sudo apt install postgresql-10

Ensure that the server is started by switching to the postgres user.

sudo su - postgres
/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Which means your PostgreSQL 10 is up and running!

Step 4: Restore old database

We need to restore old database form the backup we made:

psql -f db.out postgres

Appendix

(Optional) Create a user for yourself

PostgreSQL allows you to use operating system level authentication by default. You can create a database with your username, since the psql command uses your username as a database. If it doesn't exist, psql throws a pesky error.

You need ot execute the following command with postgres user (sudo su postgres)

psql
CREATE ROLE <username> SUPERUSER LOGIN REPLICATION CREATEDB CREATEROLE;
CREATE DATABASE <username> OWNER <username>;
\q

If postgresql start fails...

If starting postgresql server fails:

  • restart it to be safe: /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile restart

That should return something like:

waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started