PostgreSQL

In a containerized deployment, the database is located on a Postgres container which is run from a Postgres image maintained by i2 Group on Docker Hub.

Running a Postgres container

A Postgres container uses the Postgres image. In the docker run command, you can use -e to pass environment variables to Postgres on the container. The environment variables are described in environment variables.

The container will run with a User ID and Group ID of 999. All files in mounted directories will be created with these IDs. If files are manipulated externally these IDs must be retained or the container will not function correctly.

For more information about the command, see docker run reference.

Docker run command

The following docker run command runs a Postgres container:

docker run -d \
   --name "postgres" \
   --network "eia" \
   --net-alias "postgres.eia" \
   -p "5432:5432" \
   -v "postgres_data:/var/lib/postgresql" \
   -v "postgres_sqlbackup:/backup" \
   -v "/environment-secrets/simulated-secret-store/postgres:/run/secrets/" \
   -v "i2a_data_server:/var/i2a-data" \
   -e POSTGRES_USER="postgres" \
   -e POSTGRES_PASSWORD="POSTGRES_PASSWORD" \
   -e SERVER_SSL=true \
   -e SSL_PRIVATE_KEY_FILE="/run/secrets/server.key" \
   -e SSL_CERTIFICATE_FILE="/run/secrets/server.cer" \
   "i2group/i2eng-postgres:4.4.4"

For an example of the docker run command, see utils/server_functions.sh script. The run_postgres_server does not take any arguments.

Storage

Named volumes are used to persist data and logs that are generated and used in the Postgres container, as well as a separate volume for backups, outside of the container.

Note: It is good practice to have a separate volume for the backup from the database storage.

To configure the Postgres container to use these volumes, specify the -v option with the name of the volume and the path where the directory is mounted in the container. By setting -v option in the docker run command, a named volume is created. For Postgres, the path to the directory that must be mounted is /var/lib/postgresql. For example:

-v postgres_data:/var/lib/postgresql
-v postgres_sqlbackup:/backup
-v /environment-secrets/simulated-secret-store/postgres:/run/secrets/

• Secrets:
  A directory that contains all of the secrets that this tool requires. Specifically this includes credentials to access the database and certificates used in SSL.
  The directory is mounted to /run/secrets inside the container. This can then be used by other environment variables such as SSL_PRIVATE_KEY_FILE to locate the secrets.
  In a production environment, the orchestration environment can provide the secrets to the file system or the secrets can be passed in via environment variables. The mechanism that is used here simulates the orchestration system providing the secrets as files. This is achieved by using a bind mount. In production this would not be required.

• Data:
  The Postgres container requires access to the data directory to run the ingestion scripts. To access the data, the data directory must be mounted into the container.

Environment variables

Environment Variable	Description
POSTGRES_USER	The administrator user's name
POSTGRES_PASSWORD	The administrator user's password.

The following environment variables enable you to use SSL:

Environment variable	Description
SERVER_SSL	See Secure Environment variables.
SSL_PRIVATE_KEY_FILE	See Secure Environment variables.
SSL_CERTIFICATE_FILE	See Secure Environment variables.

For more information about the SSL in Postgres, see Secure TCP/IP Connections with SSL.