Date: Fri, 20 Jan 2017 08:40:54 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Vladimir Borodin <root@simply.name>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
	Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>,
	"Madusudanan.B.N" <b.n.madusudanan@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Backup taking long time !!!
Message-ID: <20170120134054.GH18360@tamriel.snowman.net>
References: <0fb0c8c3f69340a3a7d70b1c39fa2b2c@cyient.com>
 <CA+_K-Dw=_reBqdkM5EyghgR21eB7uDCKxTd7_Yh9jQ610-5gTg@mail.gmail.com>
 <23dc5b1ea9604ec3afdaabdd15491085@cyient.com>
 <CAFj8pRAHx-Wt4fyJmF=YH-F2_S=mO2E+=JDx+qnunkDP5_FPGA@mail.gmail.com>
 <b0748fe40949446283068a4d2d06c158@cyient.com>
 <CAFj8pRBFDh=hsghE8v5F71KztyczR7iWBXGJBOLLhUQDwUh8MQ@mail.gmail.com>
 <20170120122224.GF18360@tamriel.snowman.net>
 <DE553F58-646E-49E8-94D1-31794C3E9040@simply.name>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="W8H4ZJu+ON5Wa/ox"
Content-Disposition: inline
In-Reply-To: <DE553F58-646E-49E8-94D1-31794C3E9040@simply.name>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--W8H4ZJu+ON5Wa/ox
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Vladimir,

* Vladimir Borodin (root@simply.name) wrote:
> > 20 =D1=8F=D0=BD=D0=B2. 2017 =D0=B3., =D0=B2 15:22, Stephen Frost <sfros=
t@snowman.net> =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> >> This process can be automatized by some applications like barman
> >> http://www.pgbarman.org/
> >=20
> > Last I checked, barman is still single-threaded.
> >=20
> > If the database is large enough that you need multi-process backup, I'd
> > suggest looking at pgbackrest- http://www.pgbackrest.org.
> >=20
> > pgbackrest has parallel backup, incremental/differential/full backup
> > support, supports compression, CRC checking, and a whole ton of other
> > good stuff.
>=20
> Increments in pgbackrest are done on file level which is not really effic=
ient. We have done parallelism, compression and page-level increments (9.3+=
) in barman fork [1], but unfortunately guys from 2ndquadrant-it don=E2=80=
=99t hurry to work on it.

We're looking at page-level incremental backup in pgbackrest also.  For
larger systems, we've not heard too much complaining about it being
file-based though, which is why it hasn't been a priority.  Of course,
the OP is on 9.1 too, so.

As for your fork, well, I can't say I really blame the barman folks for
being cautious- that's usually a good thing in your backup software. :)

I'm curious how you're handling compressed page-level incremental
backups though.  I looked through barman-incr and it wasn't obvious to
me what was going wrt how the incrementals are stored, are they ending
up as sparse files, or are you actually copying/overwriting the prior
file in the backup repository?  Apologies, python isn't my first
language, but the lack of any comment anywhere in that file doesn't
really help.

> And actually it would be much better to do a good backup and recovery man=
ager part of the core postgres.

Sure, but that's not going to happen for 9.1, or even 9.6, and I doubt
PG10 is going to suddenly get parallel base-backup with compression.

I've been discussing ways to improve the situation with Magnus and we do
have some ideas about it, but that's really an independent effort as
we're still going to need a tool for released versions of PG.

Thanks!

Stephen

--W8H4ZJu+ON5Wa/ox
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJYghNmAAoJEO1sijiDR2RVG5YQALObrlDleiPGwkWykGi5CyuD
GwYqgTTB7/HNhQ7Z/uDWk15Hs3MsxrKpnUP5KnXWyUQp1xzalBG9JqjeZ+LmGVD6
0SFr+lJjfiHliz5v55p+WbUVMb59+i+hOt0TT3LNy/BrGjXFSq1Js3X4UUqGAWfP
VIrJ855Pi3TpnZdw2tIIF+hTS1CwxQ55/rcNEelY4LD4WXyLVTqIkHLj7hJYgXjp
IabzruxnxK9+OCg7oMo1BOe/DRU9WnYZK1FkK1/B79l1n0ee02d7r+aOU0p6qdO8
2Qe15uAd2Ns3ohSOLg9agtSdLLMXt4Ci28/4MlmjFP7H9ytIJtPmP/udoF2Cja/S
HgYSLyICILdhUo4VONK7fghB+uK1VenRtk2PskPi8dPkXkQq6lY/pQOsd1OE48lu
qbdgnjlbJElGNPJZvW6crjrDrZQJbCQTnYH0JXe8o8SJxMgX0zpeSSJGTfxFUrCO
I+NLyvgdyeJP65C/dAug/48DdLYXZwvR8I7GssSzgpJ3q6MnGfRC+ckULqK89hSe
vsr7aB7TdEKr2qk57OPBxc3A7Attp91p4lwiCt9UBKSob0g2YaQoWMpOLDt9kZ3Y
Vw8gHx0pXpCKFuk1lkh8oZbukrnV89EZtNDYIFZcaalngNwI4ymT5dhhvEeWKb4c
IfrRJJDUDr0m3yrdyzTb
=H3ez
-----END PGP SIGNATURE-----

--W8H4ZJu+ON5Wa/ox--