Обсуждение: 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 >