Fwd: Re: [Gnumed-devel] update database - no problem

[Syan Tan]

So the procedure is to do a running base backup by
archiving the contents of the pgdata directory, but surrounding
it with some function calls that record from what time and
at what WAL file was current. 
afterwards, incremental backup is just copying pg_xlog 
WAL files to an archive periodically.
Recovery is done by replacing pgdata contents with the
unarchived base backup, putting a recovery.conf file
in the pgdata directory, and starting up the postmaster
which will use the recovery.conf file parameters to find
the archived pg_xlog file, and replay all modifications
from the time the base backup was made, upto the last 
archived WAL file , in the order of the WAL files (given
by their filenames  ).



Sent: Tue Oct  9 11:28
Subject: Fwd: Re: [Gnumed-devel] update database - no problem


true, the snapshot at a particular timepoint will include
the WAL files at a particular timepoint, so there can't be
a case of no rollback and no roll forward. 
I just read the point in time recovery (PITR) section of
the postgresql documentation : in my debian setup, 
I have a directory /var/lib/postgresql/8.1/main/pg_xlog 
which contains files 00000001000000000000001B 
00000001000000000000001C ... etc ..
whenever I stop the postmaster, and put a file called 
recovery.conf with the parameters specified in the
documentation page, and restart the postmaster, the postmaster
will configure according to recovery.conf, copy 
archived WAL files back into pg_xlog, and replay any 
WAL files from the necessary time , starting with the necessary
WAL file. These two pieces of info are stored in a file named
"" where backup_label is a string passed to 
a function pg_start_backup() . On doing "select 
pg_start_backup('my_backup_base') " , it will note the time
this function is called, and the current last pg_xlog WAL 
file, so the base backup determines from when incremental backups
will be applied. 
After the postmaster has run the recovery, it makes the 
recovery.conf file renamed to recovery.done, and so stopping and 
starting it again, won't issue another recovery operation.
This seems dependent on the DBA not accidently running a recovery
operation on anything other than the base backup , which is
basically a filesystem dump restore e.g. not accidently 
running recovery twice. 

http://www.postgresql.org/docs/8.1/static/backup-online.html

thanks for raising the issue, never piqued my interest enough
to know how it works, so hopefully your thread helped a few 
people.


On Tue Oct  9 10:01 , Karsten Hilbert address@hidden> 
sent:
 
>On Tue, Oct 09, 2007 at 05:37:32AM +0000, Syan Tan wrote:
>
>> another end-to-end problematic unworkable duplication ? 
>> ZFS might snapshot a postgres db in an inconsistent state.
>Not really. Isn't that what ACID-compliant RDBMs pride
>themselves in: what *is* on disk *is* consistent (not
>necessarily complete from a users point of view but
>consistent from a technical POV).
>
>If so, that *can* be snapshotted by the FS.
>
>Karsten
>-- 
>GPG key ID E4071346 @ wwwkeys.pgp.net
>E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346