Public Repository

Last pushed: 5 months ago
Short Description
Short description is empty for this repo.
Full Description

PostgreSQL Docker image bundled with PgBouncer for connection pooling and Runit

for service supervision.

Building the image

docker build -t registry.discourse.org:5000/postgres:9.5 .

Persistent of data

In order for data to be persisted on the host volume, start the container with
-v <path on host> as follows:

docker run -v <path on host>:/shared registry.discourse.org:5000/postgres:9.5

Creating a database

A new database can be created by specifying the following environment variables
when starting a container:

  • DB_NAME: The name of the database to be created.
  • DB_USER: The username of the user to be created. The user will be
    assigned all privileges and ownership of the database created and be added
    as to stats_users in PgBouncer's config.
  • DB_PASS: The password for the user.
docker run -d --name postgres \
  -e DB_NAME=mydb \
  -e DB_USER=myuser \
  -e DB_PASS=mypassword \
  registry.discourse.org:5000/postgres:9.5

The configuration files for PgBouncer are automatically updated as well when
creating a database. The configuration files can be found at /shared/pgbouncer/etc
in the container.

A database can also be created while the container is running by executing the following command:

docker exec -it postgres "./setup createdb db_name db_user db_pass"

Database Replication

The image uses both file-based replication and streaming replication. File-based replication
allows replica servers to catch up to the master server after the container has been restarted while
streaming replication reduces the window of data loss should the master server goes down.

Starting a master server

A master server must first be configured by creating a user assigned the replication role.

docker run -d --name master \
  -e REPLICATION_MODE=master \
  -e REPLICATION_USER=myuser \
  -e REPLICATION_PASS=mypassword \
  -v /shared/master:/shared
  registry.discourse.org:5000/postgres:9.5

Note: If data is persisted on the host and a replication user has been configured, only
REPLICATION_MODE=master will be required to run the container.

docker run -d --name master -v /shared/master:/shared -e REPLICATION_MODE=master registry.discourse.org:5000/postgres:9.5

Starting a replication server

A replication server for a master server by specifying the following environment variables
when starting the container:

  • REPLICATION_MODE: "replication"
  • REPLICATION_USER: The username of the replication user on the master server.
  • REPLICATION_PASS: The password of the replication user on the master server.
  • REPLICATION_HOST: The IP address of the master server.

In order to enable file-based replication, replica servers have to include the archivedir
-in the container's volume with -v /shared/master/postgres/archivedir:/shared/postgres/archivedir.
Note that this container is built with the assumption that only one replication server will be used to replicate the master server.
This is to allow us to use log file based shipping while being able to easily clean up WAL archives.

docker run -d --name replication_1 \
  -e REPLICATION_MODE=replication \
  -e REPLICATION_USER=myuser \
  -e REPLICATION_PASS=mypassword \
  -e REPLICATION_HOST=170.17.0.2 \
  -v /shared/replica_1:/shared \
  -v /shared/master/postgres/archivedir:/shared/postgres/archivedir \
  -v /shared/master/pgbouncer/etc:/shared/pgbouncer/etc \
  registry.discourse.org:5000/postgres:9.5

Configuration

The following settings may be configured through environment variables:

  • DB_ADDRS: Sets the listen_addresses on which the server would listen on. Defaults to *.
  • DB_PORT: Sets the port on which the server listens to. Defaults to 5432.
  • DB_MAX_CONNECTIONS: Sets the max_connections allowed to the server. Defaults to 100.
  • DB_SHARED_BUFFERS: Sets the amount of shared_buffers memory used by the database server.
  • DB_WORK_MEM: Sets the amount of work_mem allocated.
  • DB_MAINTENANCE_MEM: Sets the amount of maintainance_work_mem allocated.
  • DB_ARCHIVE_COMMNAD: The command to be used for WAL archiving. Defaults to test ! -f ${PG_ARCHIVE_DIR}/%f \&\& cp %p ${PG_ARCHIVE_DIR}/%f.
  • PGBOUNCER_MAX_CLIENT_CONN: Sets the max_client_conn allowed to PgBouncer. Defaults to 20000.

Backups

Exclusive lock when running backups

