AUTOPOSTGRESQLBACKUP(8)

📦
version 2.5 · 2 versions
, in debian trixie, ubuntu resolute, ubuntu stonking

✨ NAME ✨

autopostgresqlbackup — the automated tool that makes periodic backups of databases 💾✨ (yes bestie, you're reading autopostgresqlbackup(8), section 8 of the manual, and it's iconic)

🦋 SYNOPSIS 🦋

autopostgresqlbackup [OPTIONS]

💖 DESCRIPTION 💖

AutoPostgreSQLBackup is a shell script — usually executed from a cron job or a systemd timer — designed to be a fully automated tool that makes periodic backups of databases. Set it, forget it, sleep peacefully 🫶

AutoPostgreSQLBackup extracts databases into flat files (compressed or not, encrypted or not) on a daily and/or weekly and/or monthly basis.

AutoPostgreSQLBackup supports multiple database engines (PostgreSQL and MySQL by now). Versatility? We have it. 💅

💅 OPTIONS 💅

  • -h — displays command line help
  • -d — run in debug mode (no mail sent)
  • -c — configuration file or directory (default: /etc/autodbbackup.d/)

When a directory is used, the *.conf files will be processed sequentially. That's the whole point, because it lets you backup multiple database servers with distinct settings, like:

  • database servers with distinct engines
  • PostgreSQL cluster with instances running on multiple ports
Note: if no configuration file or directory is passed as argument but /etc/default/autopostgresqlbackup exists, it will be used for backward compatibility. Old-school, but valid. 💖

🔐 ENCRYPTION 🔐

Encryption (asymmetric) is now done with GnuPG. All you gotta do is add the public key you want to encrypt the data with (armored or not) into the file pointed by the ENCRYPTION_PUBLIC_KEY configuration setting.

Export your public key:

gpg --export 0xY0URK3Y1D --output mypubkey.gpg

or

gpg --export --armor 0xY0URK3Y1D --output mypubkey.asc

then copy mypubkey.asc or mypubkey.gpg to the path pointed by the ENCRYPTION_PUBLIC_KEY configuration setting, and set the ENCRYPTION setting to yes. That's it, that's the ritual 🎀

🔓 DECRYPTION 🔓

So you wanna decrypt a previously encrypted database dump? Say less:

gpg --decrypt --output backup.sql.gz backup.sql.gz.enc

🌈 OPENSSL ENCRYPTION 🌈

Okay so starting from version 2.0, encryption with OpenSSL is no longer supported. Why? Because it was discovered (and honestly also known for quite quite some time) that encrypting large files with OpenSSL silently fails, and that decrypting these files is close to being impossible. Yeah. It's giving "do not do this" 💀🚫

⚙️ CONFIGURATION ⚙️

📬 MAILADDR

Email Address to send errors to. If it's empty, errors are displayed on stdout.

default: root

🔔 REPORT_ERRORS_ONLY

Send email to MAILADDR only if there are errors.

default: yes

🗄️ DBENGINE

Database engine.

default: postgresql

supported values: postgresql or mysql

👤 USERNAME

Username to access the database server.

default: "" (empty — the username to use is automatically defined depending on DBENGINE: postgres for PostgreSQL and root for MySQL)

🧑‍💻 SU_USERNAME

So here's the vibe: by default, on Debian systems (and maybe others), only the postgres user is allowed to access PostgreSQL databases without a password.

And in order to dump databases we need to run pg_dump/psql commands as postgres with su.

This setting makes it possible to run backups with a substitute user using su. If it's empty, su usage will be disabled.

default: "" (empty, not used)

Only while using PostgreSQL database engine.

🔑 PASSWORD

Password to access then Database server.

While using the PostgreSQL database engine, in order to use a password to connect to the database, create a file ~/.pgpass containing a line like this:

hostname:*:*:dbuser:dbpass

replace hostname with the value of DBHOST, dbuser with the value of USERNAME and dbpass with the password.

While using the MySQL database engine, if password is not set, mysqldump will try to read credentials from ~/.my.cnf if the file exists.

default: "" (empty)

🏠 DBHOST

Host name (or IP address) of the database server. Use localhost for a socket connection, or 127.0.0.1 to force a TCP connection.

default: localhost

🚪 DBPORT

Port of the database server.

While using the PostgreSQL database engine, it is also used if DBHOST is localhost (socket connection), because the socket name contains the port.

default: "" (empty — the port to use is automatically defined depending on DBENGINE: 5432 for PostgreSQL and 3306 for MySQL)

📚 DBNAMES

Explicit list of database(s) names(s) to backup.

If you would like to backup all databases on the server, set DBNAMES="all". If it's set to "all" then any new databases will automatically be backed up, no need to modify this setting when a new database gets created. Chef's kiss 👑

If the database you want to backup has a space in the name, replace the space with %20 ("data base" becomes "data%20base").

default: all

example: "users pages user%20data"

🚫 DBEXCLUDE

List of databases to exclude if DBNAMES is not set to all.

default : "" (empty)

example: "pages user%20data"

🌍 GLOBALS_OBJECTS

Virtual database name used to backup global objects (users, roles, tablespaces).

default: postgres_globals

Only while using PostgreSQL database engine.

📁 BACKUPDIR

Backup directory.

default: /var/backups

🏗️ CREATE_DATABASE

Include or not CREATE DATABASE statements in dabatbases backups.

default: yes

supported values: yes or no

📅 DOWEEKLY

Which day do you want weekly backups? (1 to 7, where 1 is Monday).

When set to 0, weekly backups are disabled.

default: 7 (Sunday)

🗓️ DOMONTHLY

Which day do you want monthly backups?

When set to 0, monthly backups are disabled.

default: 1 (first day of the month)

🗃️ BRDAILY

Backup retention count for daily backups — older backups are removed.

default: 14 (14 days)

📦 BRWEEKLY

Backup retention count for weekly backups — older backups are removed.

default: 5 (5 weeks)

🎁 BRMONTHLY

Backup retention count for monthly backups — older backups are removed.

default: 12 (12 months)

🗜️ COMP

Compression tool. It could be gzip, pigz, bzip2, xz, zstd, or any compression tool that supports reading the data to be compressed from stdin and outputting them to stdout.

If the tool is not in ${PATH}, the absolute path can be used.

default: gzip

🎛️ COMP_OPTS

Compression tools options to be used with COMP.

default: "" (empty)

example: COMP="zstd" COMP_OPTS="-f -c"

🐘 PGDUMP

pg_dump path (relative if present in ${PATH} or absolute).

default: "" (if empty pg_dump will be used)

Only while using PostgreSQL database engine.

🐘 PGDUMPALL

pg_dumpall path (relative if present in ${PATH} or absolute).

default: "" (if empty pg_dumpall will be used)

Only while using PostgreSQL database engine.

🐘 PGDUMP_OPTS

Options string for use with pg_dump (see pg_dump manual page).

default: "" (empty)

Only while using PostgreSQL database engine.

🐘 PGDUMPALL_OPTS

Options string for use with pg_dumpall (see pg_dumpall manual page).

default: "" (empty)

Only while using PostgreSQL database engine.

🐬 MY

mysql path (relative if present in ${PATH} or absolute).

default: "" (if empty mysql will be used)

Only while using MySQL database engine.

🐬 MYDUMP

mysqldump path (relative if present in ${PATH} or absolute).

default: "" (if empty mysqldump will be used)

Only while using MySQL database engine.

🐬 MYDUMP_OPTS

Options string for use with mysqldump (see mysqldump manual page).

default: "" (empty)

Only while using MySQL database engine.

🏷️ EXT

Backup files extension.

default: sql

🔒 PERM

Backup files permission.

default: 600

📏 MIN_DUMP_SIZE

Minimum size (in bytes) for a dump/file (compressed or not). A file size below this limit will raise a warning.

default: 256

🔐 ENCRYPTION

Enable encryption (asymmetric) with GnuPG.

default: no

supported values: yes or no

🔐 ENCRYPTION_PUBLIC_KEY

Encryption public key (path to the key).

default: "" (empty)

🔐 ENCRYPTION_SUFFIX

Suffix for encyrpted files.

default: .enc

⏮️ PREBACKUP

Command or script to execute before backups.

default: "" (empty, not used)

⏭️ POSTBACKUP

Command or script to execute after backups.

default: "" (empty, not used)

👑 AUTHORS 👑

Originally developed by Aaron Axelsen with Friedrich Lobenstock contributions.

Almost fully rewritten by Emmanuel Bouthenot (version 2.0 and higher).

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

🫶 CONTRIBUTIONS 🫶

Contributions are welcome on the project page: https://github.com/k0lter/autopostgresqlbackup/pulls

💀 BUGS 💀

Bug reports are welcome on the project page: https://github.com/k0lter/autopostgresqlbackup/issues

🎀 SEE ALSO 🎀

pg_dump(1), pg_dumpall(1), mysqldump(1) and the project page https://github.com/k0lter/autopostgresqlbackup/

and that's the whole page bestie, no crumbs left 💖 This was the autopostgresqlbackup(8) manual page — section 8, no fancy version number or date printed up top or down bottom, so this little sign-off is doing the heavy lifting. Go back up your databases, you main character 🦋✨

📦 where this page comes from