Installing latest PostgreSQL release in Debian 9 (and 8)

Kid with Debian logo in front of big elephant

Debian is absolutely marvelous in almost everything, but updates policy, most distributions struggle to keep their packages on latests software versions, but it's worse when you are using Stretch or Jessie.

PostgreSQL is a clear example, if we are using Jessie then the latest available release is 9.4, if we are using Stretch, latest available release is 9.6.15, while I am writing this article PostgreSQL release 12 is in beta stage. There are tons of nice functionalities waiting for us in releases 10 and 11 of PostgreSQL and it's an absolute pity they are not available for Stretch... or might be there are?

Luckily there is an specific PostgreSQL repository for Debian that will allow us to install latest packages in our Debian box without having to compile our own sources, let's see how to use this repository.

Installing a new repository

In order to install a new repository, first we need to do is adding the repository to our sources list, a oneliner to do so is:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Once it's done we need to install these packages in order to get the needed signatures (only if they are not installed on our server):

sudo apt-get install wget ca-certificates

With this installed we can now obtain the repository GPG key to add to our key registry:

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

Now we can update packages:

sudo apt update
Hit:1 http://security.debian.org stretch/updates InRelease
Get:2 http://apt.postgresql.org/pub/repos/apt stretch-pgdg InRelease [51.4 kB]
Get:3 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 Packages [183 kB]

Once package data is updated we will see we have postgresql related packages available for updates:

apt list --upgradeable
Listing... Done
libdbd-pg-perl/stretch-pgdg 3.7.4-3~pgdg90+1 amd64 [upgradable from: 3.5.3-1+b2]
libpq5/stretch-pgdg 11.5-1.pgdg90+1 amd64 [upgradable from: 9.6.15-0+deb9u1]
pgbouncer/stretch-pgdg 1.11.0-1.pgdg90+1 amd64 [upgradable from: 1.7.2-2]
postgresql/stretch-pgdg 11+207.pgdg90+1 all [upgradable from: 9.6+181+deb9u2]
postgresql-9.6/stretch-pgdg 9.6.15-1.pgdg90+1 amd64 [upgradable from: 9.6.15-0+deb9u1]
postgresql-9.6-plr/stretch-pgdg 1:8.4-1.pgdg90+1 amd64 [upgradable from: 1:8.3.0.17-1]
postgresql-client-9.6/stretch-pgdg 9.6.15-1.pgdg90+1 amd64 [upgradable from: 9.6.15-0+deb9u1]
postgresql-client-common/stretch-pgdg 207.pgdg90+1 all [upgradable from: 181+deb9u2]
postgresql-common/stretch-pgdg 207.pgdg90+1 all [upgradable from: 181+deb9u2]
postgresql-contrib-9.6/stretch-pgdg 9.6.15-1.pgdg90+1 amd64 [upgradable from: 9.6.15-0+deb9u1]
python-psycopg2/stretch-pgdg 2.7.6.1-3.pgdg90+1 amd64 [upgradable from: 2.6.2-1]

We will also have newer releases available:

sudo apt-cache search postgresql|grep "object-relational SQL database"
postgresql-9.6 - object-relational SQL database, version 9.6 server
postgresql - object-relational SQL database (supported version)
postgresql-10 - object-relational SQL database, version 10 server
postgresql-11 - object-relational SQL database, version 11 server
postgresql-9.2 - object-relational SQL database, version 9.2 server
postgresql-9.3 - object-relational SQL database, version 9.3 server
postgresql-9.4 - object-relational SQL database, version 9.4 server
postgresql-9.5 - object-relational SQL database, version 9.5 server

Updating

The first thing we can do is updating our current packages before going to newer releases (what we will do in a future article) since the update process between major releases is not immediate:

sudo apt-get upgrade
Reading package lists... Done
Building dependency tree
Reading state information... Done
Calculating upgrade... Done
The following packages have been kept back:
  pgbouncer postgresql postgresql-client-common postgresql-common
