Обсуждение: Backup From HotStandby

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

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.

> On Aug 28, 2019, at 3:31 PM, Licio Matos <licio.matos@gmail.com> wrote:
>
> I have been using a lvm snapshot, all the database files are on the same disk.

Sounds good; note that the filesystem will also need recovery thus you may want to consider trying to quiesce the filesystem before creating the snapshot to avoid filesystem issues.   


> 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.

Hmm… are you running that on the replica? It should fail; unless you’re running it on the primary.

I wouldn’t bother with pg_start_backup() to take a snapshot backup on the replica.  When recovering, the database will first do its crash recovery and then apply any WAL files needed to reach your recovery target time.

On Aug 28, 2019, at 4:11 PM, Licio Matos <licio.matos@gmail.com> wrote:

@Rui DeSousa 

I am running this on the primary, the question is if i can run this same script on the replica.

You can based on what you told me about the snapshot; however, the pg_start_backup() command will fail.  

Note: What you are doing today most would consider it a “proper” online backup; when you start running the script on the replica then you will end up with a “crashed” backup.   The “crashed” backup only works as you can guarantee a point in time atomic view of all the data files using a single atomic snapshot.

You need to remove the pg_start_backup() and pg_stop_backup() commands from the script.   The backup should occur faster as it is no longer waiting on pg_start_backup() to issue checkpoint; however, that also means that recovery times increase for the same reason.