Re: Hot backup in PostgreSQL

På torsdag 22. oktober 2020 kl. 10:18:12, skrev hubert depesz lubaczewski <
depesz(at)depesz(dot)com <mailto:depesz(at)depesz(dot)com>>:
On Thu, Oct 22, 2020 at 09:45:36AM +0200, W.P. wrote:
> > There are many ways to do it. To be able to suggest proper solution we'd
> > need to know:
> > 1. what is the problem with pg_dump?
> Time (I guess a bit, but copying files could be done using rsync, so much
> faster).

Is it *really* too slow for you? Please note that you can easily make it
much faster by doing -Fd -j $( nproc ).

I got curious and tried with this DB:

andreak(at)[local]:5433 13.0 visena=# select
pg_size_pretty(pg_database_size(current_database()));
┌────────────────┐
│ pg_size_pretty │
├────────────────┤
│ 47 GB │
└────────────────┘
(1 row)

nproc=16

Regular pg_dump:

$ time pg_dump -O -d visena > ~/data/visena/visena.dmp

real 2m43,904s
user 0m10,135s
sys 0m24,260s

Parallell pg_dump:

$ time pg_dump -OFd -j $(nproc) -f ~/data/visena/pg_backup -d visena

real 3m43,726s
user 12m36,620s
sys 0m9,537s

pg_dump with pbzip2

$ time pg_dump -O -d visena | pbzip2 -c > ~/data/visena/visena.dmp.bz2

real 6m58,741s
user 92m4,833s
sys 2m18,565s

Here are the sizes of all:

7,4G pg_backup (directory with -Fd)
32G visena.dmp
5,8G visena.dmp.bz2

--

Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>