Date: Fri, 10 Aug 2018 06:38:38 -0400
From: Stephen Frost <sfrost@snowman.net>
To: amit tripathi <amittripathi1445@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Point in Time recovery on PostgreSQL (10.3.1)
Message-ID: <20180810103838.GH3326@tamriel.snowman.net>
References: 
 <CABpqzULLXPFVaJfvnPd7gKyRs7ss4QjQFkRA_zWyPcKvKFuMmw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="STPqjqpCrtky8aYs"
Content-Disposition: inline
In-Reply-To: 
 <CABpqzULLXPFVaJfvnPd7gKyRs7ss4QjQFkRA_zWyPcKvKFuMmw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


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

Greetings,

* amit tripathi (amittripathi1445@gmail.com) wrote:
> We need some help with the point in recovery test . We have followed the
> below steps but after creating recovery.conf file and restarting the
> machine our DB is getting corrupted and not getting started.
> Please look at the below steps and help us analyze what is going wrong
> there. We have used offline postgresql installer to set up database.
> Required configuration like *wal_level=3Dhot_stanby, archive_mode=3Don, a=
nd
> archive_command=3D'cp %p /mnt/server/archive/%f*' in *postgresql.conf* are
> also set properly.

I'd strongly recommend that you consider using a real PostgreSQL backup
tool for your backups and not try to write your own.  As a simple point,
using 'cp' is not sufficient for a proper archive command.

> 1. After DB set up, We have created a tablcespace, db and mapped db with
> the tablespace. Also created some tables to generate transaction files in
> pg_wal and archive directory (which is *mnt/server/archive*)
>=20
> 2. SELECT pg_start_backup('TestPITR');
>=20
> 3. Taken backup(tar) of Postgresql data directory (opt/PostgreSQL/10/
> data)>>tar
> zcf backup20180810.tar data/
>=20
> 4. SELECT pg_stop_backup();

Did you read the documentation where we explicitly point out that the
non-exclusive backup method is recommended over the exclusive method?
There's good reasons for that:

https://www.postgresql.org/docs/current/static/continuous-archiving.html

> 5. Created some more tables and noted down their timestamp (for PITR, whi=
ch
> will be added in recovery.conf file as *recovery_target_time* )
>=20
> 6. Stopped the DB>>service postgresql-10 stop
>=20
> 7. Created a new directory >>mkdir pgbackup
>=20
> 8. Moved tar file into the pgbackup directory >>mv backup20180810.tar
> /opt/PostgreSQL/10/pgbackup
>=20
> 9. Copied archive files into the pgbackup directory>>  cp -r /
> *mnt/server/archive*/ /opt/PostgreSQL/10/pgbackup/
>=20
> 10. Renamed the old data directory( opt/PostgreSQL/10/ data )to bad.data
> >>> mv data bad.data (now opt/PostgreSQL/10/ bad.data)
>=20
> 11. untar the  back taken in step 2>>tar -xvf backup20180810.tar
>=20
> 12. Moved the data directory to old path >>>mv
> /opt/PostgreSQL/10/pgbackup/data/ /opt/PostgreSQL/10/ (Now i have
> opt/PostgreSQL/10/ data-derived from tar file and with old transaction lo=
gs
> in pg_wal)

Didn't you mention a tablespace..?  Did you handle backing up the
tablespace as part of this process and then restoring it?  It doesn't
look like it from what you've said above, unless perhaps I missed it
(this is pretty difficult to follow...).

> 13  Copied the updated pg_wal logs from bad.data to data >>>cp -r
> /opt/PostgreSQL/10/bad.data/pg_wal/0* /opt/PostgreSQL/10/data/pg_wal

Why would you perform this step..?  The WAL should have been archived
through archive_command and should be restored using restore_command
specified through recovery.conf.

> 14. Started the database  >> service postgresql-10 start (Our db works fi=
ne)

What do you mean you started the database and it "works fine"?

> 15. Created a recovery.conf file inside data folder >>opt/PostgreSQL/10/ =
data
> --given the permission and user as postgres

Wait, you started the database before creating a recovery.conf file?
That's definitely not going to do what you want- PG will end up trying
to do crash recovery instead of restoring from a backup.

Is there a backup_label file?

