pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBackup From HotStandby
10+ messages / 4 participants
[nested] [flat]
* Backup From HotStandby
@ 2019-08-28 17:34 Licio Matos <licio.matos@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Licio Matos @ 2019-08-28 17:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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*
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup From HotStandby
@ 2019-08-28 18:24 Jeff Janes <jeff.janes@gmail.com>
parent: Licio Matos <licio.matos@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jeff Janes @ 2019-08-28 18:24 UTC (permalink / raw)
To: Licio Matos <licio.matos@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup From HotStandby
@ 2019-08-28 18:40 Licio Matos <licio.matos@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Licio Matos @ 2019-08-28 18:40 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup From HotStandby
@ 2019-08-28 19:24 Rui DeSousa <rui@crazybean.net>
parent: Licio Matos <licio.matos@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Rui DeSousa @ 2019-08-28 19:24 UTC (permalink / raw)
To: Licio Matos <licio.matos@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-admin@lists.postgresql.org
> 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 <mailto:jeff.janes@gmail.com>> escreveu:
> On Wed, Aug 28, 2019 at 1:42 PM Licio Matos <licio.matos@gmail.com <mailto: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 <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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup From HotStandby
@ 2019-08-28 19:31 Licio Matos <licio.matos@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 10+ messages in thread
From: Licio Matos @ 2019-08-28 19:31 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-admin@lists.postgresql.org
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.
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup From HotStandby
@ 2019-08-28 19:33 Stephen Frost <sfrost@snowman.net>
parent: Licio Matos <licio.matos@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Stephen Frost @ 2019-08-28 19:33 UTC (permalink / raw)
To: Licio Matos <licio.matos@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-admin@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup From HotStandby
@ 2019-08-28 20:09 Rui DeSousa <rui@crazybean.net>
parent: Licio Matos <licio.matos@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Rui DeSousa @ 2019-08-28 20:09 UTC (permalink / raw)
To: Licio Matos <licio.matos@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-admin@lists.postgresql.org
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup From HotStandby
@ 2019-08-28 20:11 Licio Matos <licio.matos@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 10+ messages in thread
From: Licio Matos @ 2019-08-28 20:11 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-admin@lists.postgresql.org
@Rui DeSousa <rui@crazybean.net>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup From HotStandby
@ 2019-08-28 20:38 Rui DeSousa <rui@crazybean.net>
parent: Licio Matos <licio.matos@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Rui DeSousa @ 2019-08-28 20:38 UTC (permalink / raw)
To: Licio Matos <licio.matos@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-admin@lists.postgresql.org
> On Aug 28, 2019, at 4:11 PM, Licio Matos <licio.matos@gmail.com> wrote:
>
> @Rui DeSousa <mailto:rui@crazybean.net>
>
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup From HotStandby
@ 2019-08-28 20:59 Licio Matos <licio.matos@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 0 replies; 10+ messages in thread
From: Licio Matos @ 2019-08-28 20:59 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-admin@lists.postgresql.org
@Rui DeSousa <rui@crazybean.net>
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 <rui@crazybean.net>
>
> 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.
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2019-08-28 20:59 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-08-28 17:34 Backup From HotStandby Licio Matos <licio.matos@gmail.com>
2019-08-28 18:24 ` Jeff Janes <jeff.janes@gmail.com>
2019-08-28 18:40 ` Licio Matos <licio.matos@gmail.com>
2019-08-28 19:24 ` Rui DeSousa <rui@crazybean.net>
2019-08-28 19:31 ` Licio Matos <licio.matos@gmail.com>
2019-08-28 20:09 ` Rui DeSousa <rui@crazybean.net>
2019-08-28 20:11 ` Licio Matos <licio.matos@gmail.com>
2019-08-28 20:38 ` Rui DeSousa <rui@crazybean.net>
2019-08-28 20:59 ` Licio Matos <licio.matos@gmail.com>
2019-08-28 19:33 ` Stephen Frost <sfrost@snowman.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox