From Wikipedia:
PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.
PostgreSQL can run on Clipper via Apptainer.
Obtaining the PostgreSQL Container
Official PostgreSQL container images are available from DockerHub. Please note, as of February 2025, the container image tagged latest is around 160MB in size. Images should be stored in either your home or project folders.
Load the Apptainer module:
module load apptainer
Download the latest PostgreSQL container to the current working directory and convert it to Singularity Image Format, which is used by Apptainer. This command will overwrite an existing container if it exists.
apptainer pull --force postgres.sif docker://postgres:latest
Running PostgreSQL via Slurm Job
First, determine where the database files will be stored on the file system. An appropriate location for database files would be the /mnt/projects/your_project file system.
Create a Slurm script, for example postgresql.sbatch:
#!/bin/bash
#SBATCH --job-name=postgresql-server
#SBATCH --partition=cpu # or bigmem
#SBATCH --nodes=1
#SBATCH --ntasks-per-node=1
#SBATCH --cpus-per-task=2 # number of cpus to use, should be sufficient for most databases
#SBATCH --mem=8G # amount of ram to request, should be sufficient for most databases
#SBATCH --time=5-00:00:00 # time to run, max is five days shown here
#SBATCH --output=postgresql-server.out # standard output location
#SBATCH --error=postgresql-server.err # standard error location
#SBATCH --dependency=singleton # two jobs with same name cannot run (don't run two instances of the same postgresql server)
#SBATCH --signal=B:SIGINT@120 # two minutes before job ends, send the equivalent of Ctrl-C to postgresql so it can cleanly shutdown
# Modify this variable to the location of your scratch folder
export POSTGRES_HOME=/mnt/projects/your_project/hpcuser1_scratch
# Name of the database
export POSTGRES_DB=mydb
# Create the necessary folder structure and generate random password if not already created
mkdir -p $POSTGRES_HOME/{config,db/data,run}
[ ! -f "$POSTGRES_HOME/config/postgres-password" ] && uuidgen > $POSTGRES_HOME/config/postgres-password
chmod 600 $POSTGRES_HOME/config/postgres-password
# Configure necessary PostgreSQL variables
export POSTGRES_PASSWORD_FILE=$POSTGRES_HOME/config/postgres-password
export POSTGRES_USER=$USER
export PGDATA=$POSTGRES_HOME/db/data
export POSTGRES_HOST_AUTH_METHOD=md5
export POSTGRES_INITDB_ARGS="--data-checksums"
export POSTGRES_PORT=$(shuf -i 10000-30000 -n 1) # select a random port to run on
echo ""
echo "----------------------------------------------------------------------------------------"
echo ""
echo " PostgreSQL Server Connection Details:"
echo ""
echo " Server: $(hostname)"
echo " Port: $POSTGRES_PORT"
echo " Database: $POSTGRES_DB"
echo " Username: $USER"
echo " Password: Located in $POSTGRES_HOME/config/postgres-password"
echo ""
echo " This job will end at $(squeue --noheader -j $SLURM_JOBID -o %e)."
echo " PostgreSQL server will gracefully terminate two minutes before the end of the job."
echo ""
echo " To connect to this instance from outside of Clipper using SSH port forwarding:"
echo ""
echo " ssh -t -t $USER@clipper.gvsu.edu -L 5432:localhost:$POSTGRES_PORT ssh $(hostname) -L $POSTGRES_PORT:localhost:$POSTGRES_PORT"
echo ""
echo " This command will forward all requests on port 5432 from your local machine"
echo " to the PostgreSQL server instance running on $(hostname)."
echo " Please note this command will change for every submission of this job."
echo " Check this output for the correct ports to forward each time this job is submitted."
echo ""
echo "----------------------------------------------------------------------------------------"
echo ""
# load apptainer
module load apptainer
# run postgresql via apptainer
# note that this assumes the postgres.sif image is in the same directory as the sbatch script
apptainer run -B $POSTGRES_HOME/db:/var/lib/postgresql -B $POSTGRES_HOME/run:/var/run/postgresql postgres.sif -c "port=$POSTGRES_PORT"
Submit the job:
sbatch postgresql.sbatch
Connection details will be located at the top of the postgresql-server.out file. If a database with the selected name (mydb in the example) does not exist, PostgreSQL will create it on first run. Subsequent runs will use the existing database.
Follow security best practices when handling the database credentials.
----------------------------------------------------------------------------------------
PostgreSQL Server Connection Details:
Server: c001.clipper.gvsu.edu
Port: 21665
Database: mydb
Username: hpcuser1
Password: Located in /mnt/scratch/hpcuser1_scratch/config/postgres-password
This job will end at 2025-02-19T07:22:01.
PostgreSQL server will gracefully terminate two minutes before the end of the job.
To connect to this instance from outside of Clipper using SSH port forwarding:
ssh -t -t hpcuser1@clipper.gvsu.edu -L 5432:localhost:21665 ssh c001.clipper.gvsu.edu -L 21665:localhost:21665
This command will forward all requests on port 5432 from your local machine
to the PostgreSQL server instance running on c001.clipper.gvsu.edu.
Please note this command will change for every submission of this job.
Check this output for the correct ports to forward each time this job is submitted.
----------------------------------------------------------------------------------------
Connecting to PostgreSQL using an external client
The job output provides a command to forward port 5432 on your local machine to the PostgreSQL server instance running on Clipper.
Once logged into Clipper with SSH port forwarding enabled, it is possible to connect to the PostgreSQL server using clients such as DBeaver.
Use the credentials (username and password) provided by the job script. When using the provided SSH port forwarding command, the database host will be localhost and the port will be 5432.