PostDock - Postgres + Docker

PostgreSQL cluster with High Availability and Self Healing features for any cloud and docker environment (Amazon, Google Cloud, Kubernetes, Docker Compose, Docker Swarm, Apache Mesos)

• Info
  • Features
  • What's in the box
  • Docker images tags convention
    • Repositories
• Start cluster with docker-compose
• Start cluster in Kubernetes
• Adaptive mode
• SSH access
• Replication slots
• Configuring the cluster
  • Postgres
  • Pgpool
  • Barman
  • Other configurations
• Extended version of postgres
• Backups and recovery
• Health-checks
• Useful commands
• Publications
• Scenarios
• How to contribute
• FAQ
• Documentation and manuals

---

Info

Features

• High Availability
• Self Healing and Automated Reconstruction
• Split Brain Tolerance
• Eventually/Partially Strict/Strict Consistency modes
• Reads Load Balancing and Connection Pool
• Incremental backup (with optional zero data loss, RPO=0)
• Semi-automated Point In Time Recovery Procedure
• Monitoring exporters for all the components(nodes, balancers, backup)

What's in the box

This project includes:

• Dockerfiles for postgresql cluster and backup system
  • postgresql
  • pgpool
  • barman
• Examples of usage(suitable for production environment as architecture has fault protection with auto failover)
  • example of docker-compose file to start this cluster.
  • directory k8s contains information for building this cluster in Kubernetes

Docker images tags convention

Taking into account that PostDock project itself has versioning schema, all docker images produced by the repository have schema - postdock/<component>:<postdock_version>-<component><component_version>-<sub_component><sub_component_version>[...], where:

• <postdock_version> - semantic version without bug-fix component (can be 1.1, 1.2, ...)
• <component>, <component_version> - depends on component:
  • postgres,postgres-extended - major and minor version without dot in between(can be 95, 96, 10, 11, ...)
  • pgpool - major and minor version of component without dot in between(can be 33, 36, 37, ...)
  • barman - major version only (can be 23, 24, ...)
• <sub_component>, <sub_component_version> - depends on component:
  • for postgres - repmgr can be 32, 40, ...
  • for barman - postgres can be 96, 10, 11, ...
  • for pgpool - postgres can be 96, 10, 11, ...

Aliases are available (not recommended to use for production):

• postdock/<component>:latest-<component><component_version>[-<sub_component><sub_component_version>[...]] - refers to the latest release of the postdock, certain version of the component, certain version of the sub-components(e.g. postdock/postgres:latest-postgres101-repmgr32,postdock/postgres:latest-barman23-postgres101)
• postdock/<component>:latest - refers to the latest release of the postdock and the latest versions of all the components and sub-components (e.g. postdock/postgres:latest)
• postdock/<component>:edge - refers to build of postdock from master with the latest version the component, and all sub-components (e.g. postdock/postgres:edge)

Repositories

All available tags (versions and combinations of it) are listed in respective docker-hub repositories:

• Postgres
• Pgpool
• Barman

Start cluster with docker-compose

To start cluster run it as normal docker-compose application docker-compose -f ./docker-compose/latest.yml up -d pgmaster pgslave1 pgslave2 pgslave3 pgslave4 pgpool backup

Schema of the example cluster:

pgmaster (primary node1)  --|
|- pgslave1 (node2)       --|
|  |- pgslave2 (node3)    --|----pgpool (master_slave_mode stream)
|- pgslave3 (node4)       --|
   |- pgslave4 (node5)    --|

Each postgres node (pgmaster, pgslaveX) is managed by repmgr/repmgrd. It allows to use automatic failover and check cluster status.

Please check comments for each ENV variable in ./docker-compose/latest.yml file to understand parameter for each cluster node

Start cluster in Kubernetes

Using Helm (recomended for production)

You can install PostDock with Helm package manager check the README.md of the package for more information

Simple (NOT recomended for production)

To make it easier repository contains services' objects under k8s dir. Setup PostgreSQL cluster following the steps in the example. It also has information how to check cluster state

Configuring the cluster

You can configure any node of the cluster(postgres.conf) or pgpool(pgpool.conf) with ENV variable CONFIGS (format: variable1:value1[,variable2:value2[,...]], you can redefine delimiter and assignment symbols by using variables CONFIGS_DELIMITER_SYMBOL, CONFIGS_ASSIGNMENT_SYMBOL). Also see the Dockerfiles and docker-compose/latest.yml files in the root of the repository to understand all available and used configurations!

Postgres

For the rest - you better follow the advise and look into the src/Postgres-latest.Dockerfile file - it full of comments :)

Pgpool

The most important part to configure in Pgpool (apart of general CONFIGS) is backends and users which could access these backends. You can configure backends with ENV variable. You can find good example of setting up pgpool in docker-compose/latest.yml file:

DB_USERS: monkey_user:monkey_pass # in format user:password[,user:password[...]]
BACKENDS: "0:pgmaster:5432:1:/var/lib/postgresql/data:ALLOW_TO_FAILOVER,1:pgslave1::::,3:pgslave3::::,2:pgslave2::::" #,4:pgslaveDOES_NOT_EXIST::::
            # in format num:host:port:weight:data_directory:flag[,...]
            # defaults:
            #   port: 5432
            #   weight: 1
            #   data_directory: /var/lib/postgresql/data
            #   flag: ALLOW_TO_FAILOVER
REQUIRE_MIN_BACKENDS: 3 # minimal number of backends to start pgpool (some might be unreachable)

Barman

The most important part for barman is to setup access variables. Example can be found in docker-compose/latest.yml file:

REPLICATION_USER: replication_user # default is replication_user
REPLICATION_PASSWORD: replication_pass # default is replication_pass
REPLICATION_HOST: pgmaster
POSTGRES_PASSWORD: monkey_pass
POSTGRES_USER: monkey_user
POSTGRES_DB: monkey_db

Other configurations

See the Dockerfiles and docker-compose/latest.yml files in the root of the repository to understand all available and used configurations!

Adaptive mode

'Adaptive mode' means that node will be able to decide if instead of acting as a master on it's start or switch to standby role. That possible if you pass PARTNER_NODES (comma separated list of nodes in the cluster on the same level). So every time container starts it will check if it was master before and if there is no new master around (from the list PARTNER_NODES), otherwise it will start as a new standby node with upstream = new master in the cluster.

Keep in mind: this feature does not work for cascade replication and you should not pass PARTNER_NODES to nodes on second level of the cluster. Instead of it just make sure that all nodes on the first level are running, so after restart any node from second level will be able to follow initial upstream from the first level. That also can mean - replication from second level potentially can connect to root master... Well not a big deal if you've decided to go with adaptive mode. But nevertheless you are able to play with NODE_PRIORITY environment variable and make sure entry point for second level of replication will never be elected as a new root master

SSH access

If you have need to organize your cluster with some tricky logic or less problematic cross checks. You can enable SSH server on each node. Just set ENV variable SSH_ENABLE=1 (disabled by default) in all containers (including pgpool and barman). That will allow you to connect from any to any node by simple command under postgres user: gosu postgres ssh {NODE NETWORK NAME}

You also will have to set identical ssh keys to all containers. For that you need to mount files with your keys in paths /tmp/.ssh/keys/id_rsa, /tmp/.ssh/keys/id_rsa.pub.

Replication slots

If you want to disable the feature of Postgres>=9.4 - replication slots simply set ENV variable USE_REPLICATION_SLOTS=0 (enabled by default). So cluster will rely only on Postgres configuration wal_keep_segments (500 by default).