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

Поиск
Список
Период
Сортировка

Backup From HotStandby

От
Licio Matos
Дата:
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?

Tks.
Licio Matos

Re: Backup From HotStandby

От
Jeff Janes
Дата:
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

Re: Backup From HotStandby

От
Licio Matos
Дата:
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

Re: Backup From HotStandby

От
Rui DeSousa
Дата:


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.

Re: Backup From HotStandby

От
Licio Matos
Дата:
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 <rui@crazybean.net> escreveu:


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.

Re: Backup From HotStandby

От
Stephen Frost
Дата:
Greetings,

* Licio Matos (licio.matos@gmail.com) wrote:
> 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?

No, that doesn't work.

The pg_basebackup method does.

Thanks,

Stephen

Вложения

Re: Backup From HotStandby

От
Rui DeSousa
Дата:

> 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
filesystembefore 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
filesystemfrom 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
firstdo its crash recovery and then apply any WAL files needed to reach your recovery target time. 


Re: Backup From HotStandby

От
Licio Matos
Дата:
@Rui DeSousa 

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

Tks
Licio Matos 




Em qua, 28 de ago de 2019 às 17:09, Rui DeSousa <rui@crazybean.net> escreveu:


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

Re: Backup From HotStandby

От
Rui DeSousa
Дата:


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.  

Re: Backup From HotStandby

От
Licio Matos
Дата:
@Rui DeSousa 

Tks again, i will try this aproach or alter the script to do with the pg_basebackup.

Tks a lot guys.

Licio Matos




Em qua, 28 de ago de 2019 às 17:38, Rui DeSousa <rui@crazybean.net> escreveu:


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.