I'm looking to make very frequent backup (every hour) of postgres data on several VMs (say 20-50) towards the same archive server.
Here are more data if needed: Ideally, the system should support the load of 80 to 200 databases located on all VMs. The databases are small (10MB - 100MB) to medium sized (500MB - 2GB), composed of hundredth of tables, a small portion of these tables can easily contain several thousands of lines up to around a million of lines. Changes to the database is often new records, some updates, not so much deletion. The bandwidth would be 100Mbits/s.
As I'm already doing so with a standard filesystem using incremental backup (
rsync), I'm wondering if something similar could be achieved with postgres database backups.
I have several possible options:
- I could choose to put database on snapshotable filesystem (
btrfs, but some of these seems really slowing down postgres).
- I'm ready to use WAL if necessary
- It would be better if I can backup only at the database level if necessary. As I do not need to backup the whole postgres data, only the customers databases.
- I have some disk space on the postgres server that could keep an intermediate backup.
- I can afford some reasonable CPU workload on the VM side, but would rather minimize it on the backup server as it'll add up the more database to backup there will be.
- I'm not really looking for continuous backup or PITR recovery options. My backup server has a file based system (brfs) to do efficient periodic snapshots of backups. It's good enough.
I've thought about:
rsyncin combination with
pg_dumplocally to the server in SQL, but I'm not sure which of the different format I should use to keep maximum efficiency.
- using snapshotable filesystem that allows to send binary diffs at the block level (btrfs and ZFS are good at it) with or without using a local dump (same question about the backup format to use).
- I've learned about the existence of pg_rman, I don't really know if it can be relied upon, and the setup and various process seems slightly heavier than
pg_dump. Would it support having only incremental backups ? And can we have a practical format on the backup side ?.
and is there another way than incremental backups to reach small bandwith ?
So... how could I minize bandwith in my postgres backup scenario ?