From: | "Martin Langhoff" <martin(dot)langhoff(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | PITR - "Rewind to snapshot" scheme |
Date: | 2007-04-16 08:32:15 |
Message-ID: | 46a038f90704160132p288707aaidcb28529537429a3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hannes Dorbath | 2007-04-16 09:18:16 | Dump & restore required for x86 -> EM64T? |
Previous Message | Alban Hertroys | 2007-04-16 08:09:27 | Re: median query causes disk to fill up |