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

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, <[email protected]> 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
>