Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M43199=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1Y9DPC-0002zO-9X
 for pgsql-admin@arkaria.postgresql.org; Thu, 08 Jan 2015 13:45:42 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M43199=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1Y9DPB-0001SV-PP
 for pgsql-admin@arkaria.postgresql.org; Thu, 08 Jan 2015 13:45:41 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <ktm@aart.rice.edu>) id 1Y9DP9-0001SM-OF
 for pgsql-admin@postgresql.org; Thu, 08 Jan 2015 13:45:39 +0000
Received: from proofpoint1.mail.rice.edu ([128.42.201.100] helo=pp1.rice.edu)
 by magus.postgresql.org with esmtps
 (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80)
 (envelope-from <ktm@aart.rice.edu>) id 1Y9DP4-000535-BV
 for pgsql-admin@postgresql.org; Thu, 08 Jan 2015 13:45:38 +0000
Received: from pps.filterd (pp1.rice.edu [127.0.0.1])
 by pp1.rice.edu (8.14.5/8.14.5) with SMTP id t08Dg33f008856;
 Thu, 8 Jan 2015 07:45:29 -0600
Received: from mh1.mail.rice.edu (mh1.mail.rice.edu [128.42.201.20])
 by pp1.rice.edu with ESMTP id 1rp2ajjewk-1;
 Thu, 08 Jan 2015 07:45:29 -0600
X-Virus-Scanned: by amavis-2.7.0 at mh1.mail.rice.edu, auth channel
X-SMTP-Auth: no
X-SMTP-Auth: no
Received: from aart.rice.edu (aart.rice.edu [168.7.56.48])
 by mh1.mail.rice.edu (Postfix) with ESMTP id 68B24460197;
 Thu,  8 Jan 2015 07:45:29 -0600 (CST)
Received: by aart.rice.edu (Postfix, from userid 18612)
 id 4872B100737; Thu,  8 Jan 2015 07:45:29 -0600 (CST)
Date: Thu, 8 Jan 2015 07:45:29 -0600
From: "ktm@rice.edu" <ktm@rice.edu>
To: girish R G peetle <giri.anamika0@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: PostgreSQL Dump rate is too slow
Message-ID: <20150108134529.GE3783@aart.rice.edu>
References: 
 <CAKKd0663fQtOFMv-hWUDXrFecxmjeUW-=2PtkmTjxnaX-VsRRQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAKKd0663fQtOFMv-hWUDXrFecxmjeUW-=2PtkmTjxnaX-VsRRQ@mail.gmail.com>
User-Agent: Mutt/1.5.20 (2009-12-10)
X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0
 suspectscore=0 phishscore=0
 adultscore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1
 engine=7.0.1-1402240000 definitions=main-1501080129
X-Pg-Spam-Score: -4.2 (----)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

On Thu, Jan 08, 2015 at 11:05:54AM +0530, girish R G peetle wrote:
> Hi all,
> We have a customer who has 1TB database on a production server. They are
> trying dump based backup of this large database. Following dump command is
> being used.
> Dump rate is around 12 GB/hr, which will take lot of time for the backup to
> complete. This is affecting their production server.
> Is there is a way to increase dump data rate ?
> 
> pg_dump -U <User> -Fc -b --port=<Port> '<Db-Name>'
> 
> 
> PostgreSQL version : 9.2.4
> Platform : Linux
> 
> Thanks
> Girish

Hi Girish,

The custom format is compressed by default and your dump rate is hamstrung by
the slow compression algorithm being used. I our case for a smaller DB, turning
off compression completely (-Z 0) dropped our time to dump the DB from 6 hours
to about 20 minutes. You can push the uncompressed dump through a faster compression
engine using lzo, snappy or lz4 if you need to have compressed output due to space
limitations.

Regards,
Ken


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin