Re: a few questions on backup

Hi,

The procedure you followed is for online backups. The backups are useless
unless you set archive_command in your postgresql.conf file. This command
will copy the filled transaction log to a directory where you specified in
your archive_command. The PG won't write to transaction logs unless it takes
the copy of the filled transaction log. These copied transaction logs are
used during the recovery process for roll forwarding.

If you don't set the archive_command then you have to go for cold backups
where database will be stopped and data directory is backed up.

Hope this helps.

Regards,
Prashant Ranjalkar
EnterpriseDB Corporation

On 5/14/07, Marco Colombo <pgsql(at)esiway(dot)net> wrote:
>
> Hello,
> I have a few questions on backuping a PostgreSQL server (lets say
> anything 8.x.x). I've read "Continuous Archiving and Point-In-Time
> Recovery (PITR)" in the manual I'm still missing something...well
> actually I think I don't but I've been debating on this with a friend
> for a while, and there's something we don't seem to agree on, so I need
> some bits of clarification. :)
>
> Ok, let's say what I really need is "poor man's" PITR. That is, I
> perform only one daily full backup, and I need to be able to restore the
> database contents as they were at backup time. I don't even need to know
> the exact time (yes, that means I don't really care about which
> transactions turn out to be committed and which don't). Mmm, ok that
> can't be rightfully called PITR at all, maybe.
>
> Am I right in assuming that the following procedure is ok?
>
> 1) issue pg_start_backup();
> 2) copy (or tar or cpio) the data dir, w/o pg_xlog/
> 3) issue pg_stop_backup();
> 4) copy (or tar or cpio) pg_xlog/ contents.
>
> That's all. Please note that I'm doing NO WAL archiving.
>
> Whether this is going to work or not is based on what exactly
> pg_start_backup() does. According to him (my friend), it may simply
> prevent PostgreSQL from writing to data files until pg_stop_backup() is
> issued, thus kind of enforcing a snapshot on the data files themselves.
> Changes go to WAL only, and they are "played" on data files only after
> the pg_stop_backup(). Hmm, I don't really like that. But if so, I don't
> even need step 4), since the tar backup would be consistent. I'm able to
> restore from that only, and data are those at the time of
> pg_start_backup().
>
> Or, (that's me) pg_start_backup() only ensures that full_page_writes is
> enabled during the backup phase, so that any change to data files can be
> undone/redone based on the contents of WAL segments. The tar archive
> contains potentially inconsistant data, that's why I need WAL segments
> too. At restore time, PG eventually performs a crash recovery, and data
> are those at the time of step 4) (which is an interval really, some time
> between the start and the end of the copy, but I don't need to be able
> to tell the exact time anyway).
>
> BTW, I see a vulnerability in the above procedure... if there's enough
> write activity so that PostgreSQL recycles WAL segments between 1) and
> 4), some changes may be lost. At step 4) I need to save _all_ WAL
> segments that have been produced during the backup time. I assume that
> activity at backup time is low enough that the event is "extremely
> unlikely". Another doubt I have is about WAL checkpointing... is it
> possible that a checkpoint happens during step 2), and the tar archive
> gets data files both from before and from after the checkpoint (which I
> think is bad) or does pg_start_backup() prevent WAL checkpointing, too?
>
> Finally, if I'm missing something and the above is wrong, I think that
> the only way to perform a full backup on a live database at filesystem
> level, is to enable WAL archiving as the first step of the backup
> procedure (assuming it's not usually on, of course), and later save all
> the WAL segments that were _archived_ during that time, including the
> one made at pg_stop_backup(), on the same backup medium.
>
> If I understand the documentation right, nothing breaks if the
> archive_command just returns OK w/o saving anything during regular
> operation, and starts saving segments only during the backup time... I
> mean, that prevents me from doing arbitrary PITR after the backup, but
> all I want to do is performing a full backup, w/o being able to do any
> partial backup after that. If so, I may write a script that does nothing
> most of the time, and archives WAL segments only to be included in the
> full backup.
>
> TIA, (and sorry if the above sounds a bit messy)
> .TM.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>