pg9.6: no backup history file (*.backup) created on hot standby
Hello,
I am using PostgreSQL-9.6, trying to do basebackup on a hot standby
with archive mode set to always. However, I found there is no backup
history file (*.backup) generated in the archive destination directory
after `pg_basebackup`, which is not the case if I do the same thing on
the primary.
Is this as expected? If so, in which case should I do backup on primary
and in which case should I do it on standby?
Thank you in advance!
Magodo
On Mon, Oct 08, 2018 at 03:23:47PM +0800, magodo wrote:
Is this as expected?
Yes.
If so, in which case should I do backup on primary and in which case
should I do it on standby?
This depends on your use cases, sometimes you don't want to make the
production server, the primary use more CPU than necessary so you can
leverage the activity on a standby.
--
Michael
(Please do not forget to add the community mailing list in CC.)
On Tue, Oct 09, 2018 at 10:33:56AM +0800, magodo wrote:
Since the backup history aims to aid administrator to identify the
point from which wal archive should be kept and before which the
archive could be cleaned. It is very helpful in kinds of use cases.Why not also create it when do basebackup on standby?
The backup_label file is equally helpful, and backup history files are
not mandatory for backups. One of the reasons behind why we cannot have
those on standbys is that there is no actual way to ensure the
uniqueness of this file, as two backups could be started in parallel
with the same start location and the *same* file name. If an archive
command is not able to handle correctly the duplicates, you could bloat
pg_wal. And that's a real problem.
--
Michael
Import Notes
Reply to msg id not found: 55036790ed3c8c900d674bf90dfbdd65f6ab170f.camel@sina.com
On Tue, 2018-10-09 at 11:45 +0900, Michael Paquier wrote:
(Please do not forget to add the community mailing list in CC.)
On Tue, Oct 09, 2018 at 10:33:56AM +0800, magodo wrote:
Since the backup history aims to aid administrator to identify the
point from which wal archive should be kept and before which the
archive could be cleaned. It is very helpful in kinds of use cases.Why not also create it when do basebackup on standby?
The backup_label file is equally helpful, and backup history files
are
not mandatory for backups. One of the reasons behind why we cannot
have
those on standbys is that there is no actual way to ensure the
uniqueness of this file, as two backups could be started in parallel
with the same start location and the *same* file name. If an archive
command is not able to handle correctly the duplicates, you could
bloat
pg_wal. And that's a real problem.
--
Michael
Oh, I almost forget backup_label.. Thank you for point it out!
Yet, I am still not so clear how does the bloat of pg_wal happen? Do
you mean pg_wal will be filled up by many .backup(s)?
Magodo
On Tue, Oct 09, 2018 at 11:45:50AM +0800, magodo wrote:
Yet, I am still not so clear how does the bloat of pg_wal happen? Do
you mean pg_wal will be filled up by many .backup(s)?
If your archive_command is for example a simple cp (which it should not
be by the way), and if you try to archive twice the same file, then the
archive command would continuously fail and prevent existing WAL
segments to be archived. Segments are continuously created, and pg_wal
grows in size.
--
Michael
On Tue, 2018-10-09 at 12:53 +0900, Michael Paquier wrote:
On Tue, Oct 09, 2018 at 11:45:50AM +0800, magodo wrote:
Yet, I am still not so clear how does the bloat of pg_wal happen?
Do
you mean pg_wal will be filled up by many .backup(s)?If your archive_command is for example a simple cp (which it should
not
be by the way), and if you try to archive twice the same file, then
the
archive command would continuously fail and prevent existing WAL
segments to be archived. Segments are continuously created, and
pg_wal
grows in size.
--
Michael
Yes, but does this differ whether I'm archiving a general WAL or
archiving the backup history? I mean if user doesn't handle duplicate
archive, then pg_wal will still be filled up when archiving WAL.
Magodo
On Tue, Oct 09, 2018 at 03:26:35PM +0800, magodo wrote:
Yes, but does this differ whether I'm archiving a general WAL or
archiving the backup history? I mean if user doesn't handle duplicate
archive, then pg_wal will still be filled up when archiving WAL.
A WAL segment has a unique name, and would be finished to be used once.
The problem with backup history files on standbys is that the *same*
file can could finish by being generated *multiple* times with base
backups taken in parallel. That's a completely different story, and the
window to those backup history files having the same name gets larger
the more the window between two checkpoints is. That's a problem I
studied a couple of months back.
--
Michael
On Tue, 2018-10-09 at 16:55 +0900, Michael Paquier wrote:
On Tue, Oct 09, 2018 at 03:26:35PM +0800, magodo wrote:
Yes, but does this differ whether I'm archiving a general WAL or
archiving the backup history? I mean if user doesn't handle
duplicate
archive, then pg_wal will still be filled up when archiving WAL.A WAL segment has a unique name, and would be finished to be used
once.
The problem with backup history files on standbys is that the *same*
file can could finish by being generated *multiple* times with base
backups taken in parallel. That's a completely different story, and
the
window to those backup history files having the same name gets larger
the more the window between two checkpoints is. That's a problem I
studied a couple of months back.
--
Michael
I just realized that doing basebackup on standby have other different
behaviors than it on primary, for example, it will not switch wal on
begin or end.
So do you just mean if there is no wal switch on primary(suppose
currently on 0000000100000002), then each basebackup made on standby
will always generate a same named backup history(e.g.
0000000100000002.backup)?
---
Magodo