> Our recovery.conf file has two following parameters. *recovery_target_time
> was taken from step-5*
>=20
> *restore_command =3D 'cp /opt/PostgreSQL/10/pgbackup/archive/%f
> %p'recovery_target_time =3D '2018-08-10 02:56:31'*
>=20
> 16. Restarted the db server>> service postgresql-10 restart
>=20
> It doesn't restart instead we are getting below error(yesterday's log) on
> linux host. Also there is no log entry in log
> folder(opt/PostgreSQL/10/data/log) looks like it has stopped creating logs
> after restart.
>=20
>=20
> * journalctl -xeAug 09 06:47:53 scspr0512196001 systemd[1]: Starting
> PostgreSQL 10 database s-- Subject: Unit postgresql-10.service has begun
> start-up-- Defined-By: systemd--
> Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
> <https://www.google.com/url?q=3Dhttp://lists.freedesktop.org/mailman/list=
info/systemd-devel&sa=3DD&source=3Dhangouts&ust=3D1533964684221000&usg=3DAF=
QjCNFJDTSITdO_SpA_w3Y8ZNn7zwrDqQ>----
> Unit postgresql-10.service has begun starting up.Aug 09 06:47:53
> scspr0512196001 pg_ctl[17697]: waiting for server to start...Aug 09
> 06:47:53 scspr0512196001 pg_ctl[17697]: pg_ctl: could not start serverAug
> 09 06:47:53 scspr0512196001 pg_ctl[17697]: Examine the log output.Aug 09
> 06:47:53 scspr0512196001 systemd[1]: postgresql-10.service: control prAug
> 09 06:47:53 scspr0512196001 systemd[1]: Failed to start PostgreSQL 10 dat=
--
> Subject: Unit postgresql-10.service has failed-- Defined-By: systemd--
> Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
> <https://www.google.com/url?q=3Dhttp://lists.freedesktop.org/mailman/list=
info/systemd-devel&sa=3DD&source=3Dhangouts&ust=3D1533964684221000&usg=3DAF=
QjCNFJDTSITdO_SpA_w3Y8ZNn7zwrDqQ>----
> Unit postgresql-10.service has failed.---- The result is failed.Aug 09
> 06:47:53 scspr0512196001 systemd[1]: Unit postgresql-10.service entereAug
> 09 06:47:53 scspr0512196001 systemd[1]: postgresql-10.service failed.Aug =
09
> 06:47:53 scspr0512196001 polkitd[740]: Unregistered Authentication Age *

As mentioned in the above log message, you need to look at the
PostgreSQL logs to see what's actually happened.  These logs aren't
telling us anything useful.

However, what you're seeing here is that it's hard to write a backup
system using the low-level PostgreSQL base backup API, which is why
there are good tools written that handle all of that for you.  I'd
strongly recommend that you look at using one of those tools for your
backups instead of trying to roll your own.  My favorite is pgBackRest
(though I'm a bit biased by that), but there's a few different options
out there (barman, WAL-E are two others).

Thanks!

Stephen

--STPqjqpCrtky8aYs
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJbbWsuAAoJEO1sijiDR2RVr3UP/jNq1Ej3N7PZnh9zyAYHO9Cs
87m14jQQVnWEmfPzz03M9LFYruk+SapN1y1sOcLdpcpyUkVZE6/3X95DjR0lBl9M
35ovbP1K/p/VU7sTFRUKDr36tWgUNFD9lGpbpoqzEP5GyTpuETT1ie5VTznpoXi1
4H1V4SVy+iZUEqDXoyeTiuS3jOy5nCD6sk450iZRcFqQ6WUIJB6kGpVngJf5HOZO
2KpH2B42i+tKFgmhsIL/Sog2xhc2roAjHyhMBpgQD7lyto6kQghxW/3vZg3MYMlA
x6S/aX9bpu43kVpbc7EHqdaZmmN3OO284sno4vOq3vpgvg3d8Y52ldzCIBBzyY/U
EZKOamosicd5Pe6fZW/K8Qik/i5K64P9c+pWKtpQGFZRBAzhcqLnI9UAT3bv5wyz
641oR9A5zvZdLfXnStnXmBjb6tiSsB488rDWa2OuBxSnZylOaE87x0yj0vy4SHoW
U5sjR39rrlOpQqOJjm3qTv8yXyWmALdrdBatFXA9CCyee5BOCKQnX2wt9+RLX6ol
rz+4zyIO+rObfCbdKztzbx5YHKj4E6niP4zhZ5KE9IjZ9gAcW8dKq1m9+GyhJ6bl
L4YEAE3p1/c8tqaoh34gFu2tPsI+x1E7RtiCJvMif9prl1kgLUv3Sjf/DLJ/unxp
bkIZhbh32LydMPKdOVwx
=vIih
-----END PGP SIGNATURE-----

--STPqjqpCrtky8aYs--