Postgres Pro
Downloads
Home   >   mailing lists

Thread: reliable backup techniques

reliable backup techniques

From
"Ed L."
Date:
I'd like to get some opinions on the best techniques for getting
reliably consistent backups.  My current understanding is that the
only reliable way to get a consistent backup of a running system is
through pg_dump or pg_dumpall.  More specifically, it is my
understanding that file backups (tar, etc) do not guarantee
consistency due to the potential lag in cache writes to disk from
running transactions.

Is my understanding correct?  Are there other ways of getting
consistent backups of a live system?

Thanks,
Ed

Re: reliable backup techniques

From
Jonathan Bartlett
Date:
On Linux, you can use the Linux volume manager, and create a temporary
snapshot and tar that up.  If you have to go back to it, the database will
have to recover on startup, just as if the machine had crashed at that
point.  That's much better than a regular tar, in which the database would
be likely unrecoverable.

Jon

On Wed, 19 Feb 2003, Ed L. wrote:

>
> I'd like to get some opinions on the best techniques for getting
> reliably consistent backups.  My current understanding is that the
> only reliable way to get a consistent backup of a running system is
> through pg_dump or pg_dumpall.  More specifically, it is my
> understanding that file backups (tar, etc) do not guarantee
> consistency due to the potential lag in cache writes to disk from
> running transactions.
>
> Is my understanding correct?  Are there other ways of getting
> consistent backups of a live system?
>
> Thanks,
> Ed
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: reliable backup techniques

From
Andrew Sullivan
Date:
On Wed, Feb 19, 2003 at 09:34:15AM -0800, Jonathan Bartlett wrote:
> On Linux, you can use the Linux volume manager, and create a temporary
> snapshot and tar that up.  If you have to go back to it, the database will
> have to recover on startup, just as if the machine had crashed at that
> point.  That's much better than a regular tar, in which the database would
> be likely unrecoverable.

Hmm.  Is it guaranteed to be consistent?  I looked at Veritas for
this purpose, and I gathered there was a small possibility of
inconsistency, so I haven't done it (though I'd like to, because it'd
be much faster under some circumstances).

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Re: reliable backup techniques

From
Richard Huxton
Date:
On Wednesday 19 Feb 2003 5:23 pm, Ed L. wrote:
> I'd like to get some opinions on the best techniques for getting
> reliably consistent backups.  My current understanding is that the
> only reliable way to get a consistent backup of a running system is
> through pg_dump or pg_dumpall.

Correct (pretty much).

> More specifically, it is my
> understanding that file backups (tar, etc) do not guarantee
> consistency due to the potential lag in cache writes to disk from
> running transactions.

Correct.

> Is my understanding correct?  Are there other ways of getting
> consistent backups of a live system?

1. Stop postgresql alltogether, backup the entire data/base directory.
2. Replication (see mailing list archives/website for options)

pg_dump is almost certainly your best bet.

--
  Richard Huxton