Thread: Online base backup from the hot-standby
Hi I would like to develop function for 'Online base backup from the hot-standby' in PostgreSQL 9.2. Todo : Allow hot file system backups on standby servers (http://wiki.postgresql.org/wiki/Todo) [GOAL]* Make pg_basebackup to execute to the hot-standby server and acquire online-base-backup . - pg_basebackup canbe executed to only primary server in PostgreSQL 9.1 . - But physical-copy(etc) under processing of pg_basebackup raises the load of primary server . - Therefore , this function is necessary . [Problem] (There is the following problems when hot-standby acquires online-base-backup like executing pg_basebackup to the primaryserver .)* pg_start_backup() and pg_stop_backup() can't be executed to the hot-standby server . - hot-standbycan't insert backup-end record to WAL-files and can't operate CHECKPOINT . - Because hot-standby can'twrite anything in WAL-files .* hot-standby can't send WAL-files to archive server. - when pg_stop_backup() is executedto the primary server , it waits for completing sending wal to archive server , but hot-standby can'tdo it. [Policy] (I create with the following Policy .) * This function doesn't affect primary server . - I don't adopt the waywhich "hot-standby requests primary to execute pg_basebackup" , because I think about many standbys isconnected with a primary . [Approach]* When pg_basebackup is executed to the hot-standby server , it executes RESTARTPOINT instead of CHECKPOINT . backup_label is made from the RESTARTPOINT's results , and is sent to the designated backup server using pg_basebackupconnection .* Instead of inserting backup-end record , hot-standby writes backup-end-position in backup-history-fileand sends to the designated backup server using pg_basebackup connection . - In 9.1 , startup processknows backup-end-position from only backup-end record . In addition to its logic, startup process can knowbackup-end-position from backup-history-file . As a result , startup process can recovery certainly withoutbackup-end record . [Precondition] (As a result of the above-mentioned Policy and Approach , there is the following restrictions .) * Immediately after backupstarting of WAL must contain full page writes . But the above-mentioned Approach can't satisfy the restrictionaccording to circumstances . Because full_page_writes of primary might equal 'off' . When standby recoveryWAL which is removed full page writes by pg_lesslog , it is the same . * Because recovery starts from lastCHECKPOINT , it becomes long . * I has not thought new process that become taking the place of waiting for completingsending wal to archive server , yet. [Working Step]STEP1: Make startup process to acquire backup-end-position from not only backup-end record but alsobackup-history-file . * startup process allows to acquire backup-end-position from backup-history-file. * When pg_basebackup is executed , backup-history-file is sent to the designatedbackup server .STEP2: Make pg_start_backup() and pg_stop_backup() to be executed by the hot-standby server. [Action until The first CommitFest (on June 15)] I will create a patch to STEP1 . (The patch will be able to settle a problem of Omnipitr-backup-slave.) (a problem of Omnipitr-backup-slave : http://archives.postgresql.org/pgsql-hackers/2011-03/msg01490.php) * Shedule ofcreating STEP2 is the next CommitFest (in September 15) -------------------------------------------- Jun Ishizuka NTT Software Corporation TEL:045-317-7018 E-Mail: ishizuka.jun@po.ntts.co.jp --------------------------------------------
On 27.05.2011 09:09, Jun Ishiduka wrote: > STEP1: Make startup process to acquire backup-end-position from > not only backup-end record but also backup-history-file . > * startup process allows to acquire backup-end-position > from backup-history-file . > * When pg_basebackup is executed , backup-history-file is > sent to the designated backup server . I don't much like that approach. The standby would need to be able to write the backup history file to the archive at the end of backup, and we'd have to reintroduce the code to fetch it from archive and, when streaming, from the master. At the moment, the archiver doesn't even run in the standby. I think we'll need to write the end-of-backup location somewhere in the base backup instead. pg_stop_backup() already returns it, the client just needs to store it somewhere with the base backup. So I'm thinking that the procedure for taking a base backup from slave would look something like this: 1. psql postgres -c "SELECT pg_start_backup('label')"; 2. tar cvzf basebackup.tar.gz $PGDATA 3. psql postgres -c "SELECT pg_stop_backup()"; > backup_end_location 4. (keep backup_end_location alongside basebackup.tar.gz) Or, we can just document that the control file must be backed up *last*, so that the minimum recovery point in the control file serves the same purposes as the end-of-backup location. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
> I don't much like that approach. The standby would need to be able to > write the backup history file to the archive at the end of backup, and > we'd have to reintroduce the code to fetch it from archive and, when > streaming, from the master. At the moment, the archiver doesn't even run > in the standby. Please teach the reason for "The standby would need to be able to write the backup history file to the archive at the end of backup" . (I'd like to know why "to only pg_xlog" is wrong .) Because there is the opinion of "Cascade replication" , I don't want to realize the function with the method which the standby requests to execute it on the primary server . (The opinion of "Cascade replication": http://archives.postgresql.org/pgsql-hackers/2011-05/msg01150.php) -------------------------------------------- Jun Ishizuka NTT Software Corporation TEL:045-317-7018 E-Mail: ishizuka.jun@po.ntts.co.jp --------------------------------------------
On 31.05.2011 07:46, Jun Ishiduka wrote: >> I don't much like that approach. The standby would need to be able to >> write the backup history file to the archive at the end of backup, and >> we'd have to reintroduce the code to fetch it from archive and, when >> streaming, from the master. At the moment, the archiver doesn't even run >> in the standby. > > Please teach the reason for "The standby would need to be able to write > the backup history file to the archive at the end of backup" . > (I'd like to know why "to only pg_xlog" is wrong .) If the backup history file is not archived, the postgres process won't find it when you try to restore from the base backup. The new server has no access to the standby's pg_xlog directory. > Because there is the opinion of "Cascade replication" , I don't want to > realize the function with the method which the standby requests to execute > it on the primary server . > > (The opinion of "Cascade replication": > http://archives.postgresql.org/pgsql-hackers/2011-05/msg01150.php) I don't see how this helps. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
2011/5/31 Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>: > On 31.05.2011 07:46, Jun Ishiduka wrote: >>> I don't much like that approach. The standby would need to be able to >>> write the backup history file to the archive at the end of backup, and >>> we'd have to reintroduce the code to fetch it from archive and, when >>> streaming, from the master. At the moment, the archiver doesn't even run >>> in the standby. >> >> Please teach the reason for "The standby would need to be able to write >> the backup history file to the archive at the end of backup" . >> (I'd like to know why "to only pg_xlog" is wrong .) > > If the backup history file is not archived, the postgres process won't > find it when you try to restore from the base backup. The new server has > no access to the standby's pg_xlog directory. Right. If we take a base backup from the standby by not using pg_basebackup, since there is no way to share the backup history file from the standby to new server, an idea like you suggested would be required. OTOH, if we use pg_basebackup, I think that it makes sense that pg_basebackup transfers the backup history file from the standby to new server, puts it in the base backup (pg_xlog?), and new server reads it from the base backup but not the archive. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
>>> I don't much like that approach. The standby would need to be able to >>> write the backup history file to the archive at the end of backup, and >>> we'd have to reintroduce the code to fetch it from archive and, when >>> streaming, from the master. At the moment, the archiver doesn't even run >>> in the standby. >> >> Please teach the reason for "The standby would need to be able to write >> the backup history file to the archive at the end of backup" . >> (I'd like to know why "to only pg_xlog" is wrong .) > > If the backup history file is not archived, the postgres process won't > find it when you try to restore from the base backup. The new server has > no access to the standby's pg_xlog directory. Thanks for the answer . But , it sends the backup history file to pg_xlog of new server (=backup server) when pg_basebackup is executed to the standby server , and so I was going to create the patch of such logic . I think it don't become the above-mentioned movement . >> Because there is the opinion of "Cascade replication" , I don't want to >> realize the function with the method which the standby requests to execute >> it on the primary server . >> >> (The opinion of "Cascade replication": >> http://archives.postgresql.org/pgsql-hackers/2011-05/msg01150.php) > >I don't see how this helps. Hypothesis:* Online base backup was realized with the method which the standby requests to execute it on the "primary server".* "Cascade replication" was developed , and user is using it . (Ex. Primary -- Standby1 -- Standby2) Situation:(1) Standby2 executes pg_basebackup .(2) Then, Standby2 accesses Standby1 .(3) But, it fails, because Standby2'sprimary is Standby1, not Primary . Result:* I don't want to realize the function with the method which the standby requests to execute it on the primary server. -------------------------------------------- Jun Ishizuka NTT Software Corporation TEL:045-317-7018 E-Mail: ishizuka.jun@po.ntts.co.jp --------------------------------------------