I haven't administered such a system, but I have good experience with Veeam. Deleted the wrong SQL table by accident? Absolutely no problem to restore it without even needing to reboot any server or restart services. There are countless option for import/export that Veeam provides.
We also use Nimble for snapshots every 15 minutes on some servers, although not our databases afaik. Pretty effective if a worst case ransom attack was successful on a user with too many access rights.
These solutions aren't cheap though.
Privately I just use multiple hard drives and for database servers I would use the internal backup functionalities, which are pretty good on most databases.
Veeam is really beast when it comes to Microsoft Tech... they even are able to recover mails back to users Inbox in Exchange directly on the server. But very expensive, yes.
We leave backing up of the VMs themselves to our infrastructure team, but we also make sure that we have logical backups of our databases, so worst case we can restore our databases to a known point.
Our local files can either be pulled from git repositories or rebuilt by running an Ansible playbook against the server. Luckily we don't have an user uploaded files on our servers to worry about.
To protect both your VMs and the critical data inside them it's possible to use Xopero Software. The solution helps to ensure flexibility and resilience against data loss.
For me, it's case-by-case. I don't back up the VMs directly, just the data of the stateful applications running on the VMs (or bare metal servers, I do identical stuff for them).
For postgres, I used to just have a systemd timer that would `pg_dumpall` and throw it in s3.
For other local files, I use borg backup for personal files and services I just run for myself, and I use restic to backup server files to s3.
The operating system's configuration is all stored in git via the magic of NixOS, so I don't have to worry about files in /etc, they all are 100% reproducible from my NixOS configuration.
Edited to wal-g, since that is what I'm using now; I forgot I had to switch over, but indeed I did.
Did you jump straight to pgbackrest or compare it to wal-g? I didn't compare em at all, so I have no clue if I'm missing out on something nice or important there
I think the next few years it will not be dead, there are a lot of instances who can not easily be ported to 17. But yeah, i also read about that new feature a few days ago.
Yeah i can also recommend, i even use it to push incremental backups to ssh (nas is to old to install minio), other servers use s3.
I have it included in timescale image.. so full restore is: spinning up a temporary image, restore, restart compose. So do i with with incremental. File based restore is pretty fast.
For me its a setup and forget thing.
For etc i use gitkeep sometimes (machines who are also configured by devs itself), to have an audit like thing, with a central gitea repo in the project cluster to see changes.
For files i use restic to ssh, also s3 possible. i like the deduplication they do.
And otherwise i use proxmox, doing snapshots on HDDs at night, and at the day i move those sliwly to external nas again, keeping the last few snaps on ssd,hdd to be able to recover fast. and if the machine got dead, i have the external backups.
I also do this the same way on hetzner with my bare metals, storing an encrypted backup (restic deduped and encrypted) to external storage. Server is also proxmox with encrypted disks, so to boot up, you need to send encryption keys via secure ssh.
Also another aproach with configs is, having a clean deployment/infra config (ansible, pyinfra, terraform...), and then only restoring data, so everything is reproducable and consistent.
We don’t use mySQL / Maria but I wouldn’t bother backing up OS files every time so you should have a script to boot up a fresh VM instance and restore data at the db level- perhaps use replication if you need fast RTO. This is a fairly solved problem as far as databases are concerned. If you run on AWS or can afford at least 3 nodes running you can even avoid downtime altogether.
I don't, really. I have a text file on GitHub explaining how I set them up, and occasionally I'll grab a tarball of /etc on the more complicated ones. Data worth preserving doesn't live on the VM-- that's what NFS is for.
I have a VM running Windows Server that hosts MS SQL Server and websites under IIS. I use Robocopy and PowerShell to copy daily and weekly file backups, as well as SQL backup scripts for the databases. The backups are then stored on a separate drive, which has a nightly copy to remote storage and deletes files older than 30 days. Occasionally, I manually copy the backups from remote storage to a local computer.
It only takes a minute to restore if needed, but the problem is if the OS goes down (which has happened once in the last 10 years due to a disk failure), it takes several hours to reinstall everything from scratch.
PBS. Works great (given that you are using proxmox) and it supports file recovery from individual VMs. For individual server VMs (which are not in proxmox), I use restic
If you use elastic block storage in the cloud, many times they will let you do snapshots and backups of that.
If you can't do that, make a script to make a .tgz backup and have cron do it every day and copy it up to an s3 bucket. Have the s3 bucket configured to delete items that are over a certain age.
But what you need to consider often is, having a consistent state in those snapshots, this can be a nasty thing for examble with all sorts of databases or configuration as database (boltdb, sqlite,...)
Answer to this will totally depend on the scenario. Figure out what your RPO and RTO is on each thing and build out backups and failovers accordingly. If this is anything but a personal lab, you will likely need both db level backups (ie get this db back to this time) and vm level (the datacenter went down, good thing we have all our vms backed up offsite). Veeam comes to mind and one tool I’ve seen work well. Backup services built into cloud providers is what I use for most things these days
ZFS Offsite Backup configured using Syncoid [1] in pull mode. My backup server is started weekly using a pre-configured Shelly Plug. After boot, it connects to the main server, pulls latest ZFS Snapshots, does a ZFS scrub, sends an email for confirmation to me, and shuts down. The Shelly Plug-S is configured in a Home Assistant automation to shut off power completely if under 10W for 10 Minutes.
The advantage of this setup is that the Backup server does not need to know the encryption keys for ZFS. It can pull snapshots without knowing keys (i.e. zero trust). The main server also cannot reach the Backup server, only the other way around works (configured in opnsense that connects my offsite backup via IPSEC). The backup server is locked down in its own subnet and can only be reached from a few selected points. This is possible because there is no interaction needed due to the Shelly Plug S self-starting automation.
ZFS also doesn't care about filesystems (etc.) - it can incrementally pull ext4/xfs filesystems of VMs, without touching individual files or the need for individual file hashes (such as with rsync).
environment: KVM VMs running on physical hardware managed by us.
we have a belt & suspenders approach:
* backups of selected files / database dumps [ via dedicated tools like mysqldump or pg_dumpall ] from within VMs
* backups of whole VMs
backups of whole VMs are done by creating snapshot files via virsh snapshot-create-as, rsync followed by virsh blockcommit. this provides crash-consistant images of the whole virtual disks. we zero-fill virtual disks before each backup.
all types of backups later go to borg backup[1] [ kopia[2] would be fine as well ] deduplicated, compressed repository.
this approach is wasteful in terms of bandwidth, backup size but gives us peace of mind - even if we miss to take file-level backup of some folder - we'll have it in the VM-level backups.
Databases have their normal backup tools, which are fine for the data. Important intense-load databases can have one-way replication set up to a separate box that can do proper point-in-time backups without slowing production. Raw (uncompressed) backup data would be efficiently turned into incremental / full backup mix with something like Restic or Kopia, and shipped to something like Backblaze or S3 Glacier.
Configuration and other stuff lives outside the VMs, in some git repo, in the form of terraform / saltstack / shell scripts / what have you, that can rebuild the VM from scratch, and does it regularly. Git has very good built-in "backup tools".
60 comments
[ 2.2 ms ] story [ 134 ms ] threadWe also use Nimble for snapshots every 15 minutes on some servers, although not our databases afaik. Pretty effective if a worst case ransom attack was successful on a user with too many access rights.
These solutions aren't cheap though.
Privately I just use multiple hard drives and for database servers I would use the internal backup functionalities, which are pretty good on most databases.
Our local files can either be pulled from git repositories or rebuilt by running an Ansible playbook against the server. Luckily we don't have an user uploaded files on our servers to worry about.
For postgres, I used to just have a systemd timer that would `pg_dumpall` and throw it in s3.
Now I use https://github.com/wal-g/wal-g to backup my postgresql databases.
For other local files, I use borg backup for personal files and services I just run for myself, and I use restic to backup server files to s3.
The operating system's configuration is all stored in git via the magic of NixOS, so I don't have to worry about files in /etc, they all are 100% reproducible from my NixOS configuration.
Did you jump straight to pgbackrest or compare it to wal-g? I didn't compare em at all, so I have no clue if I'm missing out on something nice or important there
I have it included in timescale image.. so full restore is: spinning up a temporary image, restore, restart compose. So do i with with incremental. File based restore is pretty fast.
For me its a setup and forget thing.
For etc i use gitkeep sometimes (machines who are also configured by devs itself), to have an audit like thing, with a central gitea repo in the project cluster to see changes.
For files i use restic to ssh, also s3 possible. i like the deduplication they do.
And otherwise i use proxmox, doing snapshots on HDDs at night, and at the day i move those sliwly to external nas again, keeping the last few snaps on ssd,hdd to be able to recover fast. and if the machine got dead, i have the external backups.
I also do this the same way on hetzner with my bare metals, storing an encrypted backup (restic deduped and encrypted) to external storage. Server is also proxmox with encrypted disks, so to boot up, you need to send encryption keys via secure ssh.
Also another aproach with configs is, having a clean deployment/infra config (ansible, pyinfra, terraform...), and then only restoring data, so everything is reproducable and consistent.
wrote a elaborate doc on using wal-g on nixos, might be useful if someone finds themselves in same boat. has some catches.
https://github.com/geekodour/nix-postgres-docker
It only takes a minute to restore if needed, but the problem is if the OS goes down (which has happened once in the last 10 years due to a disk failure), it takes several hours to reinstall everything from scratch.
Working storage is a pair of n TB SSD in RAID0 where the VMs live. Nightly backups are stored there.
A single n*2 TB disk for backups and long term media.
When I lifecycle disks, I move them to my desktop which just has a cronjob to scp from server storage.
If you can't do that, make a script to make a .tgz backup and have cron do it every day and copy it up to an s3 bucket. Have the s3 bucket configured to delete items that are over a certain age.
The advantage of this setup is that the Backup server does not need to know the encryption keys for ZFS. It can pull snapshots without knowing keys (i.e. zero trust). The main server also cannot reach the Backup server, only the other way around works (configured in opnsense that connects my offsite backup via IPSEC). The backup server is locked down in its own subnet and can only be reached from a few selected points. This is possible because there is no interaction needed due to the Shelly Plug S self-starting automation.
ZFS also doesn't care about filesystems (etc.) - it can incrementally pull ext4/xfs filesystems of VMs, without touching individual files or the need for individual file hashes (such as with rsync).
[1]: https://github.com/jimsalterjrs/sanoid/blob/master/syncoid
[1] https://github.com/digint/btrbk
Hallowed be Thy DB_name
we have a belt & suspenders approach:
* backups of selected files / database dumps [ via dedicated tools like mysqldump or pg_dumpall ] from within VMs
* backups of whole VMs
backups of whole VMs are done by creating snapshot files via virsh snapshot-create-as, rsync followed by virsh blockcommit. this provides crash-consistant images of the whole virtual disks. we zero-fill virtual disks before each backup.
all types of backups later go to borg backup[1] [ kopia[2] would be fine as well ] deduplicated, compressed repository.
this approach is wasteful in terms of bandwidth, backup size but gives us peace of mind - even if we miss to take file-level backup of some folder - we'll have it in the VM-level backups.
[1]: https://www.borgbackup.org/
[2]: https://kopia.io/
Configuration and other stuff lives outside the VMs, in some git repo, in the form of terraform / saltstack / shell scripts / what have you, that can rebuild the VM from scratch, and does it regularly. Git has very good built-in "backup tools".
What else needs backing up in a DB server?