PostgreSQL+PostGIS installation using Singularity on Stampede2

Steps to install PostgreSQL+PostGIS on Stampede2 at TACC

Installation of a containerized instance of PostgreSQL in a high performance computing environment (Stampede2)

Motivation

Containerized PostgreSQL is a useful step to create, manage, and access PostgreSQL databases directly on an HPC environment.

Containerizing the database software enables use by HPC web-portals such as:

Limitations

This has been tested on Stampede2 supercomputer at @TACC
This can only be run on allocated computational nodes. On Stampede2 the max allocated time available is 5 days

Prerequisite software

Docker or Singularity
Shell environment, eg bash, zsh, etc

Installation Method

PostgreSQL setup requirements

If on a login node, then enter a computational node by executing the following command:

idev

The following environment variables may need to be set before continuing

export POSTGRES_PASS=password
export POSTGRES_ADDR=127.0.0.1:5432
export POSTGRES_USER=postgres
export POSTGRES_DB=postgres

To verify if the environment variables are correctly set, the following command can be used

env | grep POSTGRES*

Commands to run PostgreSQL at TACC

module load tacc-singularity

Use the following commands to pull & inititiate a PostgreSQL database instance

singularity pull docker://postgres  ## singularity pull has to be done only once
SINGULARITYENV_POSTGRES_PASSWORD=pgpass SINGULARITYENV_PGDATA=$SCRATCH/pgdata singularity run --cleanenv --bind $SCRATCH:/var postgres_latest.sif &

OR use the following commands to pull & initiate a PostgreSQL+PostGIS database instance

singularity pull docker://postgis/postgis  ## singularity pull has to be done only once
SINGULARITYENV_POSTGRES_PASSWORD=pgpass SINGULARITYENV_PGDATA=$SCRATCH/pgdata singularity run --cleanenv --bind $SCRATCH:/var postgis_latest.sif &

You should see the following message

LOG:  database system is ready to accept connections

Press enter to get back the command prompt and then run the following command to connect to the PostgreSQL database instance

SINGULARITYENV_POSTGRES_PASSWORD=pgpass SINGULARITYENV_PGDATA=$SCRATCH/pgdata singularity exec --cleanenv --bind $SCRATCH:/var postgres_latest.sif psql -U postgres -d postgres -h 127.0.0.1

(Note: There is an existing version of psql on Stampede2, we suggest using psql from the Singularity image like shown above.)

Note : Instead of SCRATCH/pgdata you can use any database on stampede2 that you have access to.

Testing environment

1) intel/18.0.2      3) impi/18.0.2   5) autotools/1.1    7) cmake/3.16.1   9) TACC
2) libfabric/1.7.0   4) git/2.24.1    6) python2/2.7.15   8) xalt/2.10.2

Alternatives

/bin/psql is available on Stampede2 without relying on Singularity/Docker. We have not tested here strictly using the pre-installed PostgreSQL software to initiate a database instance.

This installation method was inspired by sshh12/venmo-research

mquazi/PostgreSQL-Singularity