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