Postgres Pro
Downloads
Home   >   mailing lists

Re: Hot backup in PostgreSQL - Mailing list pgsql-general

From Andreas Joseph Krogh
Subject Re: Hot backup in PostgreSQL
Date
Msg-id VisenaEmail.36.d59f54ce17aad18e.1754f74bd36@tc7-visena
Whole thread Raw
In response to Re: Hot backup in PostgreSQL  (hubert depesz lubaczewski <depesz@depesz.com>)
List pgsql-general
Tree view
På torsdag 22. oktober 2020 kl. 10:18:12, skrev hubert depesz lubaczewski <depesz@depesz.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@[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
 
Attachment

pgsql-general by date:

Previous
From: Tatsuo Ishii
Date:
Subject: Re: Setup Pgpool2 with Postgresql Streaming Replication
Next
From: "Hou, Zhijie"
Date:
Subject: Initplan placed at the righttree or the lefttree of joinnode