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

[Srinath Ganesh <sganesh@xxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxx> 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