speed up restore from dump

From: Joao Ferreira gmail <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: speed up restore from dump
Date: 2008-10-30 18:25:18
Message-ID: 1225391118.4340.34.camel@debj4n.critical.pt
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all,

I've been tring to speed up the restore operation of my database without
success.

I have a 200MB dump file obtained with 'pg_dumpall --clean --oids'.
After restore is produces a database with one single table (1.000.000)
rows. I have also some indexes on that table. that's it.

It always takes me about 20 minutes to reload the data, whatever
settings I change.

I have so far touched these settings:
- fsync = off
- shared_buffers = 24MB
- temp_buffers = 24Mb
- maintenance_work_mem = 128MB
- full_page_writes = off
- wal_writer_delay = 10000
- checkpoint_segments = 200
- checkpoint_timeout = 1800
- autovacuum = off

I started with a default instalation. first I changed fsync to off, then
I started touching other cfg params. but I always get around 20 minutes
(21, 19, 18)....

Can I expect these 20 minutes to be significantly reduced ?

What other cfg paramenters shoud I touch ?

Can anyone shed some light on this ?

any faster approach to upgrade from 8.1 to 8.3 ?

thank you

Joao

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alan Hodgson 2008-10-30 18:39:31 Re: speed up restore from dump
Previous Message Martijn van Oosterhout 2008-10-30 18:22:11 Re: bytea field, a c function and pgcrypto driving me mad