Date: Sun, 22 Jan 2017 11:37:48 -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: <20170122163748.GG18360@tamriel.snowman.net>
References: <0fb0c8c3f69340a3a7d70b1c39fa2b2c@cyient.com>
 <CAGu3fER+B6dFk=7F+DEMPPOSerYZs0HZMOfVAkbKs5Gdi-B-mg@mail.gmail.com>
 <20170122145511.GD18360@tamriel.snowman.net>
 <CAGu3fESxPSSqbKrV-UHdVnWnLK0vYk2fTmxjNOoEtevW39j1uw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="63v8OnoPMaPySVTH"
Content-Disposition: inline
In-Reply-To: 
 <CAGu3fESxPSSqbKrV-UHdVnWnLK0vYk2fTmxjNOoEtevW39j1uw@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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

Greetings,

* julyanto SUTANDANG (julyanto@equnix.co.id) wrote:
> Please elaborate more of what you are saying. What i am saying is based on
> the Official Docs, Forum and our own test. This is what we had to do to
> save time, both backing up and  restoring.
>=20
> https://www.postgresql.org/docs/9.6/static/functions-admin.html
>=20
> When PostgreSQL in the mode of Start Backup, PostgreSQL only writes to the
> XLOG, then you can safely rsync / copy the base data (snapshot) then later
> you can have full copy of snapshot backup data.

You are confusing two things.

After calling pg_start_backup, you can safely copy the contents of the
data directory, that is correct.

However, PostgreSQL *will* continue to write to the data directory.
That, however, is ok, because those changes will *also* be written into
the WAL and, after calling pg_start_backup(), you collect all of the
WAL using archive_command or pg_receivexlog.  With all of the WAL
which was created during the backup, PG will be able to recover from the
changes made during the backup to the data directory, but you *must*
have all of that WAL, or the backup will be inconsistent because of
those changes that were made to the data directory after
pg_start_backup() was called.

In other words, if you aren't using pg_receivexlog or archive_command,
your backups are invalid.

> if you wanted to backup in later day, you can use rsync then it will copy
> faster because rsync only copy the difference, rather than copy all the
> data.

This is *also* incorrect.  rsync, by itself, is *not* safe to use for
doing that kind of incremental backup, unless you enable checksums.  The
reason for this is that rsync has only a 1-second level granularity and
it is possible (unlikely, though it has been demonstrated) to miss
changes made to a file within that 1-second window.

> my latter explanation is: use pg_basebackup, it will do it automatically
> for you.

Yes, if you are unsure about how to perform a safe backup properly,
using pg_basebackup or one of the existing backup tools is, by far, the
best approach.  Attempting to roll your own backup system based on rsync
is not something I am comfortable recommending any more because it is
*not* simple to do correctly.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJYhN/cAAoJEO1sijiDR2RVoNIQAMKQfdykL0DZ0mSLLCT8SL3o
vHotrrq/v9sHnsFltPk5kCr1fjPXQFPUX5M1Jqppm9cFsKnoR336Zsq1swhA7koh
9swkdpmq+FJC0jh2E6WXFKsee3ZdnuU3gfhKrOKqpIEUdkJLe5BHs7azKmel+KTB
9raUxx7YYcCsd6tYC27VKVqnEnbcZaDeuJ1fKftIKGnBUSMTKzLo1TwXCwYBVO8T
dUK5gdIbXb0BrhFcuBBP2n5WU2IFeM08JI0KXREfFVIhRg23OmU5d36kcw3Z1RWn
2+kYK81+5ncKEXSNwIlK+mCqL8GXkg1V2s+sYI4vOaehEczQbxGvEPA3uBW3MUuk
PHwLls8YFZ60lKDRcuaKgITvFPWaTgBaNFN+iihjH87oT0/8ahcHv1EY3DJiml8n
y4RlrbmfTE/OlrZPxXhVQlLNYasikIZ1UVl9amDogrjimzvbYDnZQwOJyjslcQPk
0QHB2Y1TnZwFe4sMgIq0H1mGtD2iPmx5KWG+mvq9EqOeLl9xDcGgdtuyCxtCN9Ah
2OkZ7XxUGeOeqT2t/6oZfPr7y9oEa8Q3eMnnf+DG30Dh5BKoLW8EJ6inFhDBnrdA
DbTiiDJBUm01W/ReW8+gy77XQExAJzP1nMsAFPNnmIxmasPHEXqjnfludYNjv8EO
4zWmnBdU44enLgmAwn0Y
=j/za
-----END PGP SIGNATURE-----

--63v8OnoPMaPySVTH--