pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVery Very Slow Database Restore
4+ messages / 3 participants
[nested] [flat]
* Very Very Slow Database Restore
@ 2006-12-20 20:34 Joseph McClintock <joe.mcclintock@antics.com>
0 siblings, 1 reply; 4+ messages in thread
From: Joseph McClintock @ 2006-12-20 20:34 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Very Very Slow Database Restore
@ 2006-12-21 05:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joseph McClintock <joe.mcclintock@antics.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-12-21 05:47 UTC (permalink / raw)
To: joe.mcclintock@antics.com; +Cc: pgsql-admin
Joseph McClintock <joe.mcclintock@antics.com> writes:
> 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:
Compressing the dump file will make the restore slower, most likely,
because of the extra CPU effort to decompress. It certainly won't
create any great savings.
> The database configuration is pretty much out of the box.
You probably want to increase maintenance_work_mem (to speed index
creation) and checkpoint_segments (general rule for any update-intensive
situation) ... and for 8.0 you'd maybe need larger shared_buffers etc.
The postgresql techdocs website has lots of general advice about
parameter settings --- the only thing specific to restore is you
want high maintenance_work_mem.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Very Very Slow Database Restore
@ 2006-12-21 17:43 Joseph McClintock <joe.mcclintock@antics.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Joseph McClintock @ 2006-12-21 17:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks for the feed back. Doubling the work_men from 10240 to 20480
seemed to make the process faster but then I ran out of disk space.
Shared buffers are at 2000 which I can try increasing.
I now need to move the database off the system partition to my working
partition which has more space. I hope don't have to re-install
Postges, that would be a real bummer.
--Joe
On Thu, 2006-12-21 at 00:47 -0500, Tom Lane wrote:
> Joseph McClintock <joe.mcclintock@antics.com> writes:
> > 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:
>
> Compressing the dump file will make the restore slower, most likely,
> because of the extra CPU effort to decompress. It certainly won't
> create any great savings.
>
> > The database configuration is pretty much out of the box.
>
> You probably want to increase maintenance_work_mem (to speed index
> creation) and checkpoint_segments (general rule for any update-intensive
> situation) ... and for 8.0 you'd maybe need larger shared_buffers etc.
> The postgresql techdocs website has lots of general advice about
> parameter settings --- the only thing specific to restore is you
> want high maintenance_work_mem.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Very Very Slow Database Restore
@ 2006-12-22 10:08 Federico <rotellaro@gmail.com>
parent: Joseph McClintock <joe.mcclintock@antics.com>
0 siblings, 0 replies; 4+ messages in thread
From: Federico @ 2006-12-22 10:08 UTC (permalink / raw)
To: pgsql-admin
On 12/21/06, Joseph McClintock <joe.mcclintock@antics.com> wrote:
> Thanks for the feed back. Doubling the work_men from 10240 to 20480
> seemed to make the process faster but then I ran out of disk space.
> Shared buffers are at 2000 which I can try increasing.
>
> I now need to move the database off the system partition to my working
> partition which has more space. I hope don't have to re-install
> Postges, that would be a real bummer.
>
Hi Joseph, to move the database cluster to another partition is very
simple with postgresql.
You need to shutdown the postmaster, after this copy your database
cluster directory
preserving user and permissions to the new location, update the
$PGDATA variable for the user postgres with the value of the new
location , startup the postmaster, enjoy ;)
Best Regards and Merry Christmas
Federico Campoli
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-12-22 10:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-20 20:34 Very Very Slow Database Restore Joseph McClintock <joe.mcclintock@antics.com>
2006-12-21 05:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-21 17:43 ` Joseph McClintock <joe.mcclintock@antics.com>
2006-12-22 10:08 ` Federico <rotellaro@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox