Обсуждение: Regarding db dump with Fc taking very long time to completion
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
I need to reduce to dump time of that table that has 88GB in size
Regards
Durgamahesh Manne
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 onthat 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:
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.
LucaHiparallel jobs with pg_dump can* be effective for the database which contains lot of tables&its dependent objectsparallel jobs with pg_dump can not be effective against a single tableRegardsDurgamahesh Manne
Hi,
"Speeding up dump/restore process"
Maybe - you can re-use this backup tricks.
"Speeding up dump/restore 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
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):
HiTo respected international postgresql teamI am using postgresql 11.4 versionI have scheduled logical dump job which runs daily one time at db levelThere was one table that has write intensive activity for every 40 seconds in dbThe size of the table is about 88GBLogical dump of that table is taking more than 7 hours to be completedI need to reduce to dump time of that table that has 88GB in sizeRegardsDurgamahesh Manne
On Fri, Aug 30, 2019 at 5:51 AM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
HiTo respected international postgresql teamI am using postgresql 11.4 versionI have scheduled logical dump job which runs daily one time at db levelThere was one table that has write intensive activity for every 40 seconds in dbThe size of the table is about 88GBLogical 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