Re: Backup From HotStandby

On Aug 28, 2019, at 2:40 PM, Licio Matos <licio.matos@gmail.com> 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 <jeff.janes@gmail.com> escreveu:

On Wed, Aug 28, 2019 at 1:42 PM Licio Matos <licio.matos@gmail.com> 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.