Re: [Bacula-users] Ensuring files made during the backup run are archived (hot-backup PostgreSQL)
-Original message- From:Steven Schlansker ste...@likeness.com Sent:Wed 25-01-2012 11:51 Subject:Re: [Bacula-users] Ensuring files made during the backup run are archived (hot-backup PostgreSQL) To:Martin Simmons mar...@lispworks.com; CC:bacula-users@lists.sourceforge.net; On Jan 25, 2012, at 11:34 AM, Martin Simmons wrote: On Wed, 25 Jan 2012 09:34:10 -0800, Steven Schlansker said: Looking through the documentation, there's no obvious way to guarantee order or backups within a FileSet. Is there some particular order that Bacula uses that I can rely on portably? (I assume not, but would love if there is) If not, would it be hard to add an option that say ensures that backups recurse in e.g. alphabetical order? I know I could set up two FileSets and two Jobs, but that is both more complex and makes doing differential / incremental backups and restores much more complicated. I'd prefer to have a single job that does it all. The problem is larger than just guaranteeing the order -- you also have to generate the list of WAL files after the main data files have been archived. You could make a fileset with two Include sections, one which starts at / but excludes the WAL files (with the Exclude = yes option) and other that includes only the WAL files. The main data files would be archived by the first Include section. So, Bacula will process the Include directives in order? I couldn't find that in the manual, but if if's true that is exactly what I need. Thanks! If you want to be certain that the include files are included in order, you can use a script instead of including the text file itself. That said, it seems to me that you'd need two jobs anyway. The first job backs up Postgres, the second one backs up the WAL files. What I would sometimes do in situations like this is define the backup as being as of the second the snapshot is taken, rather than as of when the backup is complete. Any updates (such as the WAL files) since then will be backed up by the next backup run, usually 24 hours later. The other option I see is, if the database is managably small, to use a Run Before script to take the snapshot, copy the snapshot somewhere else, copy the WAL files, and then back up this copy rather than the original. -- Try before you buy = See our experts in action! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-dev2___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Ensuring files made during the backup run are archived (hot-backup PostgreSQL)
Thanks to everyone for the input. I'll outline on what I settled on and why, in case someone later is interested in what worked for us. Instead of archiving these WAL files in the main backup, I set up a script to ship them as they are created directly to the backup server. This removes all the complexity involved with maintaining separate file sets, or picking out specific paths to archive later on. It also means that we have enough information to consider the backup server a cold standby -- since the WAL archives are shipped immediately, you don't lose data even if you have to go back to an older backup (you just replay forwards until the current time) The Bacula director does a Client Run Before/After Job which invokes the pg_start_backup and pg_stop_backup commands, which prepare the base data for the archiving pass. I then wrote some shell scripts to make restoring easier (finding the correct archive files for a given restore from Bacula) Done a few test restores, and it all works great. Thanks for the guidance! On Jan 27, 2012, at 9:23 AM, Kevin Keane (subscriptions) wrote: -Original message- From: Steven Schlansker ste...@likeness.com Sent: Wed 25-01-2012 11:51 Subject: Re: [Bacula-users] Ensuring files made during the backup run are archived (hot-backup PostgreSQL) To: Martin Simmons mar...@lispworks.com; CC: bacula-users@lists.sourceforge.net; On Jan 25, 2012, at 11:34 AM, Martin Simmons wrote: On Wed, 25 Jan 2012 09:34:10 -0800, Steven Schlansker said: Looking through the documentation, there's no obvious way to guarantee order or backups within a FileSet. Is there some particular order that Bacula uses that I can rely on portably? (I assume not, but would love if there is) If not, would it be hard to add an option that say ensures that backups recurse in e.g. alphabetical order? I know I could set up two FileSets and two Jobs, but that is both more complex and makes doing differential / incremental backups and restores much more complicated. I'd prefer to have a single job that does it all. The problem is larger than just guaranteeing the order -- you also have to generate the list of WAL files after the main data files have been archived. You could make a fileset with two Include sections, one which starts at / but excludes the WAL files (with the Exclude = yes option) and other that includes only the WAL files. The main data files would be archived by the first Include section. So, Bacula will process the Include directives in order? I couldn't find that in the manual, but if if's true that is exactly what I need. Thanks! If you want to be certain that the include files are included in order, you can use a script instead of including the text file itself. That said, it seems to me that you'd need two jobs anyway. The first job backs up Postgres, the second one backs up the WAL files. What I would sometimes do in situations like this is define the backup as being as of the second the snapshot is taken, rather than as of when the backup is complete. Any updates (such as the WAL files) since then will be backed up by the next backup run, usually 24 hours later. The other option I see is, if the database is managably small, to use a Run Before script to take the snapshot, copy the snapshot somewhere else, copy the WAL files, and then back up this copy rather than the original. -- Try before you buy = See our experts in action! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-dev2___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Try before you buy = See our experts in action! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-dev2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Ensuring files made during the backup run are archived (hot-backup PostgreSQL)
On Wed, 25 Jan 2012 11:40:59 -0800, Steven Schlansker said: So, Bacula will process the Include directives in order? I couldn't find that in the manual, but if if's true that is exactly what I need. Thanks! It may not be specified, but it is true empirically at least. __Martin -- Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] Ensuring files made during the backup run are archived (hot-backup PostgreSQL)
Hi all, I'm trying to set up a Bacula job to run a hot backup of a PostgreSQL installation. The fundamentals of how it would work are covered in the manual, but I will summarize here for the list. http://www.postgresql.org/docs/devel/static/continuous-archiving.html Normally if you just take a filesystem backup of the Postgres data files, you will end up with completely inconsistent data. But if you turn on WAL archiving and keep the WAL files with the backup, the database then can recover all the potentially corrupt segments by replaying these archived WAL files. The only catch is that you need all the WAL files archived at least until the backup of the main data files finishes. All this means that if only I could guarantee that the WAL files are backed up after the data files, I could have complete hot backups of my Postgres install with only a short pre/post hook. Looking through the documentation, there's no obvious way to guarantee order or backups within a FileSet. Is there some particular order that Bacula uses that I can rely on portably? (I assume not, but would love if there is) If not, would it be hard to add an option that say ensures that backups recurse in e.g. alphabetical order? I know I could set up two FileSets and two Jobs, but that is both more complex and makes doing differential / incremental backups and restores much more complicated. I'd prefer to have a single job that does it all. Thanks for any guidance, Steven -- Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Ensuring files made during the backup run are archived (hot-backup PostgreSQL)
Excerpts from Steven Schlansker's message of Wed Jan 25 12:34:10 -0500 2012: Hi Steven, Looking through the documentation, there's no obvious way to guarantee order or backups within a FileSet. Is there some particular order that Bacula uses that I can rely on portably? (I assume not, but would love if there is) If not, would it be hard to add an option that say ensures that backups recurse in e.g. alphabetical order? What about using LVM (assuming Linux, but substitute and alternate as appropriate) to snapshot the volume housing the data. You then run a pre-hook to create and mount the snapshot, point your fileset at the mounted snapshot and tear it down with a post-hook? That way, order isn't important as you get a stable snapshot of all the files at a point in time, which should preserve the recoverability of the WAL for you, no? Thanks -Ben -- Ben Walton Systems Programmer - CHASS University of Toronto C:416.407.5610 | W:416.978.4302 -- Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Ensuring files made during the backup run are archived (hot-backup PostgreSQL)
On Wed, 25 Jan 2012 09:34:10 -0800, Steven Schlansker said: Hi all, I'm trying to set up a Bacula job to run a hot backup of a PostgreSQL installation. The fundamentals of how it would work are covered in the manual, but I will summarize here for the list. http://www.postgresql.org/docs/devel/static/continuous-archiving.html Normally if you just take a filesystem backup of the Postgres data files, you will end up with completely inconsistent data. But if you turn on WAL archiving and keep the WAL files with the backup, the database then can recover all the potentially corrupt segments by replaying these archived WAL files. The only catch is that you need all the WAL files archived at least until the backup of the main data files finishes. All this means that if only I could guarantee that the WAL files are backed up after the data files, I could have complete hot backups of my Postgres install with only a short pre/post hook. Looking through the documentation, there's no obvious way to guarantee order or backups within a FileSet. Is there some particular order that Bacula uses that I can rely on portably? (I assume not, but would love if there is) If not, would it be hard to add an option that say ensures that backups recurse in e.g. alphabetical order? I know I could set up two FileSets and two Jobs, but that is both more complex and makes doing differential / incremental backups and restores much more complicated. I'd prefer to have a single job that does it all. The problem is larger than just guaranteeing the order -- you also have to generate the list of WAL files after the main data files have been archived. You could make a fileset with two Include sections, one which starts at / but excludes the WAL files (with the Exclude = yes option) and other that includes only the WAL files. The main data files would be archived by the first Include section. __Martin -- Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Ensuring files made during the backup run are archived (hot-backup PostgreSQL)
On Jan 25, 2012, at 11:34 AM, Martin Simmons wrote: On Wed, 25 Jan 2012 09:34:10 -0800, Steven Schlansker said: Looking through the documentation, there's no obvious way to guarantee order or backups within a FileSet. Is there some particular order that Bacula uses that I can rely on portably? (I assume not, but would love if there is) If not, would it be hard to add an option that say ensures that backups recurse in e.g. alphabetical order? I know I could set up two FileSets and two Jobs, but that is both more complex and makes doing differential / incremental backups and restores much more complicated. I'd prefer to have a single job that does it all. The problem is larger than just guaranteeing the order -- you also have to generate the list of WAL files after the main data files have been archived. You could make a fileset with two Include sections, one which starts at / but excludes the WAL files (with the Exclude = yes option) and other that includes only the WAL files. The main data files would be archived by the first Include section. So, Bacula will process the Include directives in order? I couldn't find that in the manual, but if if's true that is exactly what I need. Thanks! -- Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
