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
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
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.
