I was just looking into continously backing up a postgresql database (too big to make dumps constantly).
Since I’ve already got a urBackup installation I also looked into how good it would support that.
As it seems there are already some scripts: https://www.urbackup.org/backup_postgresql.html .
Is anybody using it? And has experience with it?
The plan would be to make a backup every few minutes either by using a cronjob to start the backup from the client side, or setting the interval for incremental file backups to a fraction (the gui seems to accept values like 0.1).
Would that be working?
Or would I be better off using a dedicated solution like barman (I’m inclining to that so far).
That would work. Of course setting the interval lower than the frequency with which wal files are produced wouldn’t make sense. I guess you could also add the backup start command to the wal archival command in the posgres config.
The described backup procedure is close to optimal. Even the “full” backups (basebackup) can be stored incrementally by UrBackup (on btrfs, XFS or ReFS). I can’t compare it to barman, though.
Thansk a lot for your answer.
I compared the two solutions. About doing the continous wal-backup they seem to be equal. By using also the archive_timeout parameter one can guarantee, that e.g. every x minutes a new wal file is written. So one would loose maximum x minutes of data.
What Barman supports and Urbackup doesn’t, is wal-streaming. Means the wal-data are asynchronously streamed from the postgresql server to barman. That means barman doesn’t need to wait until wal-files are written and copied. So almost 0 dataloose can be guaranteed (Assuming the network connection is fast enough).
Anyway, since for my use case it’s good enough to max. loose a few minutes of data. I’ll try out your suggestion and add the backup start command to the postgres config. Like that also all backups are centralized in only one program.
When I have enough time, I’ll try to achieve the same with MS Sql Server…
Just a few comments to the guide: https://www.urbackup.org/backup_postgresql.html#postgresql_basebackup:
The command should be:
urbackupclientctl set-settings -k virtual_clients -v wal
and not
urbackupclientctl set-settings -v virtual_clients -k wal
wal_level archive doesn’t exist in newer postgresql versions. There it is called “replica”.
Otherwise the guide seems to be working.
I’ve also tried out the file
/var/local/share/urbackup/scripts/setup-postgresbackup
it uses /bin/sh as interpreter which on debian by default is dash.
There the command
mkdir -p /var/lib/walarchive/{incoming,staging,backup}
will create a directory called
“{incoming,staging,backup}”
Propbly it would be easiest to change the interpreter to /bin/bash
Just one more thing that should be mentioned in the guide.
With the prefilebackup and especially postfilebackup script, there should not be any other virtual client than wal be used:
# Argument one null means main client
if [ $1 = 0 ]
then
urbackupclientctl reset-keep -v wal
elif exists /var/lib/walarchive/backup/*
then
rm /var/lib/walarchive/backup/*
If I understand that script right, everything in /var/lib/walarchive/backup/* will be deleted. Doesn’t matter if the virtual client was “wal” or any other. That could lead to quite unexpected results if it is not mentioned explicitly.