Regarding db dump with Fc taking very long time to completion

On Fri, Aug 30, 2019 at 11:51 AM Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:

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

I need to reduce to dump time of that table that has 88GB in size

Good luck!
I would see two possible solutions to the problem:
1) use physical backup and switch to incremental (e..g, pgbackrest)
2) partition the table and backup single pieces, if possible
(constraints?) and be assured it will become hard to maintain (added
partitions, and so on).

Are all of the 88 GB be written during a bulk process? I guess no, so
maybe partitioning you can avoid locking the whole dataset and reduce
contention (and thus time).

Luca

On Fri, Aug 30, 2019 at 4:12 PM Luca Ferrari <fluca1978@gmail.com> wrote:

On Fri, Aug 30, 2019 at 11:51 AM Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:

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

I need to reduce to dump time of that table that has 88GB in size

Good luck!
I would see two possible solutions to the problem:
1) use physical backup and switch to incremental (e..g, pgbackrest)
2) partition the table and backup single pieces, if possible
(constraints?) and be assured it will become hard to maintain (added
partitions, and so on).

Are all of the 88 GB be written during a bulk process? I guess no, so
maybe partitioning you can avoid locking the whole dataset and reduce
contention (and thus time).

Luca

Hi respected postgres team

Are all of the 88 GB be written during a bulk process?
NO
Earlier table size was 88gb
Now table size is about 148 GB
Is there any way to reduce dump time when i take dump of the table which
has 148gb in size without creating partiton on that table has 148gb in size
?

Regards
Durgamahesh Manne

On Wed, Oct 16, 2019 at 11:27 AM Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:

Is there any way to reduce dump time when i take dump of the table which has 148gb in size without creating partiton on that table has 148gb in size ?

I would at least try a parallel dump: should it be -j option to pg_dump.
I'm not sure it is an effective parallelism against a single table.

Luca

On Wed, Oct 16, 2019 at 3:09 PM Luca Ferrari <fluca1978@gmail.com> wrote:

On Wed, Oct 16, 2019 at 11:27 AM Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:

Is there any way to reduce dump time when i take dump of the table

which has 148gb in size without creating partition* on that table has 148gb
in size ?

I would at least try a parallel dump: should it be -j option to pg_dump.
I'm not sure it is an effective parallelism against a single table.

Luca

Hi

parallel jobs with pg_dump be effective for the database which contains lot
of tables&its dependent objects

parallel jobs with pg_dump can not be effective against a single table

Regards
Durgamahesh Manne

On Wed, Oct 16, 2019 at 3:22 PM Durgamahesh Manne <maheshpostgres9@gmail.com>
wrote:

Hi,

Maybe - you can re-use this backup tricks.

"Speeding up dump/restore process"
https://www.depesz.com/2009/09/19/speeding-up-dumprestore-process/

for example:
"""
*Idea was: All these tables had primary key based on serial. We could
easily get min and max value of the primary key column, and then split it
into half-a-million-ids “partitions", then dump them separately using:*
*psql -qAt -c "COPY ( SELECT * FROM TABLE WHERE id BETWEEN x AND y) TO
STDOUT" | gzip -c - > TABLE.x.y.dump*
"""

best,
Imre

Durgamahesh Manne <maheshpostgres9@gmail.com> ezt írta (időpont: 2019. aug.
30., P, 11:51):

On Fri, Aug 30, 2019 at 5:51 AM Durgamahesh Manne <maheshpostgres9@gmail.com>
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