pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about pg_wal.tar.gz generated by pg_basebackup
4+ messages / 3 participants
[nested] [flat]
* Question about pg_wal.tar.gz generated by pg_basebackup
@ 2023-05-10 08:41 Dhirendra Singh <dhirendraks@gmail.com>
2023-05-10 12:06 ` Re: Question about pg_wal.tar.gz generated by pg_basebackup Stephen Frost <sfrost@snowman.net>
2023-05-10 13:38 ` Re: Question about pg_wal.tar.gz generated by pg_basebackup Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Dhirendra Singh @ 2023-05-10 08:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi All,
I am taking backup using pg_basebackup for the purpose of point in time
recovery in case something goes wrong with the data.
The backup generates pg_wal.tar.gz which contains the wal file generated
during the backup.
Is it of any use in the point in time recovery? do i have to archive it?
According to the documentation, any wal files in pg_wal directory has to be
deleted after the base backup is restored and before the recovery starts.
So if i understand it correctly, pg_wal.tar.gz is not required for point in
time recovery...but just want to confirm if my understanding is correct.
My sole purpose of taking the backup is for point in time recovery. I am
not going to use it to create a standby server.
Thanks,
Dhirendra
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about pg_wal.tar.gz generated by pg_basebackup
2023-05-10 08:41 Question about pg_wal.tar.gz generated by pg_basebackup Dhirendra Singh <dhirendraks@gmail.com>
@ 2023-05-10 12:06 ` Stephen Frost <sfrost@snowman.net>
2023-05-10 15:32 ` Re: Question about pg_wal.tar.gz generated by pg_basebackup Dhirendra Singh <dhirendraks@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Stephen Frost @ 2023-05-10 12:06 UTC (permalink / raw)
To: Dhirendra Singh <dhirendraks@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Greetings,
* Dhirendra Singh (dhirendraks@gmail.com) wrote:
> I am taking backup using pg_basebackup for the purpose of point in time
> recovery in case something goes wrong with the data.
pg_basebackup works decently for this but it's pretty basic.
> The backup generates pg_wal.tar.gz which contains the wal file generated
> during the backup.
Yes, those are the WAL files needed to restore that backup.
> Is it of any use in the point in time recovery? do i have to archive it?
Yes and no ... if you're independently archiving your WAL then you don't
really need the WAL from pg_basebackup, but if you're not also archiving
your WAL then you absolutely must keep the WAL from the pg_basebackup.
There's an option in pg_basebackup which allows you to choose if you get
the WAL or not with pg_basebackup.
> According to the documentation, any wal files in pg_wal directory has to be
> deleted after the base backup is restored and before the recovery starts.
Strictly speaking the thing to do would be to put the WAL from
pg_basebackup somewhere and then have a restore command written that is
able to pull that WAL when PG asks for it. While the documentation says
that, we will still look in the WAL dir as the location of last restore
when doing replay so putting the WAL from pg_wal.tar.gz in there will
work too.
> So if i understand it correctly, pg_wal.tar.gz is not required for point in
> time recovery...but just want to confirm if my understanding is correct.
Archiving with archive_command (or using pg_receivewal) is required for
PITR.
> My sole purpose of taking the backup is for point in time recovery. I am
> not going to use it to create a standby server.
Have you configured an archive_command...? If not, you'll need to, to
get any proper PITR. What pg_basebackup is giving you is really a
single, but complete, backup but that will only restore to the end of
that backup, to go any farther you'll need the WAL that was written
after that backup was taken and to get that you need to set up an
archive command or use pg_receivewal.
Note that with pg_basebackup and pg_receivewal or some hand written
archive_command, you still need to write your own backup and WAL
retention code, when doing a restore figure out which backup to use for
the restore to get to the point in time you want, deal with
pg_basebackup and pg_receivewal being single-threaded, and a bunch of
other things that bespoke tooling like pgBackRest was written to
explicitly deal with.
You might save a lot of time by checking out pgBackRest instead,
especially for PITR.
Thanks,
Stephen
Attachments:
[application/pgp-signature] signature.asc (832B, ../../ZFuIt3T38oaYfxHE@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about pg_wal.tar.gz generated by pg_basebackup
2023-05-10 08:41 Question about pg_wal.tar.gz generated by pg_basebackup Dhirendra Singh <dhirendraks@gmail.com>
2023-05-10 12:06 ` Re: Question about pg_wal.tar.gz generated by pg_basebackup Stephen Frost <sfrost@snowman.net>
@ 2023-05-10 15:32 ` Dhirendra Singh <dhirendraks@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Dhirendra Singh @ 2023-05-10 15:32 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin@lists.postgresql.org
Hi Stephen,
Thanks for the reply.
I forgot to mention that i am also archiving the generated wal files. My
question was in this context.
According to your answers, it looks like it is not needed.
Thanks,
Dhirendra.
On Wed, May 10, 2023 at 5:36 PM Stephen Frost <sfrost@snowman.net> wrote:
> Greetings,
>
> * Dhirendra Singh (dhirendraks@gmail.com) wrote:
> > I am taking backup using pg_basebackup for the purpose of point in time
> > recovery in case something goes wrong with the data.
>
> pg_basebackup works decently for this but it's pretty basic.
>
> > The backup generates pg_wal.tar.gz which contains the wal file generated
> > during the backup.
>
> Yes, those are the WAL files needed to restore that backup.
>
> > Is it of any use in the point in time recovery? do i have to archive it?
>
> Yes and no ... if you're independently archiving your WAL then you don't
> really need the WAL from pg_basebackup, but if you're not also archiving
> your WAL then you absolutely must keep the WAL from the pg_basebackup.
> There's an option in pg_basebackup which allows you to choose if you get
> the WAL or not with pg_basebackup.
>
> > According to the documentation, any wal files in pg_wal directory has to
> be
> > deleted after the base backup is restored and before the recovery starts.
>
> Strictly speaking the thing to do would be to put the WAL from
> pg_basebackup somewhere and then have a restore command written that is
> able to pull that WAL when PG asks for it. While the documentation says
> that, we will still look in the WAL dir as the location of last restore
> when doing replay so putting the WAL from pg_wal.tar.gz in there will
> work too.
>
> > So if i understand it correctly, pg_wal.tar.gz is not required for point
> in
> > time recovery...but just want to confirm if my understanding is correct.
>
> Archiving with archive_command (or using pg_receivewal) is required for
> PITR.
>
> > My sole purpose of taking the backup is for point in time recovery. I am
> > not going to use it to create a standby server.
>
> Have you configured an archive_command...? If not, you'll need to, to
> get any proper PITR. What pg_basebackup is giving you is really a
> single, but complete, backup but that will only restore to the end of
> that backup, to go any farther you'll need the WAL that was written
> after that backup was taken and to get that you need to set up an
> archive command or use pg_receivewal.
>
> Note that with pg_basebackup and pg_receivewal or some hand written
> archive_command, you still need to write your own backup and WAL
> retention code, when doing a restore figure out which backup to use for
> the restore to get to the point in time you want, deal with
> pg_basebackup and pg_receivewal being single-threaded, and a bunch of
> other things that bespoke tooling like pgBackRest was written to
> explicitly deal with.
>
> You might save a lot of time by checking out pgBackRest instead,
> especially for PITR.
>
> Thanks,
>
> Stephen
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about pg_wal.tar.gz generated by pg_basebackup
2023-05-10 08:41 Question about pg_wal.tar.gz generated by pg_basebackup Dhirendra Singh <dhirendraks@gmail.com>
@ 2023-05-10 13:38 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Ron @ 2023-05-10 13:38 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 5/10/23 03:41, Dhirendra Singh wrote:
> Hi All,
> I am taking backup using pg_basebackup for the purpose of point in time
> recovery in case something goes wrong with the data.
> The backup generates pg_wal.tar.gz which contains the wal file generated
> during the backup.
> Is it of any use in the point in time recovery? do i have to archive it?
> According to the documentation, any wal files in pg_wal directory has to
> be deleted after the base backup is restored and before the recovery starts.
> So if i understand it correctly, pg_wal.tar.gz is not required for point
> in time recovery...but just want to confirm if my understanding is correct.
>
> My sole purpose of taking the backup is for point in time recovery. I am
> not going to use it to create a standby server.
Use a dedicated tool instead of writing your own. I like PgBackRest, but
there are others.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-05-10 15:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-10 08:41 Question about pg_wal.tar.gz generated by pg_basebackup Dhirendra Singh <dhirendraks@gmail.com>
2023-05-10 12:06 ` Stephen Frost <sfrost@snowman.net>
2023-05-10 15:32 ` Dhirendra Singh <dhirendraks@gmail.com>
2023-05-10 13:38 ` Ron <ronljohnsonjr@gmail.com>
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