Date: Sun, 22 Jan 2017 09:55:11 -0500
From: Stephen Frost <sfrost@snowman.net>
To: julyanto SUTANDANG <julyanto@equnix.co.id>
Cc: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Backup taking long time !!!
Message-ID: <20170122145511.GD18360@tamriel.snowman.net>
References: <0fb0c8c3f69340a3a7d70b1c39fa2b2c@cyient.com>
 <CAGu3fER+B6dFk=7F+DEMPPOSerYZs0HZMOfVAkbKs5Gdi-B-mg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="/X7uyC30lK/fklAK"
Content-Disposition: inline
In-Reply-To: 
 <CAGu3fER+B6dFk=7F+DEMPPOSerYZs0HZMOfVAkbKs5Gdi-B-mg@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--/X7uyC30lK/fklAK
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* julyanto SUTANDANG (julyanto@equnix.co.id) wrote:
> Best practice in doing full backup is using RSYNC, but before you can copy
> the DATADIR, you might you pg_start_backup to tell the server not to write
> into the DATADIR, because you are copying that data. After finished copy
> all the data in DATADIR, you can ask server to continue flushing the data
> from logs, by commanding pg_stop_backup. Remember, not to copy the XLOG
> dir.

Whoah.  That is not, at all, correct, if I'm understanding what you're
suggesting.

PG most certainly *does* continue to write into the data directory even
after pg_start_backup() has been run.  You *must* use archive_command or
pg_receivexlog to capture all of the WAL during the backup to have a
consistent backup.

> There are another way more simpler, which is applying command
> pg_basebackup, which actually did that way in simpler version.

pg_basebackup has options to stream the WAL during the backup to capture
it, which is how it handles that.

> if you did pg_dump, you wont get the exact copy of your data, and you will
> take longer downtime to recover the backup data. By that way, recovering =
is
> only starting up the postgres with that copy.

pg_dump will generally take longer to do a restore, yes.  Recovering
=66rom a backup does require that a recovery.conf exists with a
restore_command that PG can use to get the WAL files it needs, or that
all of the WAL from the backup is in pg_xlog/pg_wal.

Please do not claim that PG stops writing to the DATADIR or BASEDIR
after a pg_start_backup(), that is not correct and could lead to invalid
backups.

Thanks!

Stephen

--/X7uyC30lK/fklAK
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

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

iQIcBAEBCgAGBQJYhMfPAAoJEO1sijiDR2RVhhMQAKxyl7lHdw21j4lHen/kT0c+
e+reSVu2Y1oMlngAoKWIfbSqRDMQJyAsQQaEpaLGMw6DUdCJBkst8OyzUexwDNuw
YtmmpbgpvidhYJleLaalnMi81uQOl7LnYLQZosggqF4suOAPcxsrt1KPw6LLWd03
GdsaE/Ka5TtZmj4xMdLODULE4voBtuhOuqgDns4Yog567HONvQHzd7caKzQ7vPfQ
w5ayGRlv2+nHnzRddgR5NJ1V0hdUJrchMBIbu1SgqMUUA7g54ncH4b9PAbDNQqjI
9K734K6a5fIQr5i51K1snBpnVNmJrrcGygIiaZ5OrmkF418sYo1H8skXT35kLV39
QPmm0GpRLxOJjbkAqnwSk7Thh9i5TFztmVQQ5q1W619Bvfml2na6C5jrHPApiJCD
0M1XXgeXwusVWsQkCdfsmNHE1viBKWRU+lt6eaH0wxO9+eZO0o2iAJVl6k7+ImBl
oYk3wQul16FMzz/MKOKZeP80YWUFaAdWWIcdxCAs8FsWeboyrWd0ZsZ/V/dSMGEw
qo8uCUv83ZvOStswAK0kYE892zA8id5oudkEnGilxQV+YMwxY38C+jbK8Jm7qD9F
XCxIt5gySJv52jgfXqU6GVDugV6n9NGFaEAsrcUGdAIk2+dE1osVRGv5LjiU/zUy
8QfLbMngCH2Jd7b4Tpdt
=/DXq
-----END PGP SIGNATURE-----

--/X7uyC30lK/fklAK--