Re: Regarding db dump with Fc taking very long time to completion

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Fri, Aug 30, 2019 at 5:51 AM Durgamahesh Manne <maheshpostgres9@xxxxxxxxx> wrote:

Hi

To respected international postgresql team

I am using postgresql 11.4 version 

I have scheduled logical dump job which runs daily one time at db level

There was one table that has write intensive activity for every 40 seconds in db

The size of the table is about 88GB

 Logical dump of that table is taking more than 7 hours to be completed 

That seems very slow.  I get about 2 GB per minute on my low-end laptop.  Does your schema use obscure data types which might be hard to process?

Using your system tools (like "top" for linux), what is going on?  Is time spent in pg_dump itself, or in postgres doing the COPY?  Is it CPU bound or IO bound?  Can you use "perf top" to see where it is spending its time?

How long does it take if you turn off compression, and stream the output into the void rather than saving it?

 

time pg_dump -p xxxx -Fc -Z0 | wc -c

 

Alternatively, can you use physical rather than logical backups?

Cheers,

Jeff