Skip site navigation (1) Skip section navigation (2)

Re: pg_dump Performance

From: "Ryan Wells" <ryan(dot)wells(at)soapware(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: pg_dump Performance
Date: 2008-04-18 15:45:13
Message-ID: EE6D03C0EF73D14E8034C37CA9B627740289CE@exchange.DOCS.COM (view raw, whole thread or download thread mbox)
Lists: pgsql-adminpgsql-es-ayuda
Sorry for the double post.  Our email server had some problems
overnight.  Feel free to ignore this.  We're still working on the issue
using suggestions from last week, and we're seeing some improvements.


-----Original Message-----
From: Ryan Wells 
Sent: Friday, April 11, 2008 9:42 PM
To: pgsql-admin(at)postgresql(dot)org
Subject: pg_dump Performance

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, but none of our
searches have turned up anything (we likely just don't know the right
search terms).  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?



In response to

pgsql-admin by date

Next:From: Peter KoczanDate: 2008-04-18 17:43:20
Subject: Re: PG 8.3 and kerberos failures
Previous:From: David JorjolianiDate: 2008-04-18 06:18:35
Subject: Re: Database update problem from crontab on ubuntu server

pgsql-es-ayuda by date

Next:From: Julio Cesar Sánchez GonzálezDate: 2008-04-18 23:01:15
Subject: Re: Quitar acentos de palabras codificadas en UTF8
Previous:From: Jaime CasanovaDate: 2008-04-18 15:41:43
Subject: Re: por que SEQ SCAN Y NO INDEX SCAN

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group