The following packages will be upgraded:
  libdbd-pg-perl libpq5 postgresql-9.6 postgresql-9.6-plr postgresql-client-9.6 postgresql-contrib-9.6 python-psycopg2
7 upgraded, 0 newly installed, 0 to remove and 4 not upgraded.
Need to get 6677 kB of archives.
After this operation, 89.1 kB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 libpq5 amd64 11.5-1.pgdg90+1 [167 kB]
Get:2 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 libdbd-pg-perl amd64 3.7.4-3~pgdg90+1 [207 kB]
Get:3 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 postgresql-contrib-9.6 amd64 9.6.15-1.pgdg90+1 [502 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 postgresql-client-9.6 amd64 9.6.15-1.pgdg90+1 [1280 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 postgresql-9.6 amd64 9.6.15-1.pgdg90+1 [4298 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 postgresql-9.6-plr amd64 1:8.4-1.pgdg90+1 [59.9 kB]
Get:7 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 python-psycopg2 amd64 2.7.6.1-3.pgdg90+1 [164 kB]
Fetched 6677 kB in 1s (3660 kB/s)
Preconfiguring packages ...
(Reading database ... 77331 files and directories currently installed.)
Preparing to unpack .../0-libpq5_11.5-1.pgdg90+1_amd64.deb ...
Unpacking libpq5:amd64 (11.5-1.pgdg90+1) over (9.6.15-0+deb9u1) ...
Preparing to unpack .../1-libdbd-pg-perl_3.7.4-3~pgdg90+1_amd64.deb ...
Unpacking libdbd-pg-perl (3.7.4-3~pgdg90+1) over (3.5.3-1+b2) ...
Preparing to unpack .../2-postgresql-contrib-9.6_9.6.15-1.pgdg90+1_amd64.deb ...
Unpacking postgresql-contrib-9.6 (9.6.15-1.pgdg90+1) over (9.6.15-0+deb9u1) ...
Preparing to unpack .../3-postgresql-client-9.6_9.6.15-1.pgdg90+1_amd64.deb ...
Unpacking postgresql-client-9.6 (9.6.15-1.pgdg90+1) over (9.6.15-0+deb9u1) ...
Preparing to unpack .../4-postgresql-9.6_9.6.15-1.pgdg90+1_amd64.deb ...
Unpacking postgresql-9.6 (9.6.15-1.pgdg90+1) over (9.6.15-0+deb9u1) ...
Preparing to unpack .../5-postgresql-9.6-plr_1%3a8.4-1.pgdg90+1_amd64.deb ...
Unpacking postgresql-9.6-plr (1:8.4-1.pgdg90+1) over (1:8.3.0.17-1) ...
Preparing to unpack .../6-python-psycopg2_2.7.6.1-3.pgdg90+1_amd64.deb ...
Unpacking python-psycopg2 (2.7.6.1-3.pgdg90+1) over (2.6.2-1) ...
Setting up libpq5:amd64 (11.5-1.pgdg90+1) ...
Processing triggers for libc-bin (2.24-11+deb9u4) ...
Processing triggers for postgresql-common (181+deb9u2) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Processing triggers for man-db (2.7.6.1-2) ...
Setting up python-psycopg2 (2.7.6.1-3.pgdg90+1) ...
Setting up postgresql-client-9.6 (9.6.15-1.pgdg90+1) ...
Setting up libdbd-pg-perl (3.7.4-3~pgdg90+1) ...
Setting up postgresql-9.6 (9.6.15-1.pgdg90+1) ...
Setting up postgresql-9.6-plr (1:8.4-1.pgdg90+1) ...
Setting up postgresql-contrib-9.6 (9.6.15-1.pgdg90+1) ...

Just with this we have latest minor updates for our major version and we are now in a position able to update to newer major releases (10 or 11) of PostgreSQL in our stretch(or jessie) debian.