Re: [GENERAL] PITR - Rewind to snapshot scheme
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 [EMAIL PROTECTED] wrote: On 4/17/07, Tom Lane [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PITR - Rewind to snapshot scheme
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
2 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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] PITR - Rewind to snapshot scheme
On 4/17/07, Tom Lane [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] 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
2 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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] PITR - Rewind to snapshot scheme
Martin Langhoff [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
