From: Joshua D. Drake <jd@commandprompt.com>
To: Eduardo Morras <emorrasg@yahoo.es>
To: pgsql-admin@postgresql.org
Subject: Re: PostgreSQL Dump rate is too slow
Date: Wed, 14 Jan 2015 13:02:42 -0800
Message-ID: <54B6D972.8010304@commandprompt.com> (raw)
In-Reply-To: <20150114212058.de1c87d91a851db0bce1f1fa@yahoo.es>
References: <CAKKd0663fQtOFMv-hWUDXrFecxmjeUW-=2PtkmTjxnaX-VsRRQ@mail.gmail.com>
<20150114212058.de1c87d91a851db0bce1f1fa@yahoo.es>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 01/14/2015 12:20 PM, Eduardo Morras wrote:
>
> On Thu, 8 Jan 2015 11:05:54 +0530
> girish R G peetle <giri.anamika0@gmail.com> 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>'
>>
>
> Do not use pg_dump compression, pipe output to xz
>
> % pg_dump -U <User> -Fc -b --port=<Port> '<Db-Name>' | xz -3 dump.xz
>
> or pipe xz output to other program.
>
> When I looked for the same problem in 8.3-8.4 versions, the bottleneck was in accessing TOAST tables, it's content was decompressed, dumped and recompressed again, don't know if it has changed in current versions.
Don't do this. You are still looking at an extremely slow dump. Instead
set up a warm or hot standby or use pg_basebackup.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should
not be surprised when they come back as Romans."
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jd@commandprompt.com, emorrasg@yahoo.es
Subject: Re: PostgreSQL Dump rate is too slow
In-Reply-To: <54B6D972.8010304@commandprompt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox