Re: Postgresql backup via LVM snapshot?

Hi,

I suggest you to take a serious look at ZFS: https://zfsonlinux.org/

With simply a few commands, mainly zpool and zfs, you can manage
everything in one stack:

* encryption: new in 0.8.0
* compression: with lz4, it's very lightweitgh with practically no
impact on cpu
* snapshots: it uses copy on write, no messing with complicated lvm
snapshot management. Instantaneous snapshots. And as a bonus you can
easily copy snapshots between hosts.
* arc, l2arc: better cache management than page cache on linux, not
simply an LRU
* it handles mount point for you

I don't see any problems to you procedure, just keep your generated WALs
during the backup procedure (pg-start-backup to pg-stop-backup).

Generally, you'll want to separate WAL directory from the data one.

Because I can't cover more in a simple email (tuning, parameters,
disable pg full page writes, disable pg page checksuming, etc.), here
are good resources:

* https://www.2ndquadrant.com/en/blog/pg-phriday-postgres-zfs/
* https://www.slideshare.net/SeanChittenden/postgresql-zfs-best-practices
* https://www.slideshare.net/SeanChittenden/postgres-on-zfs-lightning-talk
* From Oracle for Oracle, but it can helps:
o https://docs.oracle.com/cd/E36784_01/html/E36845/chapterzfs-db2.html
o https://docs.oracle.com/cd/E53394_01/html/E54818/chapterzfs-1.html#scrolltoc
o https://www.oracle.com/technetwork/server-storage/solaris10/config-solaris-zfs-wp-167894.pdf

Maybe someone else can share experience with PG + ZFS ?

Hope that helps
Bruno Lavoie

On 2019-05-30 3:38 p.m., Lu, Dan wrote:
>
> Hello,
>
> Would you be able to confirm for me that a host level LVM snapshot of
> the PGDATA directory along with PG_WAL directly via LVM backup is
> supported way of backup and restore?
>
> I read about this here:
> https://dba.stackexchange.com/questions/145361/backup-standby-database-using-lvm-snapshot
>
>
> Is it as simple as:
>
> èSELECT pg_start_backup('Begin LVM Backup At xyz');
>
> èDo LVM backup from o/s end to backup PGDATA/PG_WAL
>
> èSELECT pg_stop_backup();
>
> Thanks.
>
> Dan
>
>
> ------------------------------------------------------------------------
>
> IMPORTANT: The information contained in this email and/or its
> attachments is confidential. If you are not the intended recipient,
> please notify the sender immediately by reply and immediately delete
> this message and all its attachments. Any review, use, reproduction,
> disclosure or dissemination of this message or any attachment by an
> unintended recipient is strictly prohibited. Neither this message nor
> any attachment is intended as or should be construed as an offer,
> solicitation or recommendation to buy or sell any security or other
> financial instrument. Neither the sender, his or her employer nor any
> of their respective affiliates makes any warranties as to the
> completeness or accuracy of any of the information contained herein or
> that this message or any of its attachments is free of viruses.