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

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:

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.

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.

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:

Thank you very much for your prompt response Christopher.

On Thu 11 Oct, 2018, 8:41 AM Christopher Browne, <cbbrowne@gmail.com> wrote:

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