Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id D22B12E0038 for ; Mon, 14 Apr 2008 20:22:58 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 06702-02 for ; Mon, 14 Apr 2008 20:22:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from weatherbeeta.com.au (unknown [218.214.212.154]) by developer.postgresql.org (Postfix) with ESMTP id 31B9D2E002F for ; Mon, 14 Apr 2008 20:22:53 -0300 (ADT) Received: (qmail 12159 invoked from network); 14 Apr 2008 23:22:51 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;fd8c441596fb9e653aede8e43bacad5f; Received: from mail.weatherbeeta.com.au (10.20.0.2) by network-box.weatherbeeta.com.au with SMTP; 14 Apr 2008 23:22:51 -0000 Received: (qmail 13689 invoked by uid 1166); 15 Apr 2008 09:22:50 +1000 Received: from 172.20.0.155 by mail.weatherbeeta.com.au (envelope-from , uid 1165) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.6/2315. perlscan: 1.25-st-qms. Clear:RC:1(172.20.0.155):. Processed in 0.500505 secs); 14 Apr 2008 23:22:50 -0000 X-Antivirus-mail.weatherbeeta.com.au-Mail-From: phillip.smith@weatherbeeta.com.au via mail.weatherbeeta.com.au X-Antivirus-mail.weatherbeeta.com.au: 1.25-st-qms (Clear:RC:1(172.20.0.155):. Processed in 0.500505 secs Process 13642) Received: from it-phil.weatherbeeta.com.au (HELO wbaus090) (172.20.0.155) by mail.weatherbeeta.com.au with SMTP; 15 Apr 2008 09:22:46 +1000 From: "Phillip Smith" To: "'Ryan Wells'" , "'Tom Lane'" Cc: Subject: Re: Slow pg_dump Date: Tue, 15 Apr 2008 09:22:40 +1000 Organization: Weatherbeeta Pty Ptd Message-ID: <004a01c89e86$6f47c580$9b0014ac@wbaus090> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 Importance: Normal In-Reply-To: X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1208215371.619-12152-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.01/0.03 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.069 tagged_above=0 required=5 tests=AWL=-0.850, MIME_QP_LONG_LINE=1.819, RDNS_NONE=0.1 X-Spam-Level: * X-Archive-Number: 200804/130 X-Sequence-Number: 28894 > I ran pg_dump with the arguments you suggested, and my 4 GB test table finished > backing up in about 25 minutes, which seems great. The only problem = is that the > resulting backup file was over 9 GB. Using -Z2 resulting in a 55 = minute 6GB backup. >=20 > Here's my interpretation of those results: the TOAST tables for our = image files > are compressed by Postgres. During the backup, pg_dump uncompresses = them, and if > compression is turned on, recompresses the backup. Please correct me = if I'm wrong > there. >=20 > If we can't find a workable balance using pg_dump, then it looks like = our next > best alternative may be a utility to handle filesystem backups, which = is a littlescary for on-site, user-controlled servers. Ryan -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Saturday, April 12, 2008 9:46 PM To: Ryan Wells Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Slow pg_dump=20 "Ryan Wells" writes: > We have several tables that are used to store binary data as bytea (in > this example image files), Precompressed image formats, no doubt? =20 > pg_dump -i -h localhost -p 5432 -U postgres -F c -v -f > "backupTest.backup" -t "public"."images" db_name Try it with -Z0, or even drop the -Fc completely, since it's certainly = not very helpful on a single-table dump. Re-compressing already compressed = data is not only useless but impressively slow ... Also, drop the -i, that's nothing but a foot-gun. regards, tom lane --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: = http://www.postgresql.org/mailpref/pgsql-admin THINK BEFORE YOU PRINT - Save paper if you don't really need to print this *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail.