autopostgresqlbackup/autopostgresqlbackup.1

.\" Automatically generated by Pandoc 2.9.2.1
.\"
.TH "" "" "" "" ""
.hy
.SS NAME
.PP
autopostgresqlbackup \[en] Automated tool to make periodic backups of
databases
.SS SYNOPSIS
.PP
\f[C]autopostgresqlbackup [OPTIONS]\f[R]
.SS DESCRIPTION
.PP
AutoPostgreSQLBackup is a shell script (usually executed from a cron job
or a systemd timer) designed to provide a fully automated tool to make
periodic backups of databases.
.PP
AutoPostgreSQLBackup extract databases into flat files (compressed or
not, encrypted or not) in a daily and/or weekly and/or monthly basis.
.PP
AutoPostgreSQLBackup supports mutliple databases engines (PostgreSQL and
MySQL by now).
.SS OPTIONS
.PP
\f[C]-h\f[R] displays command line help
.PP
\f[C]-d\f[R] Run in debug mode (no mail sent)
.PP
\f[C]-c\f[R] Configuration file or directory (default:
\f[C]/etc/autodbbackup.d/\f[R])
.PP
When a directory is used, the \f[C]*.conf\f[R] files will be processed
sequentially.
It allows one to backup multiple databases servers with distinct
settings :
.IP \[bu] 2
database servers with distinct engines
.IP \[bu] 2
PostgreSQL cluster with instances running on multiple ports
.PP
Note: if no configuration file or directory is passed as argument but
\f[C]/etc/default/autopostgresqlbackup\f[R] exists, it will be used for
backward compatibility.
.SS ENCRYPTION
.PP
Encryption (asymmetric) is now done with GnuPG, you just need to add the
public key (armored or not) you want to encrypt the data to in the file
pointed by the \f[C]ENCRYPTION_PUBLIC_KEY\f[R] configuration setting.
.PP
Export your public key:
.PP
\f[C]gpg --export 0xY0URK3Y1D --output mypubkey.gpg\f[R]
.PP
or
.PP
\f[C]gpg --export --armor 0xY0URK3Y1D --output mypubkey.asc\f[R]
.PP
then copy mypubkey.asc or mypubkey.gpg to the path pointed by the
\f[C]ENCRYPTION_PUBLIC_KEY\f[R] configuration setting and set the
\f[C]ENCRYPTION\f[R] setting to yes.
.SS DECRYPTION
.PP
In order to decrypt a previously encrypted database dump:
.PP
\f[C]gpg --decrypt --output backup.sql.gz backup.sql.gz.enc\f[R]
.SS OPENSSL ENCRYPTION
.PP
Starting from version 2.0 encryption with OpenSSL is no longer supported
as it was discovered[1] (but also known for quite some time[2]) that
encrypting large files with OpenSSL silently fail[3] and that decrypting
these files is close to be impossible[4].
.IP \[bu] 2
[1] https://github.com/k0lter/autopostgresqlbackup/issues/10
.IP \[bu] 2
[2] https://github.com/cytopia/mysqldump-secure/issues/21
.IP \[bu] 2
[3] https://github.com/openssl/openssl/issues/2515
.IP \[bu] 2
[4]\ https://github.com/imreFitos/large_smime_decrypt
.SS CONFIGURATION
.SS MAILADDR
.PP
Email Address to send errors to.
If empty errors are displayed on stdout.
.PP
\f[B]default\f[R]: \f[C]root\f[R]
.SS DBENGINE
.PP
Database engine
.PP
\f[B]default\f[R]: \f[C]postgresql\f[R]
.PP
\f[I]supported values\f[R]: \f[C]postgresql\f[R] or \f[C]mysql\f[R]
.SS USERNAME
.PP
Username to access the database server
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty, the username to use is
automatically defined depending on \f[C]DBENGINE\f[R]:
\f[C]postgres\f[R] for PostgreSQL and \f[C]root\f[R] for MySQL)
.SS SU_USERNAME
.PP
By default, on Debian systems (and maybe others), only
\f[C]postgres\f[R] user is allowed to access PostgreSQL databases
without password.
.PP
In order to dump databases we need to run pg_dump/psql commands as
\f[C]postgres\f[R] with su.
.PP
This setting makes possible to run backups with a substitute user using
\f[C]su\f[R].
If empty, \f[C]su\f[R] usage will be disabled)
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty, not used)
.PP
\f[I]Only while using PostgreSQL database engine\f[R]
.SS PASSWORD
.PP
Password to access then Database server
.PP
While using PostgreSQL database engine, in order to use a password to
connect to database create a file \f[C]\[ti]/.pgpass\f[R] containing a
line like this:
.PP
\f[C]hostname:*:*:dbuser:dbpass\f[R]
.PP
replace \f[C]hostname\f[R] with the value of \f[C]DBHOST\f[R],
\f[C]dbuser\f[R] with the value of \f[C]USERNAME\f[R] and
\f[C]dbpass\f[R] with the password.
.PP
While using MySQL database engine, if password is not set mysqldump will
try to read credentials from \f[C]\[ti]/.my.cnf\f[R] if file exists.
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty)
.SS DBHOST
.PP
Host name (or IP address) of database server.
Use \f[C]localhost\f[R] for socket connection or \f[C]127.0.0.1\f[R] to
force TCP connection.
.PP
\f[B]default\f[R]: \f[C]localhost\f[R]
.SS DBPORT
.PP
Port of database server.
.PP
While using PostgreSQL database engine, it is also used if
\f[C]DBHOST\f[R] is \f[C]localhost\f[R] (socket connection) as socket
name contains port.
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty, the port to use is
automatically defined depending on \f[C]DBENGINE\f[R]: \f[C]5432\f[R]
for PostgreSQL and \f[C]3306\f[R] for MySQL)
.SS DBNAMES
.PP
Explicit list of database(s) names(s) to backup
.PP
If you would like to backup all databases on the server set
\f[C]DBNAMES=\[dq]all\[dq]\f[R].
If set to \f[C]\[dq]all\[dq]\f[R] then any new databases will
automatically be backed up without needing to modify this settings when
a new database is created.
.PP
If the database you want to backup has a space in the name replace the
space by a \f[C]%20\f[R] (\f[C]\[dq]data base\[dq]\f[R] will become
\f[C]\[dq]data%20base\[dq]\f[R]).
.PP
\f[B]default\f[R]: \f[C]all\f[R]
.PP
\f[B]example\f[R]: \f[C]\[dq]users pages user%20data\[dq]\f[R]
.SS DBEXCLUDE
.PP
List of databases to exclude if \f[C]DBNAMES\f[R] is not set to
\f[C]all\f[R].
.PP
\f[B]default\f[R] : \f[C]\[dq]\[dq]\f[R] (empty)
.PP
\f[B]example\f[R]: \f[C]\[dq]pages user%20data\[dq]\f[R]
.SS GLOBALS_OBJECTS
.PP
Virtual database name used to backup global objects (users, roles,
tablespaces).
.PP
\f[B]default\f[R]: \f[C]postgres_globals\f[R]
.PP
\f[I]Only while using PostgreSQL database engine\f[R]
.SS BACKUPDIR
.PP
Backup directory
.PP
\f[B]default\f[R]: \f[C]/var/backups\f[R]
.SS CREATE_DATABASE
.PP
Include or not \f[C]CREATE DATABASE\f[R] statments in dabatbases
backups.
.PP
\f[B]default\f[R]: \f[C]yes\f[R]
.PP
\f[I]supported values\f[R]: \f[C]yes\f[R] or \f[C]no\f[R]
.SS DOWEEKLY
.PP
Which day do you want weekly backups?
(1 to 7 where 1 is Monday).
.PP
When set to 0, weekly backups are disabled.
.PP
\f[B]default\f[R]: \f[C]7\f[R] (Sunday)
.SS DOMONTHLY
.PP
Which day do you want monthly backups?
.PP
When set to 0, monthly backups are disabled.
.PP
\f[B]default\f[R]: \f[C]1\f[R] (first day of the month)
.SS BRDAILY
.PP
Backup retention count for daily backups, older backups are removed.
.PP
\f[B]default\f[R]: \f[C]14\f[R] (14 days)
.SS BRWEEKLY
.PP
Backup retention count for weekly backups, older backups are removed.
.PP
\f[B]default\f[R]: \f[C]5\f[R] (5 weeks)
.SS BRMONTHLY
.PP
Backup retention count for monthly backups, older backups are removed.
.PP
\f[B]default\f[R]: \f[C]12\f[R] (12 months)
.SS COMP
.PP
Compression tool.
It could be gzip, pigz, bzip2, xz, zstd or any compression tool that
supports to read data to be compressed from stdin and outputs them to
stdout).
.PP
If the tool is not in \f[C]${PATH}\f[R], the absolute path can be used.
.PP
\f[B]default\f[R]: \f[C]gzip\f[R]
.SS COMP_OPTS
.PP
Compression tools options to be used with \f[C]COMP\f[R]
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty)
.PP
\f[B]example\f[R]:
\f[C]COMP=\[dq]zstd\[dq] COMP_OPTS=\[dq]-f -c\[dq]\f[R]
.SS PGDUMP
.PP
pg_dump path (relative if present in \f[C]${PATH}\f[R] or absolute)
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (if empty \f[C]pg_dump\f[R] will
be used)
.PP
\f[I]Only while using PostgreSQL database engine\f[R]
.SS PGDUMPALL
.PP
pg_dumpall path (relative if present in \f[C]${PATH}\f[R] or absolute)
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (if empty \f[C]pg_dumpall\f[R]
will be used)
.PP
\f[I]Only while using PostgreSQL database engine\f[R]
.SS PGDUMP_OPTS
.PP
Options string for use with pg_dump (see
pg_dump (https://www.postgresql.org/docs/current/app-pgdump.html) manual
page).
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty)
.PP
\f[I]Only while using PostgreSQL database engine\f[R]
.SS PGDUMPALL_OPTS
.PP
Options string for use with pg_dumpall (see
pg_dumpall (https://www.postgresql.org/docs/current/app-pg-dumpall.html)
manual page).
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty)
.PP
\f[I]Only while using PostgreSQL database engine\f[R]
.SS MY
.PP
mysql path (relative if present in \f[C]${PATH}\f[R] or absolute)
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (if empty \f[C]mysql\f[R] will
be used)
.PP
\f[I]Only while using MySQL database engine\f[R]
.SS MYDUMP
.PP
mysqldump path (relative if present in \f[C]${PATH}\f[R] or absolute)
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (if empty \f[C]mysqldump\f[R]
will be used)
.PP
\f[I]Only while using MySQL database engine\f[R]
.SS MYDUMP_OPTS
.PP
Options string for use with mysqldump (see
mysqldump (https://dev.mysql.com/doc/refman/8.3/en/mysqldump.html)
manual page).
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty)
.PP
\f[I]Only while using MySQL database engine\f[R]
.SS EXT
.PP
Backup files extension
.PP
\f[B]default\f[R]: \f[C]sql\f[R]
.SS PERM
.PP
Backup files permission
.PP
\f[B]default\f[R]: \f[C]600\f[R]
.SS MIN_DUMP_SIZE
.PP
Minimum size (in bytes) for a dump/file (compressed or not).
File size below this limit will raise a warning.
.PP
\f[B]default\f[R]: \f[C]256\f[R]
.SS ENCRYPTION
.PP
Enable encryption (asymmetric) with GnuPG.
.PP
\f[B]default\f[R]: \f[C]no\f[R]
.PP
\f[I]supported values\f[R]: \f[C]yes\f[R] or \f[C]no\f[R]
.SS ENCRYPTION_PUBLIC_KEY
.PP
Encryption public key (path to the key)
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty)
.SS ENCRYPTION_SUFFIX
.PP
Suffix for encyrpted files
.PP
\f[B]default\f[R]: \f[C].enc\f[R]
.SS PREBACKUP
.PP
Command or script to execute before backups
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty, not used)
.SS POSTBACKUP
.PP
Command or script to execute after backups
.PP
\f[B]default\f[R]: \f[C]\[dq]\[dq]\f[R] (empty, not used)
.SS AUTHORS
.PP
Originally developped by Aaron Axelsen with Friedrich Lobenstock
contributions.
.PP
Almost fully rewritten by Emmanuel Bouthenot (version 2.0 and higher).
.SS LICENSE AND COPYRIGHT
.PP
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.
.PP
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.
.SS CONTRIBUTIONS
.PP
Contributions are welcome on the project page:
https://github.com/k0lter/autopostgresqlbackup/pulls
.SS BUGS
.PP
Bug reports are welcome on the project page:
https://github.com/k0lter/autopostgresqlbackup/issues
.SS SEE ALSO
.PP
\f[C]pg_dump\f[R](1), \f[C]pg_dumpall\f[R](1), \f[C]mysqldump\f[R](1)
and the project page https://github.com/k0lter/autopostgresqlbackup/