Thread: Please let me know which configuration setting we need to modify tospeedup the pg_dump backup.
Please let me know which configuration setting we need to modify tospeedup the pg_dump backup.
From
Raghavendra Rao J S V
Date:
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
Raghavendra Rao J S V
Re: Please let me know which configuration setting we need to modifyto speedup the pg_dump backup.
From
Pavan Teja
Date:
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 <raghavendrajsv@gmail.com> 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
Re: Please let me know which configuration setting we need to modifyto speedup the pg_dump backup.
From
Ron
Date:
On 10/10/2018 09:32 PM, Raghavendra Rao J S V wrote:
Is it taking "more time" because your database is bigger?
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.
Angular momentum makes the world go 'round.
Re: Please let me know which configuration setting we need to modifyto speedup the pg_dump backup.
From
Christopher Browne
Date:
On Wed, Oct 10, 2018, 10:32 PM Raghavendra Rao J S V <raghavendrajsv@gmail.com> 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.
Re: Please let me know which configuration setting we need to modifyto speedup the pg_dump backup.
From
Raghavendra Rao J S V
Date:
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, <pavan.postgresdba@gmail.com> wrote:
Hi,You can use -j jobs option to speed up the process.Hope it works.Regards,PavanOn Thu, Oct 11, 2018, 8:02 AM Raghavendra Rao J S V <raghavendrajsv@gmail.com> 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
Re: Please let me know which configuration setting we need to modifyto speedup the pg_dump backup.
From
Raghavendra Rao J S V
Date:
Thank you very much for your prompt response Christopher.
On Thu 11 Oct, 2018, 8:41 AM Christopher Browne, <cbbrowne@gmail.com> wrote:
On Wed, Oct 10, 2018, 10:32 PM Raghavendra Rao J S V <raghavendrajsv@gmail.com> 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.
Re: Please let me know which configuration setting we need tomodify to speedup the pg_dump backup.
From
Laurenz Albe
Date:
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, <pavan.postgresdba@gmail.com> 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 <raghavendrajsv@gmail.com> 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