Re: Postgresql backup via LVM snapshot?

Are you trying to fit the postgres backups in some overarching LVM-based
backup scheme you're already running or is this from scratch?


With the possible exception of rsync's deltas & hardlink trickeries
between full backups, I personally don't see a point in using a
block/file-level copy for physical backups of Postgres clusters (unless
you're keeping the snapshot around as the "thin backup" but that would
put the backup in the same volume group as the live data; meh).


pg_basebackup (or more specifically the underlying replication protocol
command BASE_BACKUP), precisely wraps up all that you're trying to
accomplish here; it is perfectly aware of the internal state of
operations and guarantees operations
synchronisation/correctness/completeness.


It roughly boils down to (with minor variations between types of
physical backups):

- dropping a checkpoint and wait for it to complete, or wait for the
next one to roll around (that will be the starting point of WAL recovery
when you restore)
- remember the WAL location of that checkpoint
- copy the data files "as is" without worrying about torn/corrupt pages
due to changes. They will be overwritten with the amended version during
WAL recovery
- optionally put away a copy of all the transaction logs that were
generated between the checkpoint and the end of the copy if you're not
already performing WAL archival (or if you simply want your backup to be
self-contained and recoverable without accessing the WAL archive).



The database will look to have crashed when you initially restore it
(same as with an any form of snapshotting including LVM) and will have
to go through WAL recovery anyway: the earliest version of the DB to
which you can safely restore is the one at the moment the copy ended, so
the benefits of the atomic low-level snapshot are negated.



By using the old exclusive start/stop_backup() & action synchronisation
yourself you're just making it more manual and error prone.



Regards


F




On 30/05/2019 20:38, 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.

--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--


Notice:  This email is confidential and may contain copyright material of
members of the Ocado Group. Opinions and views expressed in this message
may not necessarily reflect the opinions and views of the members of the
Ocado Group. 

 

If you are not the intended recipient, please notify us
immediately and delete all copies of this message. Please note that it is
your responsibility to scan this message for viruses. 

 

Fetch and Sizzle
are trading names of Speciality Stores Limited and Fabled is a trading name
of Marie Claire Beauty Limited, both members of the Ocado Group.

 


References to the “Ocado Group” are to Ocado Group plc (registered in
England and Wales with number 7098618) and its subsidiary undertakings (as
that expression is defined in the Companies Act 2006) from time to time.  
The registered office of Ocado Group plc is Buildings One & Two, Trident
Place, Mosquito Way, Hatfield, Hertfordshire, AL10 9UL.