Re: Hot backup in PostgreSQL

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

På torsdag 22. oktober 2020 kl. 10:18:12, skrev hubert depesz lubaczewski <depesz@xxxxxxxxxx>:

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@[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@xxxxxxxxxx

www.visena.com