Обсуждение: 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
Вложения
(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
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, 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, 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