[linux-lvm] PostgreSQL and file system level backup

[linux-lvm] PostgreSQL and file system level backup

[Murthy Kambhampaty]

The postgresql documentation states that in order to get a usable backup
from a filesystem dump (or copy) the database server must be shut down.
Moreover, "Half-way measures such as disallowing all connections will not
work as there is always some buffering going on. For this reason it is also
not advisable to trust file systems that claim to support "consistent
snapshots"."

Just to make certain, is this true with running xfsdump on an LVM snapshot
of the XFS filesystem? My question was prompted by a recent post on the
linux-xfs list in which one of the xfs maintainers pointed out that xfsdumps
are cache coherent. In response to my preliminary question, Tom Lane pointed
out that:

"Unless the postmaster is shut down meanwhile, you'll probably end up with a
corrupt database.  The problem is that xfsdump does not give you an
instantaneous snapshot of the filesystem state, so you will probably collect
inconsistent contents of the various files that make up the database."

Which gives rise to my present question: given that LVM DOES give an
instantaneous snapshot of the filesystem, would an xfsdump of an LVM
snapshot of an XFS filesystem give usable backups?

Thanks,
	Murthy

PS: Our databases have crossed the 100 GB size, with some tables reaching 25
GB, so pg_dump is taking more than 5 hours to complete (changing the level
of compression in custom format dump files from 6 to 1 did not significantly
reduce the dump duration). Otherwise, pg_dump-ing the database (and copying
the .conf files) was just dandy.

Re: [linux-lvm] PostgreSQL and file system level backup

[Steven Lembark]

> Which gives rise to my present question: given that LVM DOES give an
> instantaneous snapshot of the filesystem, would an xfsdump of an LVM
> snapshot of an XFS filesystem give usable backups?

> PS: Our databases have crossed the 100 GB size, with some tables reaching
> 25 GB, so pg_dump is taking more than 5 hours to complete (changing the
> level of compression in custom format dump files from 6 to 1 did not
> significantly reduce the dump duration). Otherwise, pg_dump-ing the
> database (and copying the .conf files) was just dandy.

LVM won't give you any more stable of a pictire over 5
hours than anything else will unless you shut dwon the
database first. Even if you try it once and the process
works you are just as likely to be bit my Murphy on the
next trial.

Alternatives:

	Use a mirror for the database, split it, and use that
	copy for a backup. This is a common procedure in data
	warehousing.

	Make incremental backups of the daily loads and only
	perform a full backup on, say, weekends at 0300; use
	RAID5 or mirroring to minimimize the odds of having to
	restore anything.
	
	You may be able to dump portions of the
	database in parallel to decrease the time (this
	requires careful partitioning of the space).

--
Steven Lembark                               2930 W. Palmer
Workhorse Computing                       Chicago, IL 60647
                                            +1 800 762 1582

Re: [linux-lvm] PostgreSQL and file system level backup

[Ragnar Kjørstad]

On Fri, Nov 15, 2002 at 04:25:49PM -0500, Murthy Kambhampaty wrote:
> "Unless the postmaster is shut down meanwhile, you'll probably end up with a
> corrupt database.  The problem is that xfsdump does not give you an
> instantaneous snapshot of the filesystem state, so you will probably collect
> inconsistent contents of the various files that make up the database."
> 
> Which gives rise to my present question: given that LVM DOES give an
> instantaneous snapshot of the filesystem, would an xfsdump of an LVM
> snapshot of an XFS filesystem give usable backups?

Yes, it should.

Allthough, some of the postgresql-developers use the term "corrupt
database" of a database where the table-files are inconsistant. That's
generally _always_ the case when the server is running, as the latest
updates are only available in the database-log. That's also the case for
the LVM-snapshot.

This "inconsistancy" will be fixed by log-replay when you start the server
(after restore).

If you want to avoid it, you can:
- shut down databasee
- take snapshot
- start database
- backup from snapshot



There was a long thread on this subject on the postgresql-list a month
or two ago.




-- 
Ragnar Kjørstad
Big Storage