Re: Problem about Backup

On Mon, Sep 08, 2003 at 11:26:20 +0530,
Ramesh PAtel <ramesh(at)banas(dot)guj(dot)nic(dot)in> wrote:
> Hi All
>
> I am working On Postgresql.
>
> i have on Program VET and it's database on Postgresql this programe
> run on diff. five Place and all five place separet server. and not connected eachother.
> now we want all place data store in one server and data will update Weekly.
>
> how to i backup data whoes INSERT, UPDATE, AND DELETE between two date.

When you do a back up using pg_dumpall (or pg_dump) you get a consistant
dump fo the database as it was at the start of the dump. You can't get
a constant dump from a particular time in the past.

Postgres doesn't have an incremental back up system, which is what I think
you are trying to do. In some cases you might be able to work around this
by using backed up and deleted flags and not really deleting any old data
until it has been included in a back up.

To do this newly inserted records don't get the backed up flag set.
When you delete a record, if it doesn't have the backed up flag set
you can just delete it. Otherwise you clear the backed up flag and
set the deleted flag. Updates should be handle as a delete and an insert.

To do a back up, you use serializable isolation mode and select all of
the records that don't have the backed up flag set and dump them.
Then you delete all of the records with the delete flag set.
Then you set the backed up flag for all records that don't have it set.
Then if everything has gone well you commit the transaction.
Then you probably want to run vacuum or vacuum analyze.

Your applications need to ignore any records with the delete flag set.