Home / server / Questions / 1081642
Accepted
Janning
Asked: 2021-10-26 08:04:38 +0800 CST

Postgresql 13 - Speed up pg_dump to 5 minutes instead of 70 minutes

  • 772

We use pg_dump nightly to make a snapshot of our database. We did for a long time with a simple command

pg_dump -Fc database_name

This takes about an hour and produces a file of 30+GByte.

How can we speed up things?

postgresql pg-dump

1 Answers

  1. Best Answer

    Our biggest table contains raw image data in form of a bytea column.

    When we did a simple COPY from psql on this table to stdout, it was quite fast (1 or 2 minutes), but it was very slow with the pg_dump. It took ~60 minutes.

    So investigating this I stumbled across this discussion. It seams that the pg_dump compression is rather slow if data is already compressed as it is with image data in a bytea format. And it is better to compress outside of pg_dump (-Z0).

    Additionally we found that we can make use of our multi-core cpu (-j10 and pigz). So now we are doing it like this:

    $ pg_dump -Z0 -j 10 -Fd database_name -f dumpdir
$ tar -cf - dumpdir | pigz > dumpdir.tar.gz
$ rm dumpdir

    The time has dropped from ~70 minutes to ~5 minutes. Quite amazing.

    You can restore it like this:

    $ pigz -dc dumpdir.tar.gz | tar -C dumpdir --strip-components 1 -xf -
$ pg_restore -j 10 -Fd -O -d database_name dumpdir
    • 7