Обсуждение: Streaming Rep - 2-phase backups and reducing time to full replication
Some ideas to improve current behaviour of SR http://wiki.postgresql.org/wiki/Streaming_Replication The current startup process is copied below. (7) gives some issues if it is a very long step, notably that the master may fill with data and then break off the connection before replication is fully configured. 7. Make a base backup of the primary server, load this data onto the standby. 8. Set up XLOG archiving, connections and authentication in the standby server like the primary, so that the standby might work as a primary after failover. 9. Create a recovery command file in the standby server; the following parameters are required for streaming replication. 10. Start postgres in the standby server. It will start streaming replication. It occurs to me to ask which files we need as a minimum before we can begin step (10)? If we could start step (10) before step (7) is complete then we would avoid many of our space problems and replication would enter "safe" mode considerably sooner, in some cases dozens of hours earlier. We read the recovery.conf at the start of StartupXLog(). So by that point we need only the following files * All *.conf files * pg_control (and probably much of the rest of global/ directory) Some very quick surgery on a current-version data directory shows this is correct, apart from the call to RelationCacheInitFileRemove() which can be altered to accept a missing directory as proof that the file has been removed. If we then think of the starting procedure as happening in two parts: i) sufficient startup to get to the point where we bring up the walreceiver, while startup process waits further confirmation ii) following further confirmation startup process now begins recovering database So if we do the base backup in two stages the sequence of actions could become 9. Create a recovery command file in the standby server with parameters required for streaming replication. 7. (a) Make a base backup of minimal essential files from primary server, load this data onto the standby. 10. Start postgres in the standby server. It will start streaming replication. 7. (b) Continue with second phase of base backup, copying all remaining files, ending with pg_stop_backup() * Next step is to waken startup process so it can continue recovery We don't need to introduce another call, we just need to have a mechanism for telling the startup process to sleep because we are doing a two-phase backup and another mechanism for waking it when the whole backup is complete. That sounds like a recovery.conf parameter and an additional kind of trigger file, perhaps the backup file? This seems like a simple and useful option for 8.5 -- Simon Riggs www.2ndQuadrant.com
On Dec 22, 2009, at 12:54 PM, Simon Riggs wrote: > 9. Create a recovery command file in the standby server with parameters > required for streaming replication. > > 7. (a) Make a base backup of minimal essential files from primary > server, load this data onto the standby. > > 10. Start postgres in the standby server. It will start streaming > replication. > > 7. (b) Continue with second phase of base backup, copying all remaining > files, ending with pg_stop_backup() Dumb question: could the WAL streaming code be made to also ship base files? That would make setting up a streaming replicasuper-simple. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
On Wed, Dec 23, 2009 at 6:33 AM, decibel <decibel@decibel.org> wrote: > Dumb question: could the WAL streaming code be made to also ship base files? No. > That would make setting up a streaming replica super-simple. Agreed, but it's not easy to implement that. So I'm thinking that it's out of the scope of the development for v8.5. Of course, anyone is welcome to try that ;-) Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Wed, Dec 23, 2009 at 3:54 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > 9. Create a recovery command file in the standby server with parameters > required for streaming replication. > > 7. (a) Make a base backup of minimal essential files from primary > server, load this data onto the standby. > > 10. Start postgres in the standby server. It will start streaming > replication. > > 7. (b) Continue with second phase of base backup, copying all remaining > files, ending with pg_stop_backup() This manual procedure seems to be more complicated for me than just using restore_command which retrieves the WAL files from the master's archival area. Supposing that we adopt this idea, we should implement an automatic replication of a base backup at the same time, to prevent the users from doing the complicated two-phase-backup? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Thu, 2009-12-24 at 14:40 +0900, Fujii Masao wrote: > On Wed, Dec 23, 2009 at 3:54 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > > 9. Create a recovery command file in the standby server with parameters > > required for streaming replication. > > > > 7. (a) Make a base backup of minimal essential files from primary > > server, load this data onto the standby. > > > > 10. Start postgres in the standby server. It will start streaming > > replication. > > > > 7. (b) Continue with second phase of base backup, copying all remaining > > files, ending with pg_stop_backup() > > This manual procedure seems to be more complicated for me than just > using restore_command which retrieves the WAL files from the master's > archival area. Supposing that we adopt this idea, we should implement > an automatic replication of a base backup at the same time, to prevent > the users from doing the complicated two-phase-backup? Either way works for async, but only the 2-phase backup works for synch rep. It's slightly more complicated for the base backup, but not so it isn't still very practical. The list of files is very short: *.conf and the global directory. But overall, ISTM it would actually be easier to do this than to set up both streaming and restore_command. I see it would work like this: Add a new option to recovery.conf, perhaps two_phase_backup = on. Startup creates a file called backup_in_progress then waits. When second phase of backup is complete (7b), delete the file and then Startup process will continue. Very few lines of code to make this work. -- Simon Riggs www.2ndQuadrant.com
On Tue, 2009-12-22 at 15:33 -0600, decibel wrote: > Dumb question: could the WAL streaming code be made to also ship base files? That would make setting up a streaming replicasuper-simple. That was a possible design, but that's not will be there for this release. I opposed adding the "we do the base backup for you" feature because there are many ways of doing a base backup and it would likely have restricted your options to do so. One issue that would cause is limiting the speed of the base backup to a single libpq connection, which would cause performance problems. So yes, super-simple, but not super-good for many big users. -- Simon Riggs www.2ndQuadrant.com
Re: Streaming Rep - 2-phase backups and reducing time to full replication
От
Andreas 'ads' Scherbaum
Дата:
On Thu, 24 Dec 2009 09:58:20 +0000 Simon Riggs wrote: > On Tue, 2009-12-22 at 15:33 -0600, decibel wrote: > > > Dumb question: could the WAL streaming code be made to also ship base files? That would make setting up a streaming replicasuper-simple. > > That was a possible design, but that's not will be there for this > release. > > I opposed adding the "we do the base backup for you" feature because > there are many ways of doing a base backup and it would likely have > restricted your options to do so. One issue that would cause is limiting > the speed of the base backup to a single libpq connection, which would > cause performance problems. So yes, super-simple, but not super-good for > many big users. The big users will always have other options. But for normal users who just want to enable a replication - this feature would be awesome: the entire replication is done by the database. So +1 for integrating such a feature in a future version. Bye -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project
On Thu, Dec 24, 2009 at 6:03 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > I see it would work like this: Add a new option to recovery.conf, > perhaps two_phase_backup = on. Startup creates a file called > backup_in_progress then waits. When second phase of backup is complete > (7b), delete the file and then Startup process will continue. Very few > lines of code to make this work. Where do you think the WAL files shipped before doing (7b) are stored? If it's pg_xlog, the disk full failure would occur in the standby. If it's an archive, restore_command would have to be supplied the same as my idea. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Fri, 2009-12-25 at 14:33 +0900, Fujii Masao wrote: > On Thu, Dec 24, 2009 at 6:03 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > > I see it would work like this: Add a new option to recovery.conf, > > perhaps two_phase_backup = on. Startup creates a file called > > backup_in_progress then waits. When second phase of backup is complete > > (7b), delete the file and then Startup process will continue. Very few > > lines of code to make this work. > > Where do you think the WAL files shipped before doing (7b) are stored? > If it's pg_xlog, the disk full failure would occur in the standby. If > it's an archive, restore_command would have to be supplied the same as > my idea. Yes, agreed. I am still concerned about the interactions at the start of replication. It isn't clear how these things work exactly and as a result, I feel we may be missing some better ideas. Two points concern me * When would sync rep be able to start? * How do we avoid sending WAL twice? -- Simon Riggs www.2ndQuadrant.com