Thread: Backup?

Hello,

I had a look for 'backup' on the mailing list archives and strangely 
enough didn't find a message containing the word in admin, general 
and sql mailing lists... I am sure this must have been asked before, 
but still:

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? I am 
using Postgres 7.0. The purpose of the backup is of course to have 
a copy handy if eg the harddrive breaks. 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.

Thanks in advance,

Emils

"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

> Hello,
>
> I had a look for 'backup' on the mailing list archives and strangely
> enough didn't find a message containing the word in admin, general
> and sql mailing lists... I am sure this must have been asked before,
> but still:
>
> 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? I am
> using Postgres 7.0. The purpose of the backup is of course to have
> a copy handy if eg the harddrive breaks. 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.

The pg_dump will in general be smaller.  There is a fair amount of overhead
in fitting rows into page boundaries.  Also the indexes are not backed up.
Here are some datapoints from my system.  I have a datadirectory that is
2.4GB.  A pg_dump from that is 800MB (about 400MB compressed).  My nightly
dump currently takes about 10 minutes during which the database is not down.
A reload takes 100 minutes including a vacuum analyze.  I also sleep better
knowing the data is in a readable text format that I could work with if I
had to.