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