Backup Central

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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

On Jan 25, 2012, at 10:20 AM, Ben Walton wrote:

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?

Yeah, I think that will be how I will do this if I cannot get the non-snapshot approach to work.

The main thing holding me back is that the machines (there are many of them) do not necessarily all have the same LVM configuration.

So I would then have to write some script which correctly determines which LVs to snapshot and back them all up, and it would need to be very careful to not leave stray snapshots around taking up space. Not hard, but going to require a few days' worth of work / testing / review to make sure there's no edge cases. I was trying to avoid the need to do that and have a simpler solution that doesn't require mucking with LVM.

Right now it's as simple as recursively backing up / with some obvious excludes (/proc and such), and doesn't at all depend on the LVM configuration.

I'd still be curious to hear other options to my original question, but thanks for the input.


------------------------------------------------------------------------------
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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

-----Original message-----From: Steven Schlansker <steven < at > likeness.com>Sent: Wed 25-01-2012 11:51Subject: Re: [Bacula-users] Ensuring files made during the backup run are archived (hot-backup PostgreSQL)To: Martin Simmons <martin < at > lispworks.com>; CC: bacula-users < at > 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 ifif'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.

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 <steven < at > 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 <martin < at > lispworks.com>;
CC: bacula-users < at > 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 < at > 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 < at > lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users