Thread: PITR - "Rewind to snapshot" scheme

I have been following and experimenting a bit with PITR for a while,
and I wonder whether it is practical to use the PITR hooks to roll
back the database to a known state. The scenario is that I am
developing a script that will be massaging data in a medium size
database. A pg_restore of the pristine data takes ~35 minutes to
complete, if I can take a snapshot right after pg_restore, and use it
to later "rewind" to that point, I'll save 35 minutes every time I
need to test it.

The dev box where Pg runs has plenty of disk space to spare - and
it'll be a dedicated Pg instance. I've already raised wal_buffers to
20000 and also disabled wal fsync.

So my back-of-the-envelope plan is to

 - run pg_restore
 - setup wal archiving so that the logs aren't deleted
 - pg_start_backup('label'); cp -pr pgdata pgdata-snapshot ;
pg_stop_backup('label')
 - somehow remeber the transaction identifier

At this stage, I can run my data-garbling script, and to "rewind" I
should be able to

 - stop Pg
 - install an appropriate restore.conf that stops at the correct
transaction identifier
 - cp -pr pgdata-snapshot pgdata
 - start Pg

Would something like this work? My only worries at the moment seem trivial:

 - getting the transaction identifier
 - pruning the non-current timelines to avoid the archived logfiles
from eating me alive

cheers


martin

"Martin Langhoff" <martin.langhoff@gmail.com> writes:
> I have been following and experimenting a bit with PITR for a while,
> and I wonder whether it is practical to use the PITR hooks to roll
> back the database to a known state. The scenario is that I am
> developing a script that will be massaging data in a medium size
> database. A pg_restore of the pristine data takes ~35 minutes to
> complete, if I can take a snapshot right after pg_restore, and use it
> to later "rewind" to that point, I'll save 35 minutes every time I
> need to test it.

Seems overly complicated --- why don't you just shut down the postmaster
and take a tarball archive of the PGDATA tree?  Then to revert, stop
postmaster and untar.

            regards, tom lane

Martin Langhoff wrote:
> I have been following and experimenting a bit with PITR for a while,
> and I wonder whether it is practical to use the PITR hooks to roll
> back the database to a known state. The scenario is that I am
> developing a script that will be massaging data in a medium size
> database. A pg_restore of the pristine data takes ~35 minutes to
> complete, if I can take a snapshot right after pg_restore, and use it
> to later "rewind" to that point, I'll save 35 minutes every time I
> need to test it.
>
> The dev box where Pg runs has plenty of disk space to spare - and
> it'll be a dedicated Pg instance. I've already raised wal_buffers to
> 20000 and also disabled wal fsync.
>
> So my back-of-the-envelope plan is to
>
> - run pg_restore
> - setup wal archiving so that the logs aren't deleted
> - pg_start_backup('label'); cp -pr pgdata pgdata-snapshot ;
> pg_stop_backup('label')
> - somehow remeber the transaction identifier

If it's the only database in the cluster (or you can make it so) then
it's probably simpler just to:
1. Get database to state you want
2. Stop postgresql
3. Take file-level backup of everything in $PGDATA
4. Restart postgresql
5. Run tests
6. Stop postgresql
7. Restore file-level backup
8. Go to step 4

--
   Richard Huxton
   Archonet Ltd

On 4/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Seems overly complicated --- why don't you just shut down the postmaster
> and take a tarball archive of the PGDATA tree?  Then to revert, stop
> postmaster and untar.

Thanks for the tip!

cheers


martin

Or use a SAVEPOINT.  I don't know about the impact on resources if you
leave it hanging around for a long time, but I use these for exactly
the scenario you are talking about.

- Ian

On 4/16/07, Martin Langhoff <martin.langhoff@gmail.com> wrote:
> On 4/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Seems overly complicated --- why don't you just shut down the postmaster
> > and take a tarball archive of the PGDATA tree?  Then to revert, stop
> > postmaster and untar.
>
> Thanks for the tip!
>
> cheers
>
>
> martin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>