Periodic database backups

[bluesurfer]

It would be really nice if we could schedule the frequency of database backups. At the moment we have to run manually (and locally) the docker-composecommand to backup Postgres.

[webyneter]

@bluesurfer if we're talking about remotely deployed environments, most modern PaaSes support that out-of-box; are we talking about enhancing local environment backup management?

[webyneter]

@bluesurfer?

[petrprikryl]

I think that @bluesurfer was talking about built-in backup cron job in postgres container. Or standalone backup container (service) built onto postgres image with configurable scheduling. With this feature you wouldn't need to setup backup cron job in host system manually.

[martibosch]

Hello!

as suggested by @petrprikryl would be useful, guidance on how to set up a cron job or use a standalone container to handle periodic backups. I could set up a cron job locally to periodically execute the backup commands (create a backup and upload it to a s3 instance), but I suppose that there must be a better approach.

Best,
Martí

[foarsitter]

The final answer for your PostgreSQL backups is barman.

For daily backups you can use the cron approach you suggest. Setup an cron-job or an standalone container does not make a lot of difference in my opinion.

This is my backup.sh:

#!/usr/bin/env bash

working_dir="$(dirname ${0})"

/usr/local/bin/docker-compose -f ${working_dir}/production.yml run --rm postgres backup
/usr/local/bin/docker-compose -f ${working_dir}/production.yml run --rm awscli upload

[bluesurfer]

We still are using a backup script exactly as the one suggested by @foarsitter. However such script is run locally, so in my opinion it feels "outside" the Docker workflow (what if I want to deploy the same app on multiple machines? I would have to manually setup the backup script for each one). It works though :)

To me a nice solution would be to extend the current awscli container by specifying, in the corresponding Dockerfile, the CRON task responsible for running the backup. Something similar to docker-pg-backup.

Finally, an environment variable named AWS_CRON_DB_BACKUP would be used to specify the cron schedule for when the backup needs to run.

[martibosch]

Hello again, thank you for your answers.

Indeed I had a cron-job like @foarsitter 's, however, as mentioned by @bluesurfer, running it locally feels "outside" the Docker workflow. The first idea by @bluesurfer sounds good, I believe that something like that should be included in the production Docker workflow.

Best,
Mart'i

[foarsitter]

We can replace the aswcli container with docker-pg-backup I suppose. A mapping for our environment variables and voila.

[bluesurfer]

@webyneter @foarsitter

Since this has become mandatory for my project I will share with you my solution. This article and this repository have been very useful.

Here's the changes that I made (if it make sense to you I'll make a PR)

compose/production/aws/Dockerfile

FROM debian:bullseye-slim

ENV AWS_CLI_VERSION 2.0.30


RUN apt-get update \
    && apt-get -y upgrade \
    && apt-get install -y ca-certificates curl postgresql unzip cron \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && rm -rf /var/lib/apt/lists/*


RUN curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64-${AWS_CLI_VERSION}.zip" -o "awscliv2.zip" \
    && unzip awscliv2.zip  \
    && ./aws/install \
    && rm awscliv2.zip \
    && rm -rf aws


COPY ./compose/production/aws/maintenance /usr/local/bin/maintenance
COPY ./compose/production/postgres/maintenance/backup /usr/local/bin/maintenance
COPY ./compose/production/postgres/maintenance/_sourced /usr/local/bin/maintenance/_sourced

RUN chmod +x /usr/local/bin/maintenance/*

RUN mv /usr/local/bin/maintenance/* /usr/local/bin \
    && rmdir /usr/local/bin/maintenance

ARG AWS_CRON_DB_BACKUP
RUN echo "${AWS_CRON_DB_BACKUP} /usr/local/bin/backup && /usr/local/bin/upload >> /var/log/cron.log 2>&1\n" | tr -d '"' > /etc/cron.d/cron

RUN chmod 0644 /etc/cron.d/cron


RUN crontab /etc/cron.d/cron


RUN touch /var/log/cron.log


COPY ./compose/production/aws/start /start
RUN chmod +x /start
CMD "/start"

compose/production/aws/start

#!/usr/bin/env bash

set -o errexit
set -o pipefail
set -o nounset

printenv | grep -v "no_proxy" >> /etc/environment


cron && tail -f /var/log/cron.log

production.yml

  awscli:
    build:
      context: .
      dockerfile: ./compose/production/aws/Dockerfile
      args:
        - AWS_CRON_DB_BACKUP=${AWS_CRON_DB_BACKUP-0 0 * * *}
    env_file:
      - ./.envs/.production/.django
    volumes:
      - production_postgres_data_backups:/backups:z

At the end by setting AWS_CRON_DB_BACKUP you would be able to schedule the database backup and upload to S3 (defaults to "every day at midnight")

[foarsitter]

Sweet solution @bluesurfer, I would like to see a pull-request since this does it all.