Обсуждение: pg_basebackup application does not dump all database objects

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/app-pgbasebackup.html
Description:

Hello!

It would be good to point out on the pg_basebackup documentation that it
behaves differently from

SELECT * FROM pg_start_backup('label', true, false);
cp -a xxx yyy
SELECT * FROM pg_stop_backup(false, true);

method. One thing discovered was that the latter retains replication slot
information on the clone while pg_basebackup discards all replication slot
information. Not sure if it is a feature or a bug (replication slots
retention is a good thing).

And perhaps there are more such subtle differences?

With best regards,

Kristjan Mustkivi

Greetings,

* PG Doc comments form (noreply@postgresql.org) wrote:
> It would be good to point out on the pg_basebackup documentation that it
> behaves differently from
>
> SELECT * FROM pg_start_backup('label', true, false);
> cp -a xxx yyy
> SELECT * FROM pg_stop_backup(false, true);

... that's not actually a backup.  Doing the above, you won't get a
backup_label and unless you take other steps, PG will end up thinking
it's doing crash recovery.  That's not good and can lead to corruption.
If you're going to consider taking a low-level backup you should read
the documentation here:

https://www.postgresql.org/docs/15/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP

Which specifically goes into the question about replication slots.

> method. One thing discovered was that the latter retains replication slot
> information on the clone while pg_basebackup discards all replication slot
> information. Not sure if it is a feature or a bug (replication slots
> retention is a good thing).

Our documentation points out a number of reasons why it's not, I'd
suggest you read the above.

> And perhaps there are more such subtle differences?

Not sure that there's really that many other differences between
pg_basebackup and a properly implemented low-level backup.  You
certainly have to do a lot more than what you have above to have a
properly implemented low-level backup though.

Thanks,

Stephen

Hi Stephen,

Thank you very much for the feedback and a reference! I was under the
impression that pg_basebackup does everything
pg_start_backup/pg_stop_backup but with a more user-friendly manner
and additional steps. But they are then functionally different. For my
particular case the pg_start_backup/pg_stop_backup approach was better
as it retained the replication slots as well (the end goal was to move
the db from old hardware to the new one and have it on the new hw
exactly as it was on the old one). So I was surprised that
pg_basebackup did not bring the rep slots along.

With best regards,

Kristjan

On Tue, May 16, 2023 at 5:14 PM Stephen Frost <sfrost@snowman.net> wrote:
>
> Greetings,
>
> * PG Doc comments form (noreply@postgresql.org) wrote:
> > It would be good to point out on the pg_basebackup documentation that it
> > behaves differently from
> >
> > SELECT * FROM pg_start_backup('label', true, false);
> > cp -a xxx yyy
> > SELECT * FROM pg_stop_backup(false, true);
>
> ... that's not actually a backup.  Doing the above, you won't get a
> backup_label and unless you take other steps, PG will end up thinking
> it's doing crash recovery.  That's not good and can lead to corruption.
> If you're going to consider taking a low-level backup you should read
> the documentation here:
>
> https://www.postgresql.org/docs/15/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
>
> Which specifically goes into the question about replication slots.
>
> > method. One thing discovered was that the latter retains replication slot
> > information on the clone while pg_basebackup discards all replication slot
> > information. Not sure if it is a feature or a bug (replication slots
> > retention is a good thing).
>
> Our documentation points out a number of reasons why it's not, I'd
> suggest you read the above.
>
> > And perhaps there are more such subtle differences?
>
> Not sure that there's really that many other differences between
> pg_basebackup and a properly implemented low-level backup.  You
> certainly have to do a lot more than what you have above to have a
> properly implemented low-level backup though.
>
> Thanks,
>
> Stephen



--
Kristjan Mustkivi

Email: kristjan.mustkivi@gmail.com

Greetings,

Please don't top-post on these lists.

* Kristjan Mustkivi (sonicmonkey@gmail.com) wrote:
> Thank you very much for the feedback and a reference! I was under the
> impression that pg_basebackup does everything
> pg_start_backup/pg_stop_backup but with a more user-friendly manner
> and additional steps. But they are then functionally different. For my
> particular case the pg_start_backup/pg_stop_backup approach was better
> as it retained the replication slots as well (the end goal was to move
> the db from old hardware to the new one and have it on the new hw
> exactly as it was on the old one). So I was surprised that
> pg_basebackup did not bring the rep slots along.

pg_basebackup does effectively call pg_start_backup/pg_stop_backup (or
pg_backup_start/pg_backup_stop in newer versions of PG..) and is more
user-friendly and does do the additional steps expected of a low level
backup- and which are documented in the link that I sent you.

Again, it's not enough to just call pg_start_backup and pg_stop_backup
and do a 'cp -a', and that's why pg_basebackup does more than that and
also why we document those other steps for anyone interested in writing
their own backup tool.

Perhaps an argument can be made that replication slots should be
optional for pg_basebackup to include or not (and similarily for other
PG backup tools which use the low-level API).

Thanks,

Stephen

Hi Stephen,

I do apologize for the accidental top-post - thank you for pointing it out!

On Fri, May 19, 2023 at 4:13 PM Stephen Frost <sfrost@snowman.net> wrote:
> Perhaps an argument can be made that replication slots should be
> optional for pg_basebackup to include or not (and similarily for other
> PG backup tools which use the low-level API).

From my part, it would be an excellent addition but perhaps I am alone
in this. So I leave that to you if it would be a valuable addition or
not.

Thank you sincerely for the feedback and comments!

--
Kristjan Mustkivi

Email: kristjan.mustkivi@gmail.com