Date: Wed, 26 Oct 2016 09:30:56 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Ayush Goyal <ayush@helpshift.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Using pg hotbackup directly without wal archives
Message-ID: <20161026133056.GB13284@tamriel.snowman.net>
References: 
 <CA+Pdoy_rU8eqbXBZVKaqun8Q-K0e38QGpy_TiEV6uSW5hYa92Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="/dLWkdqvW7eyw3rp"
Content-Disposition: inline
In-Reply-To: 
 <CA+Pdoy_rU8eqbXBZVKaqun8Q-K0e38QGpy_TiEV6uSW5hYa92Q@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


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

Greetings,

* Ayush Goyal (ayush@helpshift.com) wrote:
> I need to ask if there is a way to use the pg hot backup taken via
> pg_start_backup() and pg_stop_backup() calls directly without using the w=
al
> archives.

No, WAL is required for correct backups.  There are multiple ways to
ensure that the WAL is archived.

> If I start postgres with just base backup without setting up recovery.conf
> for fetching archived wal logs, I get this im the log files:
>=20
> 2016-10-26 06:48:25 UTC LOG:  database system was shut down in recovery at
> 2016-10-25 15:50:16 UTC
> 2016-10-26 06:48:25 UTC LOG:  invalid primary checkpoint record
> 2016-10-26 06:48:25 UTC LOG:  invalid secondary checkpoint record
> 2016-10-26 06:48:25 UTC PANIC:  could not locate a valid checkpoint record
> 2016-10-26 06:48:25 UTC LOG:  startup process (PID 980) was terminated by
> signal 6: Aborted
> 2016-10-26 06:48:25 UTC LOG:  aborting startup due to startup process
> failure
>=20
> Also, I noticed that if I run pg_resetxlog, I am able to start postgres,
> but I am not  sure if the database is consistent after running
> pg_resetxlog. Is database state consistent after running pg_resetxlog on
> this hot backup?

No, the database state will *not* be consistent after pg_resetxlog.  You
must archive the WAL which is written during the backup and must provide
a recovery.conf when performing a restore.

I strongly recommend that you look at using the existing PG backup tools
which will correctly perform the backup and WAL archiving, such as
pg_basebackup, pgBackRest, or barman.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJYELAPAAoJEO1sijiDR2RV7y8P/jo7KSiAnDtEGJlmQrU3dL6f
nBXsxc6EXO/MHHmOkN8MDL4FDGa93mZiVc2wc7NZGq/v3VGwsS4H8MCZiqrkCjtc
kKSYHK6mHZ2ZrIGEx2zH6yfermCjwpHhYsK3lg7E9n0wI8j0x/ZbBCtNxNsD6mb8
+FOlb07/jdmoiImEIrerSFp5NG2YLlSzcR2OsTCVLN9ZiY0qwbw6k6nk5VZEKQXG
j4PSOx13rtOp546xEJKBWubOyTN9wQwl062Xk5TPZVrGWTkmeFMgbJ2+B1dURb1Y
jyttNhqMwZXYJNGqUISnUeXD12oaLOh5Vj0MufIzu6kDX1tNMe7nyBiAcNuO5rwK
3n9kQNVt7oySuo10yS/MriJA/1Jihk+PyVaxoVr+AvYKhTMpbGkOVY6BU1i7g4F8
kvtRe/W2AjaJfbrS6vEiO/MD+2+WAf9McG58Zgq/m6UYwUJqgrYvsAtH8o0A0Zp1
oCR6/4m7z+dBgMPP6pfrstCPbatdDhF1VEvOYQhmDfhj+QSGIz4RT1S1X2FDc2nc
SXAwnn4+6SvzbBDnVSrEXqmRBiPTM03gA8xPEWlYUlSiBuXx6f5mAE9+myCzoH8x
IOPf5pMcnM6VfQ6p/cX6ZnrN3nKPpQx4F61yLiS7w8BSk26JcRrdIcSWUIxBUJBD
IdhijIDC4azuqsdHP8Qf
=t8hV
-----END PGP SIGNATURE-----

--/dLWkdqvW7eyw3rp--