Fastest way to restore a database

From: William Garrison <postgres(at)mobydisk(dot)com>
To: Postgres General List <pgsql-general(at)postgresql(dot)org>
Subject: Fastest way to restore a database
Date: 2008-09-12 15:57:57
Message-ID: 48CA9185.5050304@mobydisk.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I know that PostgreSQL is slow at restoring databases. But there are
some tricks to use when speeding it up. Here is a brief list I compiled
from reading the docs and reading some forums. Is there a definitive
list of things to do?

* Turn off fsync
So it won’t flush after every commit
* Turn off full_page_writes
Don’t write the WAL archives in a safe way. But we don’t need WAL
archives during a restore. Future versions of postgres will let you turn
off WAL archives entirely
* Turn off archive_mode
This disables the archiving of WAL logs
* Increase the checkpoint_segments parameter (the default is 3 – so...
maybe 10?)
Increases the number of transactions that can happen before a WAL checkpoint
* The --single-transaction paremeter to pg_restore *might* speed it up
One transaction is more efficient, but an entire DB restore might be too
big of a transaction. So I’m not so sure about this option
* Increase the maintenance_work_mem setting to 512MB
Gives more memory to CREATE_INDEX commands, which is part of the restore
process
* (PostgreSql 8.3 only) Turn off synchronous_commit
This makes it so that the database won’t wait for the WAL checkpoint to
be completed before moving on to the next operation. Again, we don’t
want WAL archiving during a restore anyway.

Are any of the above items not going to help? Anything I'm missing? Is
there a way to disable foreign key constraints during the restore since
I know it is already a good database?

I am using postgreSQL 8.2.9 on Win32

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oleg Bartunov 2008-09-12 16:02:22 Re: TSearch2: find a QUERY that does match a single document
Previous Message Sam Mason 2008-09-12 15:57:45 Re: Converting string to IN query