If you are running multiple replicas on the same server, running multiple backups in parallel can overload the server. In order to avoid
that, each backup will acquire an exclusive lock of a lock file at /shared/lock/.backup.lock. In order to share the lock file between
multiple replicas, simple mount an additional volume by adding -v /shared/lock:/shared/lock when running the container.

Uploading backups up to Amazon S3

The container uses AWS Cli for uploading backups to an Amazon S3 bucket.

In order to enable this feature, pass the following environment variables when starting a replication container:

  • S3_BUCKET_NAME: Bucket name to be used for storing the backups. Example: s3://postgres/backups/.
  • S3_ACCESS_KEY: Amazon S3 access key ID.
  • S3_SECRET_KEY: Amazon S3 secret access key.
  • S3CMD_GPG_PASS (optional): Specify the passphrase to encrypt the uploads. Backups to S3 are not encrypted if the environment variable is not set.

Failover

When a master server is down, follow the following steps to switch over to the replica server and promote it to a master.

  1. Confirm that master is down. Otherwise, shoot the master server in the head with docker stop <name of master container>; docker rm <name of master container>
  2. Run docker exec <name of replica container> touch /tmp/postgresql.trigger to promote the replica to a master server hereby known as current Master.
  3. Confirm that current Master is acting as a master by checking the latest log file in "/shared/postgres/data/pg_log".
  4. Check that current Master is performing WAL archiving by running ps fax | grep "archiver process".
  5. Point application server to current Master.
  6. Remove cron job on current Master by running docker exec <name of current master container> rm /var/spool/cron/crontabs/root

Once the current Master is stable and receiving data, begin switch back process by carrying out the following steps:

  1. Start a replica server of current Master by running
    docker run --name master-new -e REPLICATION_MODE=replication -e REPLICATION_HOST=<IP address of current Master> -e REPLICATION_USER=myuser -e REPLICATION_PASS=mypassword -v /shared/new_master:/shared -v /shared/replica_1/archivedir:/shared/postgres/archivedir registry.discourse.org:5000/postgres:9.5

Note that the data volume of the new master server has to be mounted onto a new directory on the host.

  1. Stop the application server or activate read only mode otherwise you risk losing data.
  2. Repeat step 1-6 of the failover process.
  3. Finally start another replica server for your new master.

Upgrading

Upgrading of between versions of PostgreSQL is handled automatically by the container using pg_upgrade
and can be done by starting the PostgreSQL container using the same host volume.

Restoring

From a backup

Follow the following steps to recover a new PostgreSQL data volume from a backup.

  1. docker run --name recovery -v /shared/new_server:/shared registry.discourse.org:5000/postgres:9.5
  2. Copy your back.tar.gz file into /shared/new_server/postgres. cp <backup to restore>.tar.gz /shared/new_server/postgres/backup.tar.gz
  3. docker exec -it recovery /bin/bash
  4. sv stop 001-postgres
  5. rm -rf /shared/postgres/data/*
  6. tar -xvf /shared/postgres/backup.tar.gz -C /shared/postgres/data/
  7. rm /shared/postgres/data/recovery.conf
  8. sv start 001-postgres
  9. Run psql and verify that the data has been recovered.

Note: In order to start as a master or replica server, remove the container and mount onto the recovered volume.

  1. docker stop recovery && docker rm recovery
  2. Start a new master server with the recovered data volume. docker run --name my_server -e REPLICATION_MODE=master -e REPLICATION_USER=myuser -e REPLICATION_PASS=mypass -v /shared/new_server:/ registry.discourse.org:5000/postgres:9.5

From a pg_dump script file

  1. docker run --name recovery -e DB_NAME=mydb -e DB_USER=myuser -e DB_PASS=mypass -v /shared/new_server:/shared registry.discourse.org:5000/postgres:9.5
  2. Copy your dump.sql fie into /shared/new_server/postgres on the host. cp dump.sql /shared/new_server/postgres
  3. Restore your database with docker exec recovery su postgres -c "psql $DB_NAME < /shared/postgres/dump.sql".
  4. Check that your data has been sucessfully recovered with docker exec -it recovery su postgres -c "psql $DB_NAME".

Pgbouncer

Accessing console for Pgbouncer

sudo su - pgbouncer
psql -h /tmp -p 6432 -U pgbouncer pgbouncer
Docker Pull Command
Owner
discourse

Comments (0)