Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id ACD6D2E0046 for ; Mon, 14 Apr 2008 19:35:39 -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 14684-08 for ; Mon, 14 Apr 2008 19:35:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from exchange.DOCS.COM (exchange.docs.com [75.13.188.5]) by developer.postgresql.org (Postfix) with ESMTP id 988A52E004D for ; Mon, 14 Apr 2008 19:35:36 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: Slow pg_dump X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Mon, 14 Apr 2008 17:35:32 -0500 Message-ID: In-Reply-To: <14644.1208054766@sss.pgh.pa.us> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Slow pg_dump Thread-Index: AcidEIc4cJl2j+FuQIu58ER5uIaetgBK6c7Q References: <14644.1208054766@sss.pgh.pa.us> From: "Ryan Wells" To: "Tom Lane" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.7 tagged_above=0 required=5 tests=AWL=0.700 X-Spam-Level: X-Archive-Number: 200804/129 X-Sequence-Number: 28893 The images are stored in whatever format our users load them as, so we don't have any control over their compression or lack thereof. 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. 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. 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 little scary 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=20 > "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