PostgreSQL Client

A Postgres Client container is an ephemeral container that is used to run the psql commands to create and configure the database.

Building an Postgres Client image

The Postgres Client is built on top of the Postgres image maintained by i2 Group on Docker Hub.

The Postgres Client image is built from the Dockerfile in images/postgres_client.

Docker build command

The following docker build command builds the Postgres Client image:

docker build -t "postgres_client_image:4.4.4" images/postgres_client

Running a Postgres Client container

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

The container will run with a default User ID and Group ID of 999 (this can be changed with the USER_ID and GROUP_ID env variables). 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 Client container:

docker run \
    --rm \
    --network "eia" \
    -v "/home/<user-name>/analyze-deployment-tooling/pre-reqs/i2analyze/toolkit:/opt/toolkit" \
    -v "/home/<user-name>/analyze-deployment-tooling/examples/pre-prod/database-scripts/generated:/opt/databaseScripts/generated" \
    -v "/home/<user-name>/analyze-deployment-tooling/examples/pre-prod/configuration/database-scripts:/opt/customDatabaseScripts" \
    -e USER_ID="$(id -u)" -e GROUP_ID="$(id -g)" \
    -e SQLCMD="/usr/lib/postgresql/bin/psql" \
    -e SQLCMD_FLAGS="-w -X -q --set=client_min_messages=warning" \
    -e DB_SERVER="postgres.eia" \
    -e DB_PORT=5432 \
    -e DB_NAME="ISTORE" \
    -e GENERATED_DIR="/opt/databaseScripts/generated" \
    -e PGUSER="dba" \
    -e PGPASSWORD="DBA_PASSWORD" \
    -e DB_SSL_CONNECTION=true \
    -e SSL_CA_CERTIFICATE="/run/secrets/CA.cer" \
    -e SSL_ADDITIONAL_TRUST_CERTIFICATES="/run/secrets/additional_trust_certificates.cer" \
    "postgres_client_image:4.4.4" "$@"

The local user ID is required so that a user is created in the Docker container with a USER_ID that is the same as the local user. The user is required to ensure that the local user can access any files that are generated on the container. The value of $id comes from your shell.

For an example of the docker run command, see run_postgres_server_command_as_etl function in client_functions.sh script. For an example of how to use run_postgres_server_command_as_etl function, see run_postgres_server_command_as_etl.

Note: you can run Postgres Client container as different users, see run_postgres_server_command_as_dba, run_postgres_server_command_as_postgres

Bind mounts

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_CA_CERTIFICATE_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.
Toolkit:
For the Postgres Client to use the tools in /opt/toolkit/i2-tools/scripts, the toolkit must be mounted into the container.
In the example scripts, this is defaulted to /opt/toolkit.
Generated scripts directory:
Some of the i2 Analyze tools generate scripts to be run against the Information Store database. For the Postgres Client to run these scripts, the directory where they are generated must be mounted into the container.
In the example scripts, this is defaulted to database-scripts/generated. The GENERATED_DIR environment variable must specify the location where the generated scripts are mounted.
Custom scripts directory:
You can create custom database scripts. For the Postgres Client to run these scripts, the directory where they are created must be mounted into the container.
In the example scripts, this is defaulted to configuration/database-scripts.

Environment variables

Environment Variable	Description
`DB_SERVER`	The fully qualified domain name of the database server.
`DB_PORT`	Specifies the port number to connect to the Information Store.
`DB_NAME`	The name of the Information Store database.
`PGUSER`	The user.
`PGPASSWORD`	The password.
`GENERATED_DIR`	The root location where any generated scripts are created.

The following environment variables enable you use SSL

Environment variable	Description
`DB_SSL_CONNECTION`	See Secure Environment variables.
`SSL_CA_CERTIFICATE`	See Secure Environment variables.
`SSL_ADDITIONAL_TRUST_CERTIFICATES`	See Secure Environment variables.

Command parsing

When commands are passed to the Postgres client by using the "$@" notation, the command that is passed to the container must be escaped correctly. On the container, the command is run using docker exec "$@". Because the command is passed to the docker run command using bash -c, the command must be maintained as a double quoted string.

For example:

    run_postgres_server_command_as_etl 
    bash -c 
    "/usr/lib/postgresql/bin/psql -w -X -q --set=client_min_messages=warning -h \${DB_SERVER} -p ${DB_PORT} -d \${DB_NAME} 
    -c \"COPY ${STAGING_SCHEMA}.${table_name} ($(
      IFS=','
      echo "${csv_header[*]}"
    )) FROM '/var/i2a-data/${BASE_DATA}/${csv_and_format_file_name}.csv' CSV HEADER ENCODING 'UTF8' NULL AS ''\""

Different parts of the command must be escaped in different ways:

\${DB_SERVER} and \${DB_NAME}
Because the command uses the container's local environment variables to obtain the values of these variables, the $ is escaped by a \.
${DB_PORT} is not escaped because this is an environment variable available to the script calling the client function.
\"COPY ${STAGING_SCHEMA}.${table_name} ... CSV HEADER ENCODING 'UTF8' NULL AS ''\"
The string value for the -c argument must be surrounded by " when it is run on the container. The surrounding " are escaped with \. The variables that are not escaped in the string are evaluated outside of the container when the function is called.