From: Ryan Wells <ryan.wells@soapware.com>
To: Ryan Wells <ryan.wells@soapware.com>
To: pgsql-admin@postgresql.org
Subject: Slow pg_dump
Date: Sat, 12 Apr 2008 19:59:09 -0500
Message-ID: <EE6D03C0EF73D14E8034C37CA9B6277401DA03@exchange.DOCS.COM> (raw)
References: <9CC13EC943102E46BA4E328F9FBCDB36255FB57B@exchange.DOCS.COM>
We're having what seem like serious performance issues with pg_dump, and I hope someone can help.
We have several tables that are used to store binary data as bytea (in this example image files), but we're having similar time issues with text tables as well.
In my most recent test, the sample table was about 5 GB in 1644 rows, with image files sizes between 1 MB and 35 MB. The server was a 3.0 GHz P4 running WinXP, with 2 GB of ram, the backup stored to a separate disk from the data, and little else running on the sytem.
We're doing the following:
pg_dump -i -h localhost -p 5432 -U postgres -F c -v -f "backupTest.backup" -t "public"."images" db_name
In the test above, this took 1hr 45min to complete. Since we expect to have users with 50-100GB of data, if not more, backup times that take nearly an entire day are unacceptable.
We think there must be something we're doing wrong. A search turned up a similar thread (http://archives.postgresql.org/pgsql-performance/2007-12/msg00404.php), but our number are so much higher than those that we must be doing something very wrong. Hopefully, either there's a server setting or pg_dump option we need to change, but we're open to design changes if necessary.
Can anyone who has dealt with this before advise us?
Thanks!
Ryan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ryan.wells@soapware.com
Subject: Re: Slow pg_dump
In-Reply-To: <EE6D03C0EF73D14E8034C37CA9B6277401DA03@exchange.DOCS.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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