[HACKERS] Re[2]: [HACKERS] standby, pg_basebackup and last xlog file
Hello all and Heikki personally Thank you for your answer I have some new points: 21.01.2013, 10:08 +02:00 от Heikki Linnakangas : >On 21.01.2013 09:14, Миша Тюрин wrote: >>Is there any reason why pg_basebackup has limitation in an online backup >> from the standby: "The backup history file is not created in the database >> cluster backed up." ? > >WAL archiving isn't active in a standby, so even if it created a backup >history file, it wouldn't go anywhere. Also, the way the backup history >files are named, if you take a backup on the master at the same time (or >another backup at the same time in the standby), you would end up with >multiple backup history files with the same name. > >>So i can't get last xlog file needed to restore :( > >Good point. That was an oversight in the patch that allowed base backups >from a standby. > >>Also maybe i can use something like ( pg_last_xlog_replay_location() + 1 >> ) after pg_basebackup finished. > >Yeah, that should work. > >>Does anybody know true way to getting last xlog file in case of applying >> pg_basebackup to standby? >>How pg_basebackup gets last xlog file in case of standby and -x option? > >The server returns the begin and end WAL locations to pg_basebackup, >pg_basebackup just doesn't normally print them out to the user. In >verbose mode, it does actually print them out, but only with -x, so that >doesn't help you either. If you can compile from source, you could >modify pg_basebackup.c to print the locations without -x and --verbose, >search lines that print out "transaction log start point / end position". 1) we can get last xlog by using control data's "Minimum recovery ending location" > > >As a workaround, without modifying the source, you can do this after >pg_basebackup has finished, to get the first WAL file you need: > >$ pg_controldata backupdir | grep "REDO WAL file" >Latest checkpoint's REDO WAL file:00030009 and I would like to correct your suggestion about first wal file 2.1) we should use backup_label to determine first needed wal 2.2) and we must not use redo from checkpoint. because there are might be more than one checkpoint during base_backup > >And as you suggested, pg_last_xlog_replay_location() for the last WAL >file you need. > >- Heikki > > >-- >Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-hackers - Misha -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re[2]: [HACKERS] standby, pg_basebackup and last xlog file
Hello all and Heikki personally Thank you for your answer I have some new points: Понедельник, 21 января 2013, 10:08 +02:00 от Heikki Linnakangas : >On 21.01.2013 09:14, Миша Тюрин wrote: >>Is there any reason why pg_basebackup has limitation in an online backup >> from the standby: "The backup history file is not created in the database >> cluster backed up." ? > >WAL archiving isn't active in a standby, so even if it created a backup >history file, it wouldn't go anywhere. Also, the way the backup history >files are named, if you take a backup on the master at the same time (or >another backup at the same time in the standby), you would end up with >multiple backup history files with the same name. > >>So i can't get last xlog file needed to restore :( > >Good point. That was an oversight in the patch that allowed base backups >from a standby. > >>Also maybe i can use something like ( pg_last_xlog_replay_location() + 1 >> ) after pg_basebackup finished. > >Yeah, that should work. > >>Does anybody know true way to getting last xlog file in case of applying >> pg_basebackup to standby? >>How pg_basebackup gets last xlog file in case of standby and -x option? > >The server returns the begin and end WAL locations to pg_basebackup, >pg_basebackup just doesn't normally print them out to the user. In >verbose mode, it does actually print them out, but only with -x, so that >doesn't help you either. If you can compile from source, you could >modify pg_basebackup.c to print the locations without -x and --verbose, >search lines that print out "transaction log start point / end position". 1) we can get last xlog by using control data's "Minimum recovery ending location" > > >As a workaround, without modifying the source, you can do this after >pg_basebackup has finished, to get the first WAL file you need: > >$ pg_controldata backupdir | grep "REDO WAL file" >Latest checkpoint's REDO WAL file:00030009 and I would like to correct your suggestion about first wal file 2.1) we should use backup_label to determine first needed wal 2.2) and we must not use redo from checkpoint. because there are might be more than one checkpoint during base_backup > >And as you suggested, pg_last_xlog_replay_location() for the last WAL >file you need. > >- Heikki > > >-- >Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-hackers - Misha
Re: [HACKERS] standby, pg_basebackup and last xlog file
On 21.01.2013 09:14, Миша Тюрин wrote: Is there any reason why pg_basebackup has limitation in an online backup from the standby: "The backup history file is not created in the database cluster backed up." ? WAL archiving isn't active in a standby, so even if it created a backup history file, it wouldn't go anywhere. Also, the way the backup history files are named, if you take a backup on the master at the same time (or another backup at the same time in the standby), you would end up with multiple backup history files with the same name. So i can't get last xlog file needed to restore :( Good point. That was an oversight in the patch that allowed base backups from a standby. Also maybe i can use something like ( pg_last_xlog_replay_location() + 1 ) after pg_basebackup finished. Yeah, that should work. Does anybody know true way to getting last xlog file in case of applying pg_basebackup to standby? How pg_basebackup gets last xlog file in case of standby and -x option? The server returns the begin and end WAL locations to pg_basebackup, pg_basebackup just doesn't normally print them out to the user. In verbose mode, it does actually print them out, but only with -x, so that doesn't help you either. If you can compile from source, you could modify pg_basebackup.c to print the locations without -x and --verbose, search lines that print out "transaction log start point / end position". As a workaround, without modifying the source, you can do this after pg_basebackup has finished, to get the first WAL file you need: $ pg_controldata backupdir | grep "REDO WAL file" Latest checkpoint's REDO WAL file:00030009 And as you suggested, pg_last_xlog_replay_location() for the last WAL file you need. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] standby, pg_basebackup and last xlog file
Hello! I wrote to general ( [GENERAL] standby, pg_basebackup and last xlog file ) some times ago. but still hasn't got any feedback. Hello! Is there any reason why pg_basebackup has limitation in an online backup from the standby: "The backup history file is not created in the database cluster backed up." ? So i can't get last xlog file needed to restore :( Think i can use -x option for getting last xlog file, but i would like to minimize size of resulting backup. // i also get all WALs by archive_command, so there is no reason to get two copies of each wal during basebackup. Also maybe i can use something like ( pg_last_xlog_replay_location() + 1 ) after pg_basebackup finished. Does anybody know true way to getting last xlog file in case of applying pg_basebackup to standby? How pg_basebackup gets last xlog file in case of standby and -x option?