k0lter / autopostgresqlbackup Goto Github PK
View Code? Open in Web Editor NEWAutomated tool to make periodic backups of PostgreSQL databases
Automated tool to make periodic backups of PostgreSQL databases
Hi,
I have a few feature requests for this script that I sort of install on every server I setup:
Kind regards,
Jeroen Baten
Note: this bug was also reported in Debian: #956083
Due to a human error, today I had to resort to backups to recover data from a
PostgreSQL database. In the worst possible moment, I realised that ALL of the
backups we had of this database are useless.
Turns out that when dumping the database to a huge text file, the system was
running out of disk space, and so the file is truncated.
Instead of detecting the problem, aborting the backup, and sending an email to
the administrator, autopostgresqlbackup assumes the dump finished well,
proceeded to compress the truncated dump, and continued as if nothing had
happened. This had been going on for months without me realising, and so I have
no useful backup.
I believe this is critical, because this bug has made me lose real world data today.
The sad part is that the bug is pretty trivial to fix: all commands should be
checked for non-zero return codes, probably by setting the '-e' flag at the
beginning of the script.
When using the su option and not running under cron but under my personal account
sudo autopostgresqlbackup
I get a lot of empty backup files (19 bytes, what happens when you zstd an empty file). only pg_dumpall works, the pg_dumps are empty. There is no error message or nothing interesting in the debug logging. If I keep the logs, I see this:
$ cat ~/ERRORS_localhost-351501695.log
Session terminated, killing shell...pg_dump: terminated by user
...killed.
I'm using autopostgresqlbackup on multiple Rocky Linux 8.7 systems and I always set the following in /etc/profile:
TMOUT=300
readonly TMOUT
export TMOUT
This is not an uncommon practice and a recommendation by many hardening guides.
For autopostgresqlbackup though it leads to multiple warnings/errors in the report:
/etc/profile: line 88: TMOUT: readonly variable
The backup is done correctly though so I wondered if there is a workaround for this message not to appear in the reports?
I can set DEBUG="yes" only by modifying the script. I'd like to be able to set it from /etc/default/autopostgresqlbackup. Additionaly, a flag to disable the rm -f "${LOG_FILE}" would be much appreciated while troubleshooting
As it is now, autopostgresqlbackup creates duplicate files on disk.
For example:
# sha1sum latest/postgres_globals_2023-05-04_08h48m.quinta.sql.gz daily/postgres_globals/postgres_globals_2023-05-04_08h48m.quinta.sql.gz
7d69994ce2bbbfd09a0e6c521c79596012b9d814 latest/postgres_globals_2023-05-04_08h48m.quinta.sql.gz
7d69994ce2bbbfd09a0e6c521c79596012b9d814 daily/postgres_globals/postgres_globals_2023-05-04_08h48m.quinta.sql.gz
On disk, they are using different inodes, which means they are unnecessarily consuming disk space:
# ls -i latest/postgres_globals_2023-05-04_08h48m.quinta.sql.gz daily/postgres_globals/postgres_globals_2023-05-04_08h48m.quinta.sql.gz
114430433 daily/postgres_globals/postgres_globals_2023-05-04_08h48m.quinta.sql.gz 114430432 latest/postgres_globals_2023-05-04_08h48m.quinta.sql.gz
If possible, autopostgresqlbackup should use hard links to save space.
As far as I understand, autopostgresqlbackup informs by email only if there is an error.
Is there a way to force autopostgresqlbackup to send mails with log even if there is no error (like the old versions)? Instead of checking the directory to see if everything is alright, getting an email is more relieving.
This is a followup issue regarding #12
Currently the parameter -Fd
for pg_dump
is not supported by autopostgresqlbackup. But it is a prerequisite for the parameter -j
which enables the use of multiple processes in order to speed up the dumping. This is only possible if the dump target is not a file but a directory.
In version 1.0 (possibly the earlier fork) there is an option for passing -b
on the command line to allow easy setting of the backup directory if you're doing multiple backups. This seems to be missing and it'd be great to have it back. I currently am running a system that backs up several databases from different servers, and have a cronjob that is basically
autopostgresqlbackup -h server1.com -b /backup/server1
autopostgresqlbackup -h server2.com -b /backup/server2
[...]
Any chance this can be added?
PostgreSQL: 15.7
AutoPostgreSQLBackup: 2.0
I have 3 DBs. autopostgresqlbackup backups all except one.
DBNAMES="all"
DBEXCLUDE=""
How could it be possible?
We run the debian/ubuntu version of this script with option -Fc to create .pgdump files instead of .sql files. In the v2 version, this fails: $OPTS is not only passed to pg_dump, but also to pg_dumpall. And theoptions for these 2 programs are not the same.
I'd suggest passing OPTS to only pg_dump, and add a new config variable OPS_PGDUMPALL or something.
Related bonus issue: the v1.1 version does not support pg_dump -Fd (which would allow parallel backups): changing rights on a directory failed.
It would be super nice if autopostgresqlbackup would run on OpenBSD as well! :)
There's not much work required to do so, and I've already made some PRs to make autopostgresqlbackup more portable.
I do believe these would make it possible to run autopostgresqlbackup on several other systems as well, i.e. macOS.
Sorry for the spamming of issues and PRs. I thought #25 warranted it's own issue and fix, as it would be nice to fix that on linux as well. #24 and #27 should probably have been in the same PR, but the work was incremental.
Just FYI.
mysql --> mariadb
mysqldump --> mariadb-dump
mysqlshow --> mariadb-show
Perhaps, you can add another DB engine for mariadb?
The script has this code in dump():
db="${1}"
...
cmd_args=("${DB}")
Note how the first is in lower case, and the second in upper case. As a result, the DB argument is never passed to pg_dump, so it reverts to the default database (probably 'postgres'). If you have a db cluster with 10 databases, you get 10 identical backups of the default database, named after the different database. The 9 other backups are missing.
autopostgresqlbackup cgecks uf a dump file is empty:
if [ ! -s "${dump_file}" ]; then
log_error "Something went wrong '${dump_file}' is empty (no space left on device?)"
fi
I would love it if the test checks not for 0 bytes, but for a user-configurable threshold. So i say in the config file
MINIMUM_BACKUP_SIZE=1024
and it warns me if any produced .sql or .pgdump file is less than 1024 bytes.
I ask this because I've managed to get some dumps that are 0 bytes, or only contain a short error message. But after compressing these files, they grow to >0 bytes.
As a bonus, if the total backup size is below some threshold, that would also merit logging an error, but the needed adaptations for this are probably a lot bigger.
Adding support for pigz as compression type would immensely speed up the compression on systems with multiple cores.
When running autopostgresqlbackup with the debug-flag, I noticed an extra space
floating around:
debug|Running command: pg_dump homeassistant --username postgres --create
(Notice the extra space before --create
)
After doing some digging, I think I found the problem. $PGDUMP_ARGS
is set as an array here, and gets transformed to a string at line 356.
Demonstration:
$ PGDUMP_ARGS=()
$ pg_args="${PGDUMP_ARGS[*]}"
$ declare -p pg_args
declare -- pg_args="" # <- empty string
$ pg_args+=(--create)
$ declare -p pg_args
declare -a pg_args=([0]="" [1]="--create") # <- first element in array is an empty string
$ unset pg_args PGDUMP_ARGS
$ PGDUMP_ARGS=(--foo --bar)
$ pg_args="${PGDUMP_ARGS[*]}"
$ declare -p pg_args
declare -- pg_args="--foo --bar" # <- one string with both arguments
$ pg_args+=(--create)
$ declare -p pg_args
declare -a pg_args=([0]="--foo --bar" [1]="--create") # <-- first element in array is string with both arguments
The same goes for $PGDUMPALL_ARGS
, which is handled in the same way.
This apparantly not a problem on Linux, where bash and pg_dump chugs along happily with
this. But, on OpenBSD I get this error when $PGDUMP_OPTS
is empty:
error|pg_dump: error: too many command-line arguments (first is "")
error|pg_dump: hint: Try "pg_dump --help" for more information.
error|Running command 'pg_dump authelia --username root --create has failed
And this when I set more than one option in $PGDUMP_OPTS
:
error|pg_dump: unknown option -- no-comments --no-blobs
error|pg_dump: hint: Try "pg_dump --help" for more information.
error|Running command 'pg_dump authelia --username root --no-comments --no-blobs --create has failed
I'm not sure why this is handled differently on OpenBSD. It's the same shell (bash 5.2.15), with the same BASHOPTS
and SHELLOPTS
.
Would it be possible to add support for multiple hosts, please?
For example, we could have the hosts separated by commas (or another separator) and, for each host, run the same backup process.
# Host names (or IP addresses) of PostgreSQL servers e.g localhost
DBHOST=server1, server2, server3
The config file is today hardcoded to /etc/default/autopostgresqlbackup
If I could provide a config file on the command file, I could schedule multiple instances of autopostgresqlbackup in cron, backing up different postgres clusters from the same machine.
Workaround: The config file is a script file, so it can switch between configs based on an environment variable.
I notice there is not yet a 2.1 tag, and maybe you also want to add upgrade instructions for v2.0 and v2.1, documenting which settings have been added and removed..
I don't see the -d flag mentioned in the docs.
In general, I'd suggest you document settings in table form, with 4 columns: Name, default, available version range, description.
Anyway, thanks very much for all your hard work. It's very rare to see such a huge laundry list of issues fixed in such a short amount of time.
Last package update in debian is from 2021. Is the package still maintained there?
When trying to decrypt a large file (1.9GB )with openssl smime
an error occurs: openssl/openssl#2515
It seems that files larger than 1.5GB cannot be decrypted.
tail +${count} generates an error on RHEL 6-8 systems. Make intent explicit with "-n +${count}"
+++ b/files/usr/local/sbin/autopostgresqlbackup
@@ -475,7 +475,7 @@ cleanup() {
sed -r 's/^.+([0-9]{4}-[0-9]{2}-[0-9]{2}_[0-9]{2}h[0-9]{2}m).*$/\1 \0/'
sort -r | \
sed -r -n 's/\S+ //p' | \
- tail "+${count}" | \
+ tail "-n +${count}" | \
xargs -L1 rm -fv | \
while IFS= read -r line ; do
log_info "${line}"
I have no idea what's the process of adding packages to repos. If someone can point me where to start, I'll be glad to do the work.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.