Docker – backup postgresql via docker exec

dockerpostgresql

How would you go about taking automatic rotated backups of a postgresql database running inside a docker 1.3 container? I'm mostly interested in the overall approach vs. the details of actual the backup commands themselves.

I am thinking about updating this autobackup script, which has apparently been around since 2005. My current plan is

Install the script in cron.daily on the docker host
Have the script use direct local filesystem access for the rotation logic to handle the existence tests, mkdir and rm commands
use docker exec containername to run the psql and pg_dump commands within the container, probably sending the stdout content to the docker host filesystem in the correct place via shell redirection

Best Answer

The autobackup script is nice but don't verget to make a backup of the users and passwords: pd_dumpall -g If you don't need seperate backups of each database inside postgresql, it's eysier to use pg_dumpall.

Related Solutions

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

OK, got it from someone else. This query should do the trick:

select *
from pg_stat_activity
where datname = 'mydatabasename';

Postgresql daily backup

Since my databases are fairly small I "cheat" and use logrotate to do my postgresql-backups. Something like this in /etc/logrotate.d/postgresql-backup :

/var/backups/postgresql-dump.sql {
        daily
        nomissingok
        rotate 30
        compress
        delaycompress
        ifempty
        create 640 root adm
        dateext
        postrotate
                /usr/bin/sudo -u postgres /usr/bin/pg_dumpall --clean > /var/backups/postgresql-dump.sql
        endscript
}

This gives me a conveniently available complete dump, no more than a day old, and automatically compresses and rotates previous dumps.

Obviously this won't scale with larger databases, but for my purpose it's just about perfect. (I also use BackupPC to handle off-site backups which include the local postgresql-backups)

Best Answer

Related Solutions

Postgresql – How to see active connections and “current activity” in PostgreSQL 8.4

Postgresql daily backup

Related Topic