Date: Thu, 22 Feb 2018 06:59:40 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
Cc: pgsql-admin@postgresql.org, Luis Marin <luismarinaray@gmail.com>
Subject: Re: PostgreSQL Backup Strategies
Message-ID: <20180222115940.GE2416@tamriel.snowman.net>
References: 
 <CAEp6HQVHATX7yjYtdbVcOf0_03ZAxmEVKo=E1WG+y5rkok76jA@mail.gmail.com>
 <aqm0ckut7fsf67hqeupu7k74.1519268886459@email.android.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="Si/0J0/bdwiM91zd"
Content-Disposition: inline
In-Reply-To: <aqm0ckut7fsf67hqeupu7k74.1519268886459@email.android.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--Si/0J0/bdwiM91zd
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Greetings,

* Alvaro Aguayo Garcia-Rada (aaguayo@opensysperu.com) wrote:
> Sorry I have no book to recommend, but my recommendation will always pass through pg_basebackup, which will perform a full backup of you running database without needing to stop anything, and will also(with adequate args) add all transactiins committed zuring the backup to the backup itself.

Note that you *must* ensure that the write-ahead-log / xlog is captured
for the duration of the backup, regardless of how you do the backup.
The above seems to imply that you only need to if you want the
transactions committed during the backup- that is *not* the case, you
need the WAL to have a *consistent* backup.  Without the WAL, the backup
isn't consistent and isn't valid and therefore can't be used.

> I do myself use pg_basebackup and a simple shellscript to do automated backups of a production system.

pg_basebackup with the --xlog or --wal option is a good way to get a
snapshot of a running database.  You can also use archive_command or
pg_receivexlog / pg_receivewal to collect the WAL but then you have to
verify that you have all of the WAL generated during the backup.

> I've also heard about pgbarman, but have no knowledge on it.

For doing file-based backups of PG, you should definitely be using an
existing well maintained PG backup implementation, such as pgBackRest,
barman, or perhaps WAL-E/WAL-G.  These have, literally, years of
development resources put into them to make sure that the backups taken
are consistent and valid (though you should also, always, be testing
your backups).  Unless you're planning to invest serious time
understanding all of the details involved in taking a backup of PG, I'd
strongly recommend you use an existing solution.

If you're looking for logical backups, there's also pg_dump, of course,
but you can't do point-in-time-recovery with pg_dump in the same way you
can with a file-based backup, and restore time is longer as indexes have
to be rebuilt and constraints re-checked with pg_dump.

Thanks!

Stephen

--Si/0J0/bdwiM91zd
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJajrCsAAoJEO1sijiDR2RVoCcQAJDm4Z5dIwMl85yWgMxr/CxK
6vYK1kEV1+yWZA1u/d14jb3opgzgohzrWRSr0eQrX8c2yRdtphGLBxyAx3adNUun
bjn7OyyBxgYCq2X1ATpzDZdHGKgxEh9O4B3ApbRpVBK346KTDT5eWXmzb/OOk41s
OfMnyUIHVG8Q4hWDwu9ygsAx9cCavrOSnaxvF2NNmc9DGQ8rpa+K6kM+26mWN0mh
BzlxEGXCVzvZo1d51P4wkhwtcCaw0zTi7mzNuT5cSCx4O3Fkw4NnH2WeylKFlLVs
1XjA08Hq63o+0NdZM9IH9YFSeipnIsiSaJETVlnSyflzaRHH6kXAMsApvUL2LtMH
ayi8/tTfaIT9TFLZUDWID48LkUkPImf8HrtJ2Phm3i7LTYfHcxsbH/TiH17IfCgs
Vw8LZuB63sX4IyLT5zsrAw6urYuettgRV36wCpcG+KiRL8K2ppZvejksYJzafPWS
QbTX8qeZovfTPUZS3GB6xfE0LyA6v3k6duR60INC5JblVB5flZM3Qe5RFFGHQ4a3
PFI9H7XsYBs21UEJAQUVh9xDlw5PuvhwvVfUnyI/7bbdl/dBfqTIdoVD32dbwsY9
XNm4/gg6rXrDuV2z47XwmYCMQSSPw82USjSh4XuDmeJ7QFtdQZjwp9RzRjM//phK
J8g8hqjEoDohsnVOjiST
=VaG4
-----END PGP SIGNATURE-----

--Si/0J0/bdwiM91zd--