Skip to content

PostgreSQL

PostgreSQL is our default SQL style database used in various projects. Versions can be found in the server-config project.

It is recommended to run the database in a container. That way it is easier handling upgrades and data drop. However, running as a standalone installation or even on another (networked) machine is entirely possible.

Docker

Make sure the docker environment is installed.

Quick start

From a commandline run the following:

docker run -d -p 5432:5432 --name postgresql -e POSTGRESQL_USERNAME=postgres -e POSTGRESQL_PASSWORD=postgres bitnami/postgresql:<version>

This will pull and start a container which can be connected to. Scripts in various projects assume that the user is postgres and the password is postgres.

For persisting volumes, check Persisting Volumes

Option Description
-d Run the container in detached mode
-p Port mappings, PostgreSQL is most commonly running on port 5432
-- name Optional, Name of the Docker container
-- restart Optional, restart the container at e.g. reboot
POSTGRESQL_USERNAME Default user
POSTGRESQL_PASSWORD Default password
POSTGRESQL_DATABASE Optional Default database
\<version> Replace \<version> with applicable. E.g. 11.18.0

For more options, checkout docker documentation and image options.

Persisting volumes

Check the Docker documentation on how to create and use named docker volumes

Adding the -v will volume mount the docker volume to the container. This way we can quickly move data around if for example we need to share test data with someone else.

Assuming that a docker volume postgresql-data has been created with docker volume create postgresql-data add the following to the docker command to have a persistent volume: -v postgresql-data:/var/lib/postgresql/data

Docker-Compose

If docker-compose is installed, it is also possible to create a compose.yml and start the container with that. Create the compose file with options as described above, and docker compose up the container. Use -d to run the container detached. This compose file will add a named volume as well. Replace the tag with the current version we use.

compose.yml

services:
  postgres:
    image: bitnami/postgresql:<version>
    container_name: postgresql
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - 5432:5432
    volumes:
      - postgresql-data:/var/lib/postgresql/data
volumes:
  postgresql-data:

Clearing data

Multiple options exist.

  1. Stop and remove the container (and data). Re-run afterwards.
    docker stop postgresql
    docker rm -v postgresql
    Rerun docker as described in Quickstart
  2. Connect to the database server and clear data.
  3. Remove the data in the data folder.
  4. Execute script that clears data.

Stand-alone installation

If one would rather have the database installed locally this is of course also possible. Download PostgreSQL for your operating system.

Install PostgreSQL with default settings, and note the password ( recommended: postgres for compatibility with scripts).\ When installed, the database will run in the background ready to accept new connections as requested.

Connecting to a database

There are multiple options for connecting to a database, but the default tool used is pgAdmin, which has everything we need when running a containerized database.

pgAdmin

pgAdmin can be installed by going to their download page, choosing your operating system, and following the instructions.

To connect to a database, click on the following in the pgAdmin menubar: Object -> Register -> Server..., which opens the Register Server window.

The following settings should be used to connect to a database:

  • General
    • name: A name to save the database as in pgAdmin.
  • Connection

If connecting to a production database, additional SSH tunneling settings need to be configured: