Re: ZFS filesystem - supported ?

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(dot)mladen(at)gmail(dot)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
>
>
>
>