Thread: Snapshot Copy of a Postgres DB
Cheers,
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Rowena Samuel Network Appliance, Inc.
Systems Engineer 201 City Centre Drive, Suite 900
Tel 905.804.1099 x3053 Mississauga, Ontario L5B 2T4
Cell 416.400.2060
Email rowena@netapp.com www.netapp.com
"I always wanted to be somebody, but now I realize I should have been more specific." - Lily Tomlin
On 5/1/06 10:37 AM, "Samuel, Rowena" <Rowena.Samuel@netapp.com> wrote: > Hi All, > > I have no Postgres knowledge. I am trying to find out if it would be > possible to Snapshot a Postgres database for a > PostgreSQL user who has a mixed environment and takes Snapshot copies of > all his other databases. > > What we would normally do for a database Snapshot in other database > environments is to write a script which places > the database into a hot backup mode, takes the Snapshot, and then brings > the database back into normal operational > mode. > > I have talked to a couple of PostgreQL DBAs and I understand that, while > there are hot backups with pg_dump, there is > no hot backup mode that the database can be placed into in Postgres. Can > I get confirmation of this? > > Assuming the answer to the above is "no", one DBA I talked to mentioned > that Postgres writes to a buffer(?) or log(?) > before the disk. So is it possible to just simply Snapshot the database > on the fly? How does Postgres handle a > situation where server running the database crashes? How does it handle > error recovery? This would be similar to > taking a snapshot on the fly.... pg_dump makes a dump of the database that is self-consistent. Other transactions can be occurring during the dump, but those will not be included in the dump. There is no need to bring the server down or to put it in backup mode to do this. As for recovery, there is usually little you need to do. Just start postgres again and generally all is well. If you have disk corruption or something more serious, then the recovery is probably similar to most other database products. You simply take your backup and recover with it. If you need point-in-time-recovery, you will probably want to look at the documentation more specifically, as the WAL files will need to be backed up as well for that (see the docs for details). Sean
On 5/3/06 8:18 AM, "Samuel, Rowena" <Rowena.Samuel@netapp.com> wrote: > > > Hi Sean, > > Thanks for the reply, but not exactly what I am looking for. > > My customer is currently using pg-dump but his database is large ( > hundreds of GBs ) so pg-dump is becoming disruptive for him. Takes a > while to backup and due to the number of writes on the database it does > seem to impact performance is slow down those writes. > > So, he is keen to use our Snapshot technology to take an almost > instantaneous snapshot of the DB. We have Snapshot management tools for > some databases but not Postgres as yet. Most DBs will have some kind of > backup mode like that of pg_dump that allows you to place the DB into a > quiet state but without doing a dump. > > It definitely appears this is not the case for Postgres as noone seems > to know about a "hot-backup" command state. > > So my question around recovery was, given that Postgres uses a WAL and > has flush markers or consistency points in the log, if we took a > Snapshot on the fly. How well would the database recover when we > restored it? I expect it would recover quite well but I am hoping > someone has some experience with this and understands the WAL enough to > answer??? Have you looked here: http://www.postgresql.org/docs/8.1/interactive/backup-online.html I think that describes in some detail the typical way of establishing PITR backups, but I haven't actually used this in practice (we are mainly read-only here). If performance is the main concern, then perhaps posting to either "general" or "performance" with some numbers will be beneficial, as there may be some tuning that needs to occur, like putting WAL files on a separate disk from the main tablespace, for example. Sean