To: "Emils Klotins" <emils@dot.lv>
cc: pgsql-sql@postgresql.org
Subject: Re: Backup? 
In-reply-to: <3958C38D.19942.24191291@localhost> 
References: <3958C38D.19942.24191291@localhost>
Comments: In-reply-to "Emils Klotins" <emils@dot.lv>
	message dated "Tue, 27 Jun 2000 15:09:01 +0200"
Date: Tue, 27 Jun 2000 10:43:13 -0400
Message-ID: <9737.962116993@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Emils Klotins" <emils@dot.lv> writes:
> Would it be possible to backup a db by just copying the dir struct 
> under the PG_DATA or must I absolutely use pg_dump?

If you ensure nothing at all is happening (as in "shut down the
postmaster" --- I would not trust any half measures) then a dump of
PGDATA would work.  If anything is going on then a dump done that way
will be inconsistent.

> As I understand pg_dump could be comparatively slower, plus it
> produces a text format dumpfile, which could be kind of large in my db
> in future.

pg_dump is slower, but since it can run in parallel with other
activities, I'm not sure that the extra time is a material
disadvantage.  I wouldn't assume that the text dumpfile will be
larger than a tar of the database directory either --- very likely
it'd be the other way round.  Finally, the text dump is amenable to
editing, selective restoration, etc, which you *cannot* do with
a directory dump --- the only thing a directory dump is good for is
restoring the entire DB to exactly where it was.

The main thing that makes a directory dump less useful than it might
appear to be is that the logical state of each table is split between
the table file proper (containing tuples marked with transaction
insert/delete stamps) and pg_log (which tells you which transactions
committed and which did not).  Thus you cannot restore just one
table file; you have to restore pg_log as well, and that makes it
an all-or-nothing proposition.

			regards, tom lane