Re: a few questions on backup

Tom Lane wrote:
> No. You have to have an actual archive_command script copying the WAL
> segments somewhere else when told to. An asynchronous copy of the xlog
> directory will be nothing but garbage, because we recycle WAL segments
> as fast as we can (ie, as soon as the archive_command claims to have
> saved the data).

Mmm, sorry I'm not sure I'm following here. Maybe I should provide some
background. In my pg_xlog directory I see five files, WAL segments, I
suppose. Only one (as I expected) is begin currently used, the others
are old (one a couple of days old).

When PG performs a switch from one segment to another one (I assume it
recycles the oldest available), does it archive the recycled one (before
starting using it of course) or the just-filled one? If it's the one
being recycled, it means that in my setup it would takes two days to
archive a segment since it stopped being used. Am I missing something?

> 1) 2) and 3) are OK, but you need to use archive_command to collect the
> xlog segments.
>
> Actually ... given your low requirements, I wonder why you don't just
> stop the postmaster, tar the datadir, start the postmaster.

Well, currently we do a pg_dump. The database mainly supports dynamic
websites. It's very unlikely they get updated at the time the backup
runs, and overall there is little updating even during the day, but I
don't like stopping the postmaster because, even if the write load is
negligible, the read one might be not. It's still small enough that a
tar (to disk) might take only a minute or two to complete, but yet it's
a minute of downtime for the web sites. If I can avoid that, why not?

I'm not unsatisfied with pg_dump, and I agree that with my requirements
the whole issue is accademic. I just wanted to learn how it works
exactly, such knowledge could provide useful for doing the Right Thing
in case of troubles. Maybe it's the right time for me to have a look at
the source...

Hannes Dorbath wrote:
> lvcreate -s -L5G -nbackup /dev/foo/postgresql
> mount /dev/foo/backup /mnt/backup-snap
> tar jcpvf pg-backup-<time_stamp>.bz2 /mnt/backup-snap
>
> You can't do much wrong with that, it's fast and easy to use.

Been there, done that. In my environment (Fedora Core 6) it's fast and
easy, but not reliable, unfortunately. Sometimes the snapshot won't get
created, sometimes it won't get removed after the backup is done.

.TM.