Spry_Sparrow

PostgreSQL 11.0 PostGIS 2.5.0 GEOS 3.7, GDAL 2.3.2, PROJ4 5.2.0

Contents

• How to use
• Versions
• Scripts
• Executing Arbitrary Commands
• Data Persistence
• Passwords
• Configuring the Data Store
• Killing the Container

How to use

Using Docker compose

docker-compose.yml:

version: "3"
services:
  postgis:
    image: geographica/postgis:spry_sparrow
    ports:
      - "5432:5432"
    volumes:
      - db-data:/data
    environment:
      - POSTGRES_PASSWD=postgres
volumes:
  db-data:

Run:

docker-compose up

Without compose

docker run --name postgis -p 5432:5432 geographica/postgis:spry_sparrow

Environment variables

This will create a container with a default volume, /data, for storing the data store. The default encoding will be UTF-8, and the locale en_US. No additional modification or action is taken.

Containers can be configured by means of setting environmental variables:

• POSTGRES_PASSWD: set the password for user postgres. See Passwords for more details. Defaults to postgres;

• ENCODING: encoding to create the data store and the default database, if applicable. Defaults to UTF-8;

• LOCALE: locale for the data store and the default database, if any. Defaults to en_US;

• PG_HBA: configuration of pg_hba.con access file. See [Configuring the Data Store](#Configuring the Data Store) for details;

• PG_CONF: configuration of postgresql.conf See [Configuring the Data Store](#Configuring the Data Store) for details.

Versions

This Dockerfile compiles the following software:

• PostgreSQL 11.0

• GEOS 3.7.0

• Proj 5.2.0

• GDAL 2.3.2

• PostGIS 2.5.0

Scripts

There is a script in this repo to help working with this image. psql-docker opens a psql console on a standalone container to connect to other databases. To check how it works:

psql-docker -h

Executing Arbitrary Commands

The image can run arbitrary commands. This is useful for example for creating a temporary container for just dump a database, run a psql session with the one inside this image, or executing scripts into another container.

Some examples:

# Interactive pg_dump, will ask for password

docker run --rm -ti -v /whatever/:/d --link the_container_running_the_database:pg \
geographica/postgis:spry_sparrow \
pg_dump -b -E UTF8 -f /d/dump -F c -v -Z 9 -h pg -p 5432 -U postgres project

# Full automatic pg_dump, with password as ENV variable

docker run --rm -v /home/malkab/Desktop/:/d --link test_07:pg \
geographica/postgis:spry_sparrow \
PGPASSWORD="new_password_here" pg_dump -b -E UTF8 -f /d/dump33 -F c \
-v -Z 9 -h pg -p 5432 -U postgres postgres

# Interactive psql

docker run --rm -ti -v /home/malkab/Desktop/:/d --link test_07:pg \ geographica/postgis:spry_sparrow \ PGPASSWORD="new_password_here" psql -h pg -p 5432 -U postgres postgres

Data Persistence

Datastore data can be persisted in a data volume or host mounted folder and be used later by another container. The container checks if /data/ is empty or not. If not, considers the datastore to be not created and creates an empty one.

Passwords

Passwords sent to the container with environment variable POSTGRES_PASSWD can be passed either on plain text or already encrypted á la PostgreSQL. To pass it on plain text means that anybody with access to the docker inspect command on the server will be able to read passwords. Encrypting them previously means that docker inspect will show the encrypted password, adding an additional layer of secrecy.

PostgreSQL passwords are encrypted using the MD5 checksum algorithm on the following literal:

md5 + md5hash(real password + username)

For example, in the case of user myself and password secret, the encrypted password will be the MD5 sum of secretmyself prefixed with md5, in this case, md5a296d28d6121e7307ac8e72635ae206b.

To provide encrypted password to containers, use the following command:

export USER="projectuser" && \
export USERPASSWD="md5"$(printf '%s' "userpass" ${USER} | md5sum | cut -d ' ' -f 1) && \
export PGPASSWD="md5"$(printf '%s' "password_here" "postgres" | md5sum | cut -d ' ' -f 1) && \
docker run -d -P --name ageworkshoptestpg -e "POSTGRES_PASSWD=${PGPASSWD}" \
-e "CREATE_USER=${USER}" -e "CREATE_USER_PASSWD=${USERPASSWD}" \
geographica/postgis:spry_sparrow

Ugly, but effective. Keep in mind, however, that if you use provisioning methods like bash scripts or Docker Compose others will still be able to read passwords from these sources, so keep them safe.

Configuring the Data Store

The image allows for configuration of pg_hba.conf and postgresql.conf data store files at creation time and later. This is advanced stuff, refer to the PostgreSQL documentation for details.

pg_hba.conf configuration is handled by a script called pg_hba_conf. pg_hba_conf has three modes of operation:

[1] pg_hba_conf l

[2] pg_hba_conf a "line 1#line 2#...#line n"

[3] pg_hba_conf d "line 1#line 2#...#line n"

which means:

• [1] prints current contents of pg_hba.conf;

• [2] adds lines to pg_hba.conf;

• [3] deletes lines from pg_hba.conf.

This commands can be issued by standard Docker's exec:

docker exec -ti whatevercontainer pg_hba_conf a \
"host all all 23.123.22.1/32 trust#host all all 93.32.12.3/32 md5"

but at startup it is controlled by an environment variable, PG_HBA, which defaults to:

ENV PG_HBA "local all all trust#host all all 127.0.0.1/32 trust#host all all 0.0.0.0/0 md5#host all all ::1/128 trust"

This defaults should be submitted for basic operation. For universal access, for example for testing, add:

local all all trust#host all all 0.0.0.0/0 trust#host all all 127.0.0.1/32 trust#host all all ::1/128 trust

Modify this variable to configure at creation time. Keep in mind, however, that any value provided to this variable will supersede the default. Don't forget to include basic access permissions if you modify this variable, or the server will be hardly reachable. For testing purposes, direct commands can be issued via exec.

Configuration of postgresql.conf follows an identical procedure. Command is postgresql_conf and has the same syntax as pg_hba_conf. The environmental variable is PG_CONF, which defaults to the following configuration:

max_connections=100#listen_addresses='*'#shared_buffers=128MB#dynamic_shared_memory_type=posix#log_timezone='UTC'#datestyle='iso, mdy'#timezone='UTC'#lc_messages='en_US.UTF-8'#lc_monetary='en_US.UTF-8'#lc_numeric='en_US.UTF-8'#lc_time='en_US.UTF-8'#log_statement='all'#log_directory='pg_log'#log_filename='postgresql-%Y-%m-%d_%H%M%S.log'#logging_collector=on#client_min_messages=notice#log_min_messages=notice#log_line_prefix='%a %u %d %r %h %m %i %e'#log_destination='stderr,csvlog'#log_rotation_size=500MB

At creation time, language, encoding, and locale info is added based on env variables LOCALE and ENCODING.

Logs are stored at $POSTGRES_DATA_FOLDER/pg_log.

Killing the Container

This container will handle signals send to it with docker kill properly, so the database is shut down tidily. Thus:

• SIGTERM signals for a smart shutdown, waiting for all connections and transactions to be finished. The server won't allow for new connections, thou:

pg_ctl -D . stop -m smart

docker kill -s SIGTERM containername

• SIGINT signals for fast shutdown. The server will abort current transactions and disconnect users, but will exit nicely otherwise;

pg_ctl -D . stop -m fast

docker kill -s SIGINT containername

• SIGQUIT signals for immediate shutdown. This will leave the database in a improper state and lead to recovery on next startup:

pg_ctl -D . stop -m immediate

docker kill -s SIGQUIT containername