Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump backup utility is taking more time around 24hrs to take thebackup of 28GB

pg_dump backup utility is taking more time around 24hrs to take thebackup of 28GB

From
Raghavendra Rao J S V
Date:
Hi All,

We are using pg_dump backup utility in order to take the backup of the database. Unfortunately,it is taking around 24hrs of time  to take the backup of  28GB database. Please guide me how to reduce the time and is there any parameter need to be modified which will help us to reduce the backup time. We are using Postgres 9.2 version

Note:-Kindly suggest me options using pg_dump only.

--
Regards,
Raghavendra Rao 

Re: pg_dump backup utility is taking more time around 24hrs to takethe backup of 28GB

From
Ozz Nixon
Date:
There are many possible problems, could you share your command line?

For example. Dump on SAME drive as the DB files, can produce disk contention. Dump across the network, can have packet collision of network latency. I dump 20gb test server in a matter of a couple minutes here. But, I run multiple disk controllers, data drive one 1 iSCSI and archives on another iSCSI - both run SSD ... so obviously, in this design I am simply moving RAM from 1 gig channel to another 1 gig channel, both are private circuits - no network conflict, and I average no less than 1GBps A to B stack performance.

On Thu, Oct 18, 2018 at 8:25 AM Raghavendra Rao J S V <raghavendrajsv@gmail.com> wrote:
Hi All,

We are using pg_dump backup utility in order to take the backup of the database. Unfortunately,it is taking around 24hrs of time  to take the backup of  28GB database. Please guide me how to reduce the time and is there any parameter need to be modified which will help us to reduce the backup time. We are using Postgres 9.2 version

Note:-Kindly suggest me options using pg_dump only.

--
Regards,
Raghavendra Rao 

RE: pg_dump backup utility is taking more time around 24hrs to takethe backup of 28GB

From
Kevin Brannen
Date:

From: Raghavendra Rao J S V [mailto:raghavendrajsv@gmail.com]
Sent: Thursday, October 18, 2018 7:26 AM
To: pgsql-general@lists.postgresql.org
Subject: pg_dump backup utility is taking more time around 24hrs to take the backup of 28GB

 

Hi All,

 

We are using pg_dump backup utility in order to take the backup of the database. Unfortunately,it is taking around 24hrs of time  to take the backup of  28GB database. Please guide me how to reduce the time and is there any parameter need to be modified which will help us to reduce the backup time. We are using Postgres 9.2 version

 

Note:-Kindly suggest me options using pg_dump only.

 

--

Regards,
Raghavendra Rao 

===========================================

 

I agree with Ozz, be sure you’re dumping to another drive than what your DB sits on.

Investigate the “-f d” option, and the “-j” option can be useful too. I’ll also point out that hardware makes a big difference. :)

 

We can dump a 180G DB in about 15min. Note, that is to an SSD RAID array and we do “-f d -j $num_cpus_minus_2” and there are 32 cpus on the server (so -j 30).

 

HTH,

Kevin

 

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

Re: pg_dump backup utility is taking more time around 24hrs to takethe backup of 28GB

From
Jeff Janes
Date:
On Thu, Oct 18, 2018 at 8:26 AM Raghavendra Rao J S V <raghavendrajsv@gmail.com> wrote:
Hi All,

We are using pg_dump backup utility in order to take the backup of the database. Unfortunately,it is taking around 24hrs of time  to take the backup of  28GB database. Please guide me how to reduce the time and is there any parameter need to be modified which will help us to reduce the backup time. We are using Postgres 9.2 version

What minor version?  How long does it take to do just a pg_dump -s?  Can you use system tools (like top in Linux) to identify the bottleneck as CPU, disk, etc.?

Cheers,

Jeff