Very Very Slow Database Restore

From: Joseph McClintock <joe(dot)mcclintock(at)antics(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Very Very Slow Database Restore
Date: 2006-12-20 20:34:20
Message-ID: 1166646860.3941.4.camel@mcclintock.anticsoffline.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I have inherited the on going management of our company's PostgreSQL 8.0
database. I'm having a very serious problem with the restore process.
I've been running periodic pg_dumps from our production database and
then restoring the data on our development system. That has been working
find until now. Previously when I ran:

pg_dump -U username database > db.backup

I would get me a 800+ MB file and I could restore with

psql -U username -d database < db.backup

which took 15 minutes or so.

Now our database has grown and the pg_dump give me a 2.1 GB file which
is taking 12 hours or more to restore, Yickes! I've tried compressing
the dump file:

pg_dump -F c -U username database > db.backup

and then run

pg_restore -U username -d database db.backup

but no difference and probably its slower because the restore has to
decompress the dump.

The database configuration is pretty much out of the box. The only thing
that's been changed is the number of allowed db connections which I
don't think has much barring on this problem.

Any thoughts about this problem would be appreciated.

Thanks

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Brad Nicholson 2006-12-20 21:04:16 Re: Suggestions needed about how to dump/restore a database
Previous Message Thomas Markus 2006-12-20 10:00:17 Re: Suggestions needed about how to dump/restore a database