Right... which is why you do pg_basebackup infrequently. It also captures WALs when conducting the backup. The tradeoff is that if you have a huge amount of WAL files then running a restore can be quite time-consuming. There isn't really a clear win here though. You trade off a long time backup that will restore everything to that exact moment in time or you trade off a restore that could take an extremely long time in the case where you just keep WALs and do a base backup monthly. Worst case scenario is that you need to restore an hour before a scheduled base backup so you get to go through every WAL to that point. Snapshots are fine. We also just pg_dump and tar+bz2 the result. You can even do that on a remote machine. It is probably a crude solution but it works.
But again, the OP should probably be paying for a service and not relying on a message board for something like this. I guarantee that Postgres owners know what to do for this case and have a selection of best practices because they should and need to or you hire a professional DBA to step in and tell you what they found works the best.
I absolutely loved the response of ZFS because the clear winner when ZFS and ext4 performed the benchmark was nearly the same. Now you get to deal with a non-default file system for a gain that should not be noticeable unless you are really trying to use every single last cycle on your computer. If you are, I highly recommend getting the paid support and hiring a crack team of DBAs who can install your servers using complex distrubutions.
Thanks,
~Ben
On 11/1/21 17:58, Stephen Frost wrote:
> Well, at least one alternative to performing these snapshots would be to
> use a tool like pg_basebackup or pgbackrest to perform the backups
> instead.
File system based backups are much slower than snapshots. The
feasibility of file based utility like pg_basebackup depends on the size
of the database and the quality of the infrastructure. However, if
opting for snapshot based solutions, I would advise something like Pure
or NetApp which use special hardware to accelerate the process and have
tools to backup snapshots, like SnapVault (NetApp).
Also, when using file level utilities, I would go full commercial.
Commercial backup utilities are usually optimized for speed, support
deduplication and maintain backup catalog, which can come handy if there
are regulatory requirements about preserving your backups (HIPPA, SOX)
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com