EEA & Eionet documentation hub

Browse documentation for IT-systems used by the European Environment Agency and the Eionet network.

PostgreSQL with replication support (ZODB RelStorage ready)

Docker image for PostgreSQL with replication support and ZODB RelStorage ready

This image is generic, thus you can obviously re-use it within your non- related EEA projects.

Base docker image

Source code

Changelog

Installation

  1. Install Docker.

  2. Install Docker Compose.

Simple usage

$ docker run --name=pg1 \
             -e POSTGRES_USER=postgres \
             -e POSTGRES_PASSWORD=postgres \
             -e POSTGRES_DBNAME=datafs zasync \
             -e POSTGRES_DBUSER=zope \
             -e POSTGRES_DBPASS=zope \
         eeacms/postgres

Or using docker-compose:

postgres:
  image: eeacms/postgres
  ports:
  - "5432:5432"
  environment:
    POSTGRES_USER: postgres
    POSTGRES_PASSWORD: postgres
    POSTGRES_DBNAME: datafs zasync
    POSTGRES_DBUSER: zope
    POSTGRES_DBPASS: zope
  volumes:
  - postgres_data:/var/lib/postgresql/data

PostgreSQL replication

Start master node:

$ docker run --name=master \
             -e POSTGRES_USER=postgres \
             -e POSTGRES_PASSWORD=postgres \
             -e POSTGRES_DBNAME=datafs zasync \
             -e POSTGRES_DBUSER=zope \
             -e POSTGRES_DBPASS=zope \
             -e POSTGRES_CONFIG_wal_level=hot_standby \
             -e POSTGRES_CONFIG_max_wal_senders=8 \
             -e POSTGRES_CONFIG_wal_keep_segments=8 \
             -e POSTGRES_CONFIG_hot_standby=on \
        eeacms/postgres

Start replica:

$ docker run --name=replica1 \
             --link=master \
             -e POSTGRES_REPLICATE_FROM=master \
             -e POSTGRES_USER=postgres \
             -e POSTGRES_PASSWORD=postgres \
             -e POSTGRES_CONFIG_wal_level=hot_standby \
             -e POSTGRES_CONFIG_max_wal_senders=8 \
             -e POSTGRES_CONFIG_wal_keep_segments=8 \
             -e POSTGRES_CONFIG_hot_standby=on \
    eeacms/postgres

or using docker-compose:

master:
  image: eeacms/postgres
  environment:
  - POSTGRES_USER=postgres
  - POSTGRES_PASSWORD=postgres
  - POSTGRES_DBNAME=datafs zasync
  - POSTGRES_DBUSER=zope
  - POSTGRES_DBPASS=zope
  - POSTGRES_CONFIG_wal_level=hot_standby
  - POSTGRES_CONFIG_max_wal_senders=8
  - POSTGRES_CONFIG_wal_keep_segments=8
  - POSTGRES_CONFIG_hot_standby=on
  volumes:
  - master_data:/var/lib/postgresql/data

replica:
  image: eeacms/postgres
  tty: true
  stdin_open: true
  links:
  - master
  environment:
  - POSTGRES_USER=postgres
  - POSTGRES_PASSWORD=postgres
  - POSTGRES_CONFIG_wal_level=hot_standby
  - POSTGRES_CONFIG_max_wal_senders=8
  - POSTGRES_CONFIG_wal_keep_segments=8
  - POSTGRES_CONFIG_hot_standby=on
  - POSTGRES_REPLICATE_FROM=master
  volumes:
  - replica_data:/var/lib/postgresql/data

Customize your deployment by changing environment variables. See Supported environment variables section bellow.

You may want to restore existing PostgreSQL database, within data container. See section Restore existing database

Persistent data as you wish

The PostgreSQL database is kept in a data-only container named data. The data container keeps the persistent data for a production environment and must be backed up.

So if you are running in a development environment, you can skip the backup and delete the container if you want.

On a production environment you would probably want to backup the container at regular intervals. The data container can also be easily copied, moved and be reused between different environments.

Restore existing database

Extract PostgreSQL database from the container

$ cd eea.docker.postgres
$ docker-compose up -d
$ docker exec -it eeadockerpostgres_postgres_1 \
  bash -c "gosu postgres pg_dump datafs | gzip > /postgresql.backup/datafs.gz"
$ ls /var/lib/docker/volumes/www-postgres-dump/_data

Restore PostgreSQL database from backup

WARNING: NEVER do this directly on PRODUCTION. This will DROP your existing database

$ cd eea.docker.postgres
$ docker-compose up -d
$ docker cp datafs.gz eeadockerpostgres_postgres_1:/postgresql.backup/
$ docker exec eeadockerpostgres_postgres_1 \
         gosu postgres /postgresql.restore/database-restore.sh datafs

Supported environment variables

  • POSTGRES_USER This optional environment variable is used in conjunction with POSTGRES_PASSWORD to set a user and its password. This variable will create the specified user with superuser power and a database with the same name. If it is not specified, then the default user of postgres will be used.
  • POSTGRES_PASSWORD This environment variable is recommend for you to use the PostgreSQL image. This environment variable sets the superuser password for PostgreSQL. The default superuser is defined by the POSTGRES_USER environment variable. Default postgres
  • POSTGRES_DBNAME Create multiple databases (space separated) within PostgreSQL with POSTGRES_DBUSER as owner. E.g. POSTGRES_DBNAME=datafs zasync
  • POSTGRES_DBUSER Owner for POSTGRES_DBNAME
  • POSTGRES_DBPASS Password for POSTGRES_DBUSER
  • POSTGRES_REPLICATE_FROM Start a PostgreSQL replica of the given master

You can also override postgres configuration via environment variables by using POSTGRES_CONFIG_ prefix. Example:

POSTGRES_CONFIG_MAX_CONNECTIONS=200
POSTGRES_CONFIG_SHARED_BUFFERS=4GB

Also you can configure recovery.conf by using RECOVERY_CONFIG_ environment variables.

See PostgreSQL Documentation for supported parameters. You should also check Tuning Your PostgreSQL Server

In the same way you can define maintenance cron jobs by using POSTGRES_CRON_ prefix. Example to backup nighly datafs database at 3 AM:

POSTGRES_CRON_1=0 3 * * * postgres /postgresql.restore/database-backup.sh datafs

or restore Staging DB daily at 5 AM:

POSTGRES_CRON_2=0 5 * * * postgres /postgresql.restore/database-restore.sh datafs

The Initial Owner of the Original Code is European Environment Agency (EEA). All Rights Reserved.

The Original Code is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

Funding

European Environment Agency (EU)