I want to install the latest version of PostgreSQL on my server. The first thing to do is to backup my database from my current installation.

$ pg_dump -U username database_name > backup.sql

From here I can compile and install the latest version of PostgreSQL, then restore my previous data.

Setup

Let’s get a working directory

$ mkdir postgresql && cd postgresql

And install the necessary dependencies

$ sudo apt install build-essential libreadline6-dev zlib1g-dev libssl-dev libxml2-dev libxslt1-dev libossp-uuid-dev libsystemd-dev libproj-dev libpcre3-dev libjson-c-dev

PostgreSQL also needs a dedicated user account.

$ sudo adduser --system postgres

download postgresql

$ curl -O https://ftp.postgresql.org/pub/source/v10.0/postgresql-10.0.tar.bz2
$ curl -O https://ftp.postgresql.org/pub/source/v10.0/postgresql-10.0.tar.bz2.sha256
$ shasum -a 256 postgresql-10.0.tar.bz2
$ cat postgresql-10.0.tar.bz2.sha256 # check values match

download postgis

$ curl -O http://download.osgeo.org/postgis/source/postgis-2.4.0.tar.gz

download geos

geos is a dependency for PostGIS.

$ curl -O http://download.osgeo.org/geos/geos-3.6.2.tar.bz2

download gdal

gdal is another dependency for PostGIS.

$ curl -O http://download.osgeo.org/gdal/2.2.2/gdal-2.2.2.tar.xz
$ curl -O http://download.osgeo.org/gdal/2.2.2/gdal-2.2.2.tar.xz.md5
$ md5sum gdal-2.2.2.tar.xz
$ cat gdal-2.2.2.tar.xz.md5 # check values match

Installing

Now we can install PostgreSQL and its dependencies.

PostgreSQL

$ tar -xf postgresql-10.0.tar.bz2
$ cd postgresql-10.0
$ ./configure --with-openssl --with-systemd --with-uuid=ossp --with-libxml --with-libxslt --with-system-tzdata=/usr/share/zoneinfo
$ make
$ sudo make install
$ cd ..

GEOS

$ tar -xf geos-3.6.2.tar.bz2
$ cd geos-3.6.2
$ ./configure
$ make
$ sudo make install
$ cd ..

Gdal

$ tar -xf gdal-2.2.2.tar.xz
$ cd gdal-2.2.2/
$ ./configure --with-liblzma=yes --with-pg=/usr/local/pgsql/bin/pg_config
$ make
$ sudo make install
$ cd ..

PostGIS

$ tar -xf postgis-2.4.0.tar.gz
$ cd postgis-2.4.0
$ ./configure --disable-gtktest --with-pgconfig=/usr/local/pgsql/bin/pg_config
$ make
$ sudo make install
$ sudo ldconfig
$ cd ..

Configuring

The last line from above, sudo ldconfig, makes sure the .so files are linked to and loaded correctly, in particular the postgis .so files. Then when we try and get PostgreSQL to load a module, it’ll find the right file.

To initiate PostgreSQL change to the postgres user and initiate the db, we also need to set the right permissions on the folder used for the db:

$ sudo mkdir /usr/local/pgsql/data
$ sudo chown -R postgres /usr/local/pgsql/data
$ sudo chmod 700 /usr/local/pgsql/data
$ sudo su postgres
postgres@hostname:/home/user/postgresql$ /usr/local/pgsql/bin/initdb -E UTF8 -D /usr/local/pgsql/data

Startup

Let’s use systemd to start and stop PostgreSQL. Create a new service file at /etc/systemd/system/postgresql.service:

[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking
TimeoutSec=120
User=postgres

ExecStart= /usr/local/pgsql/bin/pg_ctl -s -D /usr/local/pgsql/data start -w -t 120
ExecReload=/usr/local/pgsql/bin/pg_ctl -s -D /usr/local/pgsql/data reload
ExecStop=  /usr/local/pgsql/bin/pg_ctl -s -D /usr/local/pgsql/data stop -m fast

[Install]
WantedBy=multi-user.target

Then start and enable the PostgreSQL server:

$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql

Now we setup a new database as the postgres user using psql:

$ sudo su postgres
postgres@hostname:/home/user/postgresql$ /usr/local/psql/bin/psql
postgres=# CREATE ROLE username WITH LOGIN PASSWORD 'password';
postgres=# CREATE DATABASE database_name WITH OWNER username;
postgres=# \q

Then, whilst we are still the postgres user load the backup file:

postgres@hostname:/home/user/postgresql$ /usr/local/pgsql/bin/psql -d database_name --set ON_ERROR_STOP=on -f backup.sql

Further Updates

When a minor version it released, e.g. from 9.6.2 -> 9.6.3, updating is very easy. Compile the new source code, stop postgresql.service, and run the sudo make install command. Then you can simply start the postgresql.service again, as the data structure will remain compatible.

Major updated are more involved. We need to dump our database as at the start of this article. Then move the /usr/local/pgsql folder to a backup location. Then install as the article describes.