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

From: Srinath Ganesh <sganesh(at)galaxiasol(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Postgres PITR: Recovery does not seem to fetch from Archive Dir
Date: 2019-08-21 12:01:15
Message-ID: CAMre02SiX1-2dUuPcYXJ5D3MBfzuuPAV028Lmj+Te_Hp5Oxsxg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Peter Wainaina 2019-08-21 18:17:40 Permission for not Django app to do Write and Read
Previous Message Shital A 2019-08-20 14:45:53 Re: Pgsql resource agent of pacemaker