Re: Backup From HotStandby

Rui,

I have been using a lvm snapshot, all the database files are on the same
disk.
My script start the pg_start_backup() make a lvm snapshot stop the backup
and then copy the files do another filesystem from the snap and after to
the tape.

Tks.

*Licio Matos*


Em qua, 28 de ago de 2019 às 16:24, Rui DeSousa <[email protected]>
escreveu:

>
>
> On Aug 28, 2019, at 2:40 PM, Licio Matos <[email protected]> wrote:
>
> Tks Jeff,
>
> But in this case i am not using pg_basebackup, but i make
> pg_start_backup() and pg_stop_backup() in the script and make a snapshot.
> So, i believe this work too, right?
>
> Tks again.
>
> *Licio Matos*
>
> Em qua, 28 de ago de 2019 às 15:24, Jeff Janes <[email protected]>
> escreveu:
>
>> On Wed, Aug 28, 2019 at 1:42 PM Licio Matos <[email protected]>
>> wrote:
>>
>>> Hi Guys,
>>>
>>> Is it possible to make a backup snapshot from a hotstandby (slave) that
>>> is receiving WAL stream from a master on postgresql 9.6?
>>>
>>
>>  Yes, but with some restrictions.  See the docs for your version (
>> https://www.postgresql.org/docs/9.6/app-pgbasebackup.html) starting with
>> "pg_basebackup can make a base backup from not only the master but also the
>> standby."
>>
>> Cheers,
>>
>> Jeff
>>
>
> Licio,
>
> Yes, you can make a backup using a snapshot given that you can assure it
> is an atomic operation across the entire PostgreSQL datafiles.  What
> snapshot technology are you using and how is your datafiles layed out?
>
> If Postgres resides on a single volume and a snapshot is taken of that
> volume then the result would be a crash recovery backup.  There is no need
> to run pg_start_backup() in this case nor can it be run on a replica.  I’ve
> been doing this type of backups for a better part of decade with great
> success.  The benefit is zero impact on the primary database and multiple
> backups can be taken throught the day to reduce recovery times in a large
> and high volume database.
>
> If you can’t guarantee an atomic operations across all the datafiles then
> you need to use a different method.  i.e. shutdown the replica, take the
> snapshot backups, and start the replica again.
>
>