Postgres/PostGIS in Docker for production

Resources

Dockerfile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
FROM postgres:11

ENV POSTGISV 2.5
ENV TZ America/New_York

# add addressing dictionary
RUN mkdir -p /opt/apps
COPY ./pgsql-address-dictionary.zip /opt/apps/pgsql-address-dictionary.zip

RUN apt-get update \
  && apt-get install -y --no-install-recommends \
  postgresql-$PG_MAJOR-postgis-$POSTGISV \
  postgresql-$PG_MAJOR-postgis-$POSTGISV-scripts \
  postgresql-$PG_MAJOR-pgrouting \
  postgresql-$PG_MAJOR-pgrouting-scripts \
  postgresql-server-dev-$PG_MAJOR \
  unzip \
  make \
  && cd /opt/apps \
  && unzip pgsql-address-dictionary.zip \
  && cd pgsql-addressing-dictionary-master \
  && make install \
  && apt-get purge -y --auto-remove postgresql-server-dev-$PG_MAJOR make unzip

# set time zone
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

# add bakcup job
RUN mkdir -p /opt/backups
COPY ./pgbackup.sh /opt/pgbackup.sh
RUN chmod +x /opt/pgbackup.sh

# add init script
RUN mkdir -p /docker-entrypoint-initdb.d
COPY ./initdb-postgis.sh /docker-entrypoint-initdb.d/postgis.sh

# create volume for backups
VOLUME ["/opt/backups"]

docker-compose.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
version: '2'
volumes:
  postgres11-postgis2.5:
services:
  postgres:
    build:
      context: .
      dockerfile: Dockerfile
    image: postgres11-postgis2.5
    container_name: postgres11-postgis2.5
    volumes:
      - postgres11-postgis2.5:/var/lib/postgresql/data
      - ./backups:/opt/backups
    networks:
      - postgres_conn
    ports:
      - '5432:5432'
    env_file: .env

networks:
  postgres_conn:
    driver: bridge

initdb-postgis.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#!/bin/sh

set -e

# Perform all actions as $POSTGRES_USER
export PGUSER="$POSTGRES_USER"

# https://pgtune.leopard.in.ua/
# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 4 GB
# CPUs num: 2
# Connections num: 150
# Data Storage: ssd
psql -c "ALTER SYSTEM SET max_connections = '150';"
psql -c "ALTER SYSTEM SET shared_buffers = '1GB';"
psql -c "ALTER SYSTEM SET effective_cache_size = '3GB';"
psql -c "ALTER SYSTEM SET maintenance_work_mem = '256MB';"
psql -c "ALTER SYSTEM SET checkpoint_completion_target = '0.7';"
psql -c "ALTER SYSTEM SET wal_buffers = '16MB';"
psql -c "ALTER SYSTEM SET default_statistics_target = '100';"
psql -c "ALTER SYSTEM SET random_page_cost = '1.1';"
psql -c "ALTER SYSTEM SET effective_io_concurrency = '200';"
psql -c "ALTER SYSTEM SET work_mem = '6990kB';"
psql -c "ALTER SYSTEM SET min_wal_size = '1GB';"
psql -c "ALTER SYSTEM SET max_wal_size = '2GB';"
psql -c "ALTER SYSTEM SET max_worker_processes = '2';"
psql -c "ALTER SYSTEM SET max_parallel_workers_per_gather = '1';"
psql -c "ALTER SYSTEM SET max_parallel_workers = '2';"

# add postgrereader user
psql -c "CREATE USER sampleuser WITH PASSWORD 'samplepassword';"

# create databases
psql -c "CREATE DATABASE gis;"

# add extensions to databases
psql gis -c "CREATE EXTENSION IF NOT EXISTS postgis;"
psql gis -c "CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;"
psql gis -c "CREATE EXTENSION IF NOT EXISTS addressing_dictionary;"

# restore database if dump file exists
if [ -f /opt/backups/restore.dump ]; then
  echo "Restoring backup..."
  pg_restore -d gis --clean --if-exists /opt/backups/restore.dump
fi

pgbackup.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
#!/bin/bash

set -e

# dump databases
for DATABASE in `psql -At -U postgres -c "select datname from pg_database where not datistemplate order by datname;" postgres`
do
  echo "Plain backup of $DATABASE"
  pg_dump -U postgres -Fc "$DATABASE" > /opt/backups/"$DATABASE".$(date -d "today" +"%Y-%m-%d-%H-%M").dump
done

# delete files older than 7 days
find /opt/backups -mtime +7 -type f -delete

.env

1
POSTGRES_PASSWORD=supersecretpassword

Cron jobs

1
2
@reboot sleep 10 && /usr/bin/docker-compose -f /home/tobin/workspace/code/docker/postgis/postgres11-postgis2.5/docker-compose.yml up -d
@daily docker exec postgres11-postgis2.5 /opt/pgbackup.sh