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?