Thread: 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)

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@apartia.org>
To: "PostgreSQL-general" <pgsql-general@postgresql.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@postgresql.org

"Colin Stearman" <cstearman@infofind.com> writes:

> 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 have only seen this problem if the column that stores the reference
to the BLOB is *not* of type "oid".  If you store your BLOB references
in an "oid" column, pg_restore will "fix up" the references for you
when the BLOBs get renumbered.

This is kind of an annoying restriction as the automatic DB-creation
software I was using didn't support oid columns, but it does work.

-Doug

Louis-David Mitterrand <vindex@apartia.org> writes:
> 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?

Exceedingly.  Are you *sure* your data is okay --- have you actually run
any consistency checks?  I'd be looking for partially-committed
transactions, broken indexes, that sort of thing.

> What is the accepted practice w.r.t Postgres backups?

*NOT* that.

            regards, tom lane