PostgreSQL backup with pghoard & kubernetes

With Shopify changes, we're currently updating our calculator.
Head over to the article below for more information on the latest changes.
Oops! Something went wrong while submitting the form.
June 17, 2016


This is a small tutorial on how to do incremental backups using pghoard for your PostgreSQL (I assume you’re running everything in Kubernetes). This is intended to help people to get started faster and not waste time finding the right dependencies, etc..

pghoard is a PostgreSQL backup daemon that incrementally backups your files on a object storage (S3, Google Cloud Storage, etc..).
For this tutorial what we’re trying to achieve is to upload our PostgreSQL to S3.

First, let’s create our docker image (we’re using the alpine:3.4 image cause it’s small):

FROM alpine:3.4


RUN apk add --no-cache \
   bash \
   build-base \        
   python3 \
   python3-dev \
   ca-certificates \
   postgresql \
   postgresql-dev \
   libffi-dev \
RUN python3 -m ensurepip && \
   rm -r /usr/lib/python*/ensurepip && \
   pip3 install --upgrade pip setuptools && \
   rm -r /root/.cache && \
   pip3 install boto pghoard

COPY pghoard.json /pghoard.json.template


REPLICA_USER and REPLICA_PASSWORD env vars will be replaced later in your Kubernetes conf by whatever your config is in production, I use those values to test locally using docker-compose.

The config pghoard.json which tells where to get your data from and where to upload it and how:

   "backup_location": "/data",
   "backup_sites": {
       "default": {
           "active_backup_mode": "pg_receivexlog",
           "basebackup_count": 2,
           "basebackup_interval_hours": 24,
           "nodes": [
                   "host": "YOUR-PG-HOST",
                   "port": 5432,
                   "user": "replica",
                   "password": "replica",
                   "application_name": "pghoard"
           "object_storage": {
               "aws_access_key_id": "REPLACE",
               "aws_secret_access_key": "REPLACE",
               "bucket_name": "REPLACE",
               "region": "us-east-1",
               "storage_type": "s3"
           "pg_bin_directory": "/usr/bin"
   "http_address": "",
   "http_port": 16000,
   "log_level": "INFO",
   "syslog": false,
   "syslog_address": "/dev/log",
   "syslog_facility": "local2"

Obviously replace the values above with your own. And read pghoard docs for more config explanation.

Note: Make sure you have enough space in your /data; use a Google Persistent Volume if you DB is very big.

Launch script which does 2 things:

  1. Replaces our ENV variables with the right username and password for our replication (make sure you have enough connections for your replica user)
  2. Launches the pghoard daemon.

#!/usr/bin/env bash

set -e

if [ -n "$TESTING" ]; then
   echo "Not running backup when testing"
   exit 0

cat /pghoard.json.template | sed "s/\"password\": \"replica\"/\"password\": \"${REPLICA_PASSWORD}\"/" | sed "s/\"user\": \"replica\"/\"password\": \"${REPLICA_USER}\"/" > /pghoard.json
pghoard --config /pghoard.json

Once you build and upload your image to you’ll need a replication controller to start your pghoard daemon pod:

apiVersion: v1
kind: ReplicationController
 name: pghoard
 replicas: 1
   app: pghoard
       app: pghoard
       - name: pghoard
           - name: REPLICA_USER
             value: "replicant"
           - name: REPLICA_PASSWORD
             value: "The tortoise lays on its back, its belly baking in the hot sun, beating its legs trying to turn itself over. But it can't. Not with out your help. But you're not helping."

The reason I use a replication controller is because I want the pod to restart if it fails, if a simple pod is used it will stay dead and you’ll not have backups.

Future to do:

  • Monitoring (are you backups actually done? if not, do you receive a notification?)
  • Stats collection.
  • Encryption of backups locally and then uploaded to the cloud (this is supported by pghoard).

Hope it helps, stay safe and sleep well at night.

Again, repo with the above:

Read more posts by this author.
Near-future is for human-computer hybrids
Most of tech startups today try to be scrappy, to have many users and customers while keeping a small team.

Start using Gorgias now

Create your helpdesk in minutes. Our team is here to help and would be happy to chat with you.