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

Srinath Ganesh <[email protected]>
Newsgroups gmane.comp.db.postgresql.admin
Message-ID <CAMre02SiX1-2dUuPcYXJ5D3MBfzuuPAV028Lmj+Te_Hp5Oxsxg@mail.gmail.com>
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
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.