Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 0D67A2E004D for ; Sat, 12 Apr 2008 22:00:43 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 17898-04 for ; Sat, 12 Apr 2008 22:00:31 -0300 (ADT) X-Greylist: delayed 22:18:43.783595 by SQLgrey-1.7.5 Received: from exchange.DOCS.COM (unknown [75.13.188.5]) by developer.postgresql.org (Postfix) with ESMTP id 46BA92E002E for ; Sat, 12 Apr 2008 22:00:38 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C89D01.C91D02AF" Subject: Slow pg_dump X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Sat, 12 Apr 2008 19:59:09 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Slow pg_dump Thread-Index: AcicRsQ2KtAJv4NNS9Wi+9C7pSTgkQAutCat References: <9CC13EC943102E46BA4E328F9FBCDB36255FB57B@exchange.DOCS.COM> From: "Ryan Wells" To: "Ryan Wells" , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.859 tagged_above=0 required=5 tests=AWL=0.758, HTML_MESSAGE=0.001, RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200804/112 X-Sequence-Number: 28876 This is a multi-part message in MIME format. ------_=_NextPart_001_01C89D01.C91D02AF Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We're having what seem like serious performance issues with pg_dump, and = I hope someone can help. =20 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: =20 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 =20 ------_=_NextPart_001_01C89D01.C91D02AF Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Slow pg_dump

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.p= hp), 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






------_=_NextPart_001_01C89D01.C91D02AF--