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

[Syan Tan]

And just to overstuff any appetite for backup procedures,
here was what the docs said about filesystem snapshots and rsync
backups.


An alternative file-system backup approach is to make 
a "consistent snapshot" of the data directory, if the file system 
supports that functionality (and you are willing to trust that it 
is implemented correctly). The typical procedure is to make 
a "frozen snapshot" of the volume containing the database, then 
copy the whole data directory (not just parts, see above) from the 
snapshot to a backup device, then release the frozen snapshot. 
This will work even while the database server is running. However, 
a backup created in this way saves the database files in a state 
where the database server was not properly shut down; therefore, 
when you start the database server on the backed-up data, it will 
think the server had crashed and replay the WAL log. This is not a 
problem, just be aware of it (and be sure to include the WAL files 
in your backup). 
 If your database is spread across multiple file systems, there 
may not be any way to obtain exactly-simultaneous frozen snapshots 
of all the volumes. For example, if your data files and WAL log 
are on different disks, or if tablespaces are on different file 
systems, it might not be possible to use snapshot backup because 
the snapshots must be simultaneous. Read your file system 
documentation very carefully before trusting to the 
consistent-snapshot technique in such situations. The safest 
approach is to shut down the database server for long enough to 
establish all the frozen snapshots. 
 Another option is to use rsync to perform a file system backup. 
This is done by first running rsync while the database server is 
running, then shutting down the database server just long enough 
to do a second rsync. The second rsync will be much quicker than 
the first, because it has relatively little data to transfer, and 
the end result will be consistent because the server was down. 
This method allows a file system backup to be performed with 
minimal downtime. 
 Note that a file system backup will not necessarily be smaller 
than an SQL dump. On the contrary, it will most likely be larger. 
(pg_dump does not need to dump the contents of indexes for 
example, just the commands to recreate them.)



On Tue Oct  9 11:34 , Syan Tan  sent:

>
>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
>
>
>
>
>
>
>
>_______________________________________________
>Gnumed-devel mailing list
>address@hidden
>http://lists.gnu.org/mailman/listinfo/gnumed-devel