Re: [GENERAL] Backup

[Chander Ganesan <chander(at)otg-nc(dot)com>]

Simon Riggs wrote:

On Fri, 2008-01-25 at 11:34 +1100, Phillip Smith wrote:
  

We have a center in Europe who has just started to use PostgreSQL and was
asking me if there are any Symantec product or other products that backup
this type of database.
      

It doesn't appear to. 
    

The design of the PITR system allows a product-agnostic backup. Anything
that can backup a file can backup PostgreSQL. There is no need for
special certifications of hologram logos.

You may need to write a few lines of script to do it, but that's not a
problem surely?

So you can use pg_dump or PITR, as you choose.
  

If you don't mind if you lose some transactions you can also use file system snapshotting....which would work just as well as pg_dump, and probably have less impact on the running database (assuming you had decent enough hardware).

So long as the product you are using can snapshot the file system prior to performing a backup, you can use just about any product (without having to really do much/any work).  When you restore a snapshotted file system that contains your postgresql database, postgresql (on restart) will enter auto-recovery mode and recover (as it does in the case of the crash) to the last transaction that was completed successfully prior to the snapshot being created.  Note that this would only work if all your tablespaces were on the same file system, and would be unable to "roll forward" using WAL files generated after the backup.

However, you should keep in mind that - like a pg_dump - you won't be able to perform PITR recovery from such a backup.  Also, the recovery time may be non-trivial depending on your WAL settings.

-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
As me about Expert PostgreSQL & PostGIS Training delivered worldwide.