Fastest pq_restore?

From: Michael Andreasen <michael(at)dunlops(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Fastest pq_restore?
Date: 2011-03-17 14:25:16
Message-ID: AANLkTi=x88_1hTotWNkw8XAGxt2iXs1JmW0WZ4sCtWXm@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I've been looking around for information on doing a pg_restore as fast as
possible. It is for a backup machine so I am not interested in anything like
crash recovery or anything else that would impact speed of load. I just want
to go from no database to database there as fast as possible. The server is
for postgresql only and this is the only database, sp both system at
postgres can be set however is required for the fast load.

Currently I am using a twin processor box with 2GB of memory and raid 5
disk.

I start postgres before my load with these settings, which have been
suggested.

shared_buffers = 496MB
maintenance_work_mem = 160MB
checkpoint_segments = 30
autovacuum = false
full_page_writes=false

maintenance_work_mem and checkpoint_segments were advised to be increased,
which I have done, but these are just guess values as I couldn't see any
advise for values, other than "bigger".

I restore like this;

pg_restore -Fc -j 4 -i -O -d my_db my_db_dump.tbz

Even as this, it is still slower than I would like.

Can someone suggest some optimal settings (for postgresql 9) that will get
this as quick as it can be?

Thanks.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kevin Grittner 2011-03-17 14:49:45 Re: Updating histogram_bounds after a delete
Previous Message Derrick Rice 2011-03-17 13:27:41 Re: Updating histogram_bounds after a delete