Re: Postgres PITR: Recovery does not seem to fetch from Archive Dir

On Wed, Aug 21, 2019 at 5:01 AM Srinath Ganesh <sganesh(at)galaxiasol(dot)com>
wrote:

> Make and give permission to database dir
>
> cd /home/admin-12/Desktop/
>
> mkdir test2
> sudo chown postgres:postgres test2
>
> mkdir test2_wal
> sudo chown postgres:postgres test2_wal
>
> touch test2.log
> sudo chown postgres:postgres test2.log
>
> Init Database
>
> /usr/lib/postgresql/11/bin/initdb -D /home/admin-12/Desktop/test2
>
> Edit postgres conf
>
> nano /home/admin-12/Desktop/test2/postgresql.conf
>
> archive_mode = on
> archive_command = 'cp %p /home/admin-12/Desktop/test2_wal/%f'
> /usr/lib/postgresql/11/bin/pg_ctl -D /home/admin-12/Desktop/test2 -l /home/admin-12/Desktop/test2.log start
>
> Dummy Data
>
> create table testPITR1 as select * from pg_class, pg_description; select * from current_timestamp;
>
> Backup
>
> SELECT pg_start_backup('label', false, false);
> tar -cvzf test2.tar test2SELECT * FROM pg_stop_backup(false, true);
>
> More Dummy data
>
> create table testPITR2, testPITR3, testPITR4
>
> Kill existing db
>
> /usr/lib/postgresql/11/bin/pg_ctl -D /home/admin-12/Desktop/test2 -l /home/admin-12/Desktop/test2.log stop
> mv test2 test2.bad
> tar -xvzf test2.tar
> sudo chown -R postgres:postgres test2
>
> Recovery conf
>
> nano /home/admin-12/Desktop/test2/recovery.conf
>
> restore_command = 'cp /home/admin-12/Desktop/test2_wal/%f %p'
> recovery_target_time = '2019-08-21 16:14:11.175781+05:30'
>
> *WITHOUT THIS STEP I CANNOT RECOVER Table 2,3,..*
>
> cp test2.bad/pg_wal/0* test2/pg_wal/
>
> Any tips?
> ------------------------------
>
> IF i do above steps in Postgres Docker (using volumes), then I get PANIC:
> could not locate a valid checkpoint record, but this somehow is limited
> to docker
>

Thank you for clearly documenting your test scenario.

I would recommend using pg_basebackup instead of taring up these files
while PG is running.
ref: https://www.postgresql.org/docs/11/backup-file.html
I suspect the PANIC you're seeing in docker is related to you failing to
get an atomic snapshot.
But that doesn't get to the heart of your question....

It might worthwhile to step through your test scenario and track where your
data is written, and therefore where it is available, at all points in time.

When you create your testPITR1 table and then perform a file system backup,
you are capturing the testPITR1 table in your backup.
Good so far.

You then create the testPITR2, testPITR3 and testPITR4 tables.
Given that these tables were created after your backup completed, they are
not available in your test2.tar file.
They are instead written first to the WAL files and then to the database
itself within the test2 directory.
When you move test2 to test2.bad and restore the test2.tar backup file,
you're restoring a database that has no knowledge of tables testPITR2-4.
All of the data relating to the last 3 test tables is stored in test2.bad.

The larger point being that you can't get access to any data that was
written to the database after your backup was taken without rolling forward
through time using WAL replay, which is why you're having to feed your
newly restore database WAL files from test2.bad/pg_wal/
Relevant:
https://www.postgresql.org/docs/11/continuous-archiving.html#BACKUP-TIMELINES

A properly designed PITR solution will retain or expire your base (file
system) backups in conjunction with your WAL files so that you always have
the WAL files you need to make use of your base backups.