Re: Using PITR Backup and Recovery

In response to "beer(at)cmu(dot)edu" <beer(at)cmu(dot)edu>:

> We recently moved to PITR backup and recovery solution as defined in the
> documentation. Our basic setup executes the backup start command, and then
> takes a snapshot of the filesystem and backups wal files. However, we have
> database files ( not wal files ) that change while the system is in backup
> mode. This happens during every backup. Is this normal?

Maybe. Not entirely sure I understand you're meaning, but ...

My understanding is that pg_start_backup()'s purpose is to mark the database
so it knows what the last complete transaction was before it was started. This
doesn't prevent PostgreSQL from making changes to DB files, it just ensures
that in the event of a restore, PG knows where to start as far as the data
files and the WAL log are concerned.

I'm curious as to why files would be changing if you made a filesystem snapshot,
but that wouldn't be a problem with PostgreSQL, it would be a problem with the
filesystem code. Or I could be misunderstanding what you mean.

In any event, if database activity is occurring while the backup is running,
PostgreSQL's data files will continue to change, but the archive of WAL logs
will allow the system to recover from inconsistent changes during the
recovery phase.

I don't know if anyone's done extensive testing to know just how reliable PITR
is, but it's worked every time for me. One caveat: you can't recover PITR
data from an amd64 system to an i386 system :D PostgreSQL's data (and possibly
the WAL logs as well) is architecture dependent. This can be a royal pain if
you don't know about it and you have a mix of architectures.

--
Bill Moran
http://www.potentialtech.com