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

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

From

Srinath Ganesh

Date:

21 August 2019, 15:01:15

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 test2
SELECT * 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

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

From

Steven Crandell

Date:

22 August 2019, 12:46:18

On Wed, Aug 21, 2019 at 5:01 AM Srinath Ganesh <sganesh@galaxiasol.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 test2
SELECT * 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.

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

From

Rui DeSousa

Date:

22 August 2019, 18:24:23

Dummy Data

create table testPITR1 as select * from pg_class, pg_description; 
select * from current_timestamp;

Is this timestamp used in the recovery?

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

This is a normal shutdown, thus the WAL file should have been archive; was the latest WAL file archive?

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’

If this timestamp was used from the prior step; then the tables testPITR2, testPITR3, and testPITR4 should not be restored as they where created after the given timestamp.

WITHOUT THIS STEP I CANNOT RECOVER Table 2,3,..
cp test2.bad/pg_wal/0* test2/pg_wal/
Any tips?

What is you recovery process after issuing the cp command, a restore without recovery_target_time?

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

From

Srinath Ganesh

Date:

22 August 2019, 18:34:24

After re-trying its working fine. maybe problem was I was starting server before firing up recovery.

On Wed, 21 Aug, 2019, 17:31 Srinath Ganesh, <sganesh@galaxiasol.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 test2
SELECT * 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

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

From

Luca Ferrari

Date:

24 August 2019, 12:52:11

On Wed, Aug 21, 2019 at 2:01 PM Srinath Ganesh <sganesh@galaxiasol.com> wrote:
> 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'

If your recovery target time is BEFORE the creation of the other
tables, you will not see them. Remove the recovery target time, so
your database will replay WALs to the very end (which does not make
much sense as PITR, but ...).

>
> WITHOUT THIS STEP I CANNOT RECOVER Table 2,3,..
>
> cp test2.bad/pg_wal/0* test2/pg_wal/
>

You are mixing WALs from one instance and datafiles from two
instances, which is a very bad practice. The database will not be able
to see what is happening. It works because you have shutdown cleanly
the main instance, so data files are consistent.

Luca