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 Mon, Nov 1, 2021 at 10:43 PM Mladen Gogala <gogala.mla...@gmail.com> wrote: > > 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 > > > >
