Please let me know which configuration setting we need to modify to speedup the pg_dump backup.

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Please let me know which configuration setting we need to modify to speedup the pg_dump backup.

Raghavendra Rao J S V
Hi All,

pg_dump is taking more time. Please let me know which configuration setting we need to modify to speedup the pg_dump backup.We are using 9.2 version on Centos Box.

--
Regards,
Raghavendra Rao J S V

Reply | Threaded
Open this post in threaded view
|

Re: Please let me know which configuration setting we need to modify to speedup the pg_dump backup.

pavan95
Hi,

You can use -j jobs option to speed up the process.

Hope it works.

Regards,
Pavan

On Thu, Oct 11, 2018, 8:02 AM Raghavendra Rao J S V <[hidden email]> wrote:
Hi All,

pg_dump is taking more time. Please let me know which configuration setting we need to modify to speedup the pg_dump backup.We are using 9.2 version on Centos Box.

--
Regards,
Raghavendra Rao J S V

Reply | Threaded
Open this post in threaded view
|

Re: Please let me know which configuration setting we need to modify to speedup the pg_dump backup.

Ron-2
In reply to this post by Raghavendra Rao J S V
On 10/10/2018 09:32 PM, Raghavendra Rao J S V wrote:
Hi All,

pg_dump is taking more time. Please let me know which configuration setting we need to modify to speedup the pg_dump backup.We are using 9.2 version on Centos Box.

Is it taking "more time" because your database is bigger?


--
Angular momentum makes the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: Please let me know which configuration setting we need to modify to speedup the pg_dump backup.

Christopher Browne-3
In reply to this post by Raghavendra Rao J S V
On Wed, Oct 10, 2018, 10:32 PM Raghavendra Rao J S V <[hidden email]> wrote:
Hi All,

pg_dump is taking more time. Please let me know which configuration setting we need to modify to speedup the pg_dump backup.We are using 9.2 version on Centos Box.

There certainly isn't a configuration parameter to say "make PG dump faster."

- It is possible that it is taking longer to backup the database because the database has grown in size.  If you have a lot more data captured, this would be a natural consequence, that you need simply to accept.

But there are additional possibilities...

- Perhaps the database is bloated because an update pattern is leading to a lot of dead data being left behind.  In that case some tables need to be vacuumed much more often and you should look into the auto vacuum configuration.

- Perhaps the database has some table that contains a lot of obsolete data.  This would depend heavily on the nature of your application.

You should look to see what data you are collecting that is not of ongoing value.  That may represent data that you should trim out of the database.  That should improve the amount of time it takes to do a backup of the database.
Reply | Threaded
Open this post in threaded view
|

Re: Please let me know which configuration setting we need to modify to speedup the pg_dump backup.

Raghavendra Rao J S V
In reply to this post by pavan95
We are using postgresql 9.2. It doesn't contain the option. 

Please guide me any other approaches to improve the performance of pg_dump.

On Thu 11 Oct, 2018, 8:05 AM Pavan Teja, <[hidden email]> wrote:
Hi,

You can use -j jobs option to speed up the process.

Hope it works.

Regards,
Pavan

On Thu, Oct 11, 2018, 8:02 AM Raghavendra Rao J S V <[hidden email]> wrote:
Hi All,

pg_dump is taking more time. Please let me know which configuration setting we need to modify to speedup the pg_dump backup.We are using 9.2 version on Centos Box.

--
Regards,
Raghavendra Rao J S V

Reply | Threaded
Open this post in threaded view
|

Re: Please let me know which configuration setting we need to modify to speedup the pg_dump backup.

Raghavendra Rao J S V
In reply to this post by Christopher Browne-3
Thank you very much for your prompt response Christopher.

On Thu 11 Oct, 2018, 8:41 AM Christopher Browne, <[hidden email]> wrote:
On Wed, Oct 10, 2018, 10:32 PM Raghavendra Rao J S V <[hidden email]> wrote:
Hi All,

pg_dump is taking more time. Please let me know which configuration setting we need to modify to speedup the pg_dump backup.We are using 9.2 version on Centos Box.

There certainly isn't a configuration parameter to say "make PG dump faster."

- It is possible that it is taking longer to backup the database because the database has grown in size.  If you have a lot more data captured, this would be a natural consequence, that you need simply to accept.

But there are additional possibilities...

- Perhaps the database is bloated because an update pattern is leading to a lot of dead data being left behind.  In that case some tables need to be vacuumed much more often and you should look into the auto vacuum configuration.

- Perhaps the database has some table that contains a lot of obsolete data.  This would depend heavily on the nature of your application.

You should look to see what data you are collecting that is not of ongoing value.  That may represent data that you should trim out of the database.  That should improve the amount of time it takes to do a backup of the database.
Reply | Threaded
Open this post in threaded view
|

Re: Please let me know which configuration setting we need to modify to speedup the pg_dump backup.

Laurenz Albe
In reply to this post by Raghavendra Rao J S V
Please bottom-post, otherwise the thread becomes hard to read.

On Thu, 2018-10-11 at 08:44 +0530, Raghavendra Rao J S V wrote:

> We are using postgresql 9.2. It doesn't contain the option.
>
> Please guide me any other approaches to improve the performance of pg_dump.
>
> On Thu 11 Oct, 2018, 8:05 AM Pavan Teja, <[hidden email]> wrote:
> > You can use -j jobs option to speed up the process.
> >
> > Hope it works.
> >
> > On Thu, Oct 11, 2018, 8:02 AM Raghavendra Rao J S V <[hidden email]> wrote:
> > > pg_dump is taking more time. Please let me know which configuration setting we
> > > need to modify to speedup the pg_dump backup.We are using 9.2 version on Centos Box.

There you have your first configuration change to speed up pg_dump:
upgrade from 9.2 to a release that is still supported.
And please don't tell me that you cannot / may not.
If your data are important for you, and you really want that speed-up,
you can.

Anyway, to make pg_dump faster with the -j option, you need to use
the "directory format" (-F d).

Other options to speed up pg_dump are limited: you can get faster
storage so that sequential scans are faster or more memory so that
more of the data resides in the filesystem cache.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com