To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: Backup strategies
Date: Wed, 15 Oct 2008 11:30:40 +0200
Lines: 64
Message-ID: <gd4d7q$3ed$1@ger.gmane.org>
References: <48F4D693.9000304@lerner.co.il>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig9B612C82132EFCD270730CBB"
User-Agent: Thunderbird 2.0.0.17 (X11/20080925)
In-Reply-To: <48F4D693.9000304@lerner.co.il>

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig9B612C82132EFCD270730CBB
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Reuven M. Lerner wrote:

> But before I do any of these things, I want to hear what others have
> discovered in terms of high-performance backups.  Is there a way to sto=
p
> pg_dump from locking up the database so much?  Is there a knob that I
> can turn to do a low-priority backup while the live site is running?  I=
s
> there a superior backup strategy than pg_dump every 24 hours?

If you are sysadmin-minded and your operating system & file system
support snapshots, an easy solution (and the one I use) is to create a
read-only snapshot of the file system with the (binary) database files
and back that up. The approach has some benefits:

* It won't interfere with "normal" database operations (no locking;
though I'm not sure that locking is your problem here as pgsql uses MVCC)=

* It works at disk speeds instead of converting data back to SQL for stor=
age
* Restoring the database works automagically - no need to import the
data from SQL back
* It's convenient to backup snapshots with usual file system backup
utilities. Tar works fine.

It also has some significant disadvantages:

* The binary database representation is usually much larger than the SQL
text one (because of indexes and internal structures). OTOH you can
easily use tar with gzip to compress it on the fly.
* Technically, the snapshot of the database you're taking represents a
corrupted database, which is repaired automatically when it's restored.
It's similar to as if you pulled the plug on the server while it was
working - PostgreSQL will repair itself.
* You cannot restore the database to a different version of PostgreSQL.
The same rules apply as if upgrading - for example you can run data from
8.3.0 on 8.3.3 but not from 8.2.0 to 8.3.0.

Warning: DO NOT do on-the-fly binary backups without snapshots.
Archiving the database directory with tar on a regular file system,
while the server is running, will result in an archive that most likely
won't work when restored.


--------------enig9B612C82132EFCD270730CBB
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFI9bhAldnAQVacBcgRAjr1AKCZaNZ+/8Lxl6BN0QIYRzCzMH6/MgCgn+sr
+ltu4olPYHflaXSnnTQHIJQ=
=6Ono
-----END PGP SIGNATURE-----

--------------enig9B612C82132EFCD270730CBB--