Re: is backing up a live postgres enough?

This is generally a bad idea. The manual states that it will work only if
the database is inactive. That is, not being changed by users. As this is
difficult to achieve without going off line, it is not useful.

pg_dumpall for dumping and psql template1 <dumpfile are the approved methods
of dumping and restoring. However, a problem arises if your database(s)
contain Blobs (large objects), as these will not be saved or restored.

There is a switch for pg_dump to save blobs and a new program, pg_restore,
which will restore backups done this way, including blobs. However, there
are 3 problems with this.

1. The pg_dump switch -b doesn't work with pg_dumpall
2. There is no "pg-restoreall" program
3. An apparent bug in dumping and restoring blobs does not result in the
restored blob having the same OID as the original. A major problem as the
OID is what you use to access the blob. (I have a bug report in on this.)

I am working on a script to do a full dump and restore but it is dependent
upon the repair of item #3.

This discussion is better suited to the [ADMIN] mail list. When #3 is
resolved, or if I find a workaround, I'll post it there.

Hope this helps.

Colin

----- Original Message -----
From: "Louis-David Mitterrand" <vindex(at)apartia(dot)org>
To: "PostgreSQL-general" <pgsql-general(at)postgresql(dot)org>
Sent: Sunday, November 24, 2002 4:51 AM
Subject: [GENERAL] is backing up a live postgres enough?

I recently restored a debian system from off-site rsync'ed copie of
/{var,etc,home} including a large Postgres db in /var/lib/postgres/data.
FWIW the rsync backup was run nightly on the _live_ system with all
daemons active.

The db started fine on the restored filesystem. Was I lucky? Is it still
necessary to create a daily dump with pg_dump? What is the accepted
practice w.r.t Postgres backups?

Thanks in advance, cheers,

--
HIPPOLYTE: Puis-je vous demander quel funeste nuage,
Seigneur, a pu troubler votre auguste visage ?
(Phèdre, J-B Racine, acte 4, scène
2)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org