The is the tutorial for setting up Postgres Point In Time Recovery(PITR). This has been tried and tested on Postgres Server version 9.5 and Ubuntu 16.04. For other version of postgres server the steps should be similar.
First edit the postgresql.conf and make the below changers.
wal_level=logical
archive_mode=on
archive_command = 'test ! -f /var/lib/postgresql/pgTestPITR/wals/%f && cp %p /var/lib/postgresql/pgTestPITR/wals/%f'
/var/lib/postgresql/pgTestPITR/wals/ is the path assumed where the wal files will be stored. You can change the path as per your needs. Make sure the path exists before you configure it.
%f will be replaced by Postgres with the archive file name.
%p will be replaced by Postgres with the archive file path including file name.
$ sudo service postgres restart
$ psql -c "create table testPITR1 as select * from pg_class, pg_description;"
$ psql -c "select * from current_timestamp;"
2018-07-04 16:22:08.080111+05:30
Assuming that you are logged in as postgres user on ubuntu, the above first command will create a table testPITR1 in postgres db. The second command is just for printing the timestamp. Make a note of the time stamp here as well as next coming create table commands.
$ psql -c "SELECT pg_start_backup('firstbase_backup');"
$ tar -C /var/lib/postgresql/9.5/main -czvf /var/lib/postgresql/pgTestPITR/backups/pg_basebackup_backup.tar.gz .
$ psql -c "SELECT pg_stop_backup();"
The first command takes the backup with label firstbase_backup. Second command it making a tar.gz file of whole postgres data directory at /var/lib/postgresql/pgTestPITR/backups/pg_basebackup_backup.tar.gz (Will be different for you.)
The third command is stopping the back up.
The above step is optional, but recommended. For understanding the working of pg_start_backup and pg_stop_backup, please refer the official docs.
$ psql -c "create table testPITR2 as select * from pg_class, pg_description;"
$ psql -c "select * from current_timestamp;"
2018-07-04 16:34:17.832994+05:30
$ psql -c "create table testPITR3 as select * from pg_class, pg_description;"
$ psql -c "select * from current_timestamp;"
2018-07-04 16:35:00.450202+05:30
$ psql -c "create table testPITR4 as select * from pg_class, pg_description;"
$ psql -c "select * from current_timestamp;"
2018-07-04 16:36:26.766128+05:30
$ sudo service postgresql stop
$ rm -rf /var/lib/postgresql/9.5/main/*
$ /usr/lib/postgresql/9.5/bin/initdb /var/lib/postgresql/9.5/main/
$ tar xvf /var/lib/postgresql/pgTestPITR/backups/pg_basebackup_backup.tar.gz -C /var/lib/postgresql/9.5/main
Create /var/lib/postgresql/9.5/main/recovery.conf and add the below contents.
restore_command = 'cp /var/lib/postgresql/pgTestPITR/wals/%f %p'
recovery_target_time = '2018-07-04 16:34:17'
The recovery_target_time is optional, if you don't add it, the postgres will try to recover till whatever it can recover.
Next, start the server.
$ sudo service postgresql start
After some time, recovery.conf will be renamed to recovery.done.
Now, you can login into the postgres using psql client and check the contents of postgres db. It should be containing the tables testPITR1 and testPITR2, as we have put in recovery.conf till that time.
https://www.postgresql.org/docs/9.5/static/continuous-archiving.html
https://github.com/anishnath/postgres
https://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/
https://www.postgresql.org/message-id/20130406180711.GA27333@raptor.commandosoftware.com
https://www.reddit.com/r/PostgreSQL/comments/2oajbv/can_someone_explain_to_me_what_postgresql_does/
https://books.google.co.in/books?id=M9mbAgAAQBAJ&pg=PA632&lpg=PA632&dq=pg_start_backup+usability&source=bl&ots=NGIj-ec-lm&sig=qrP0fQoa3cUE7DBJO0uF-e_9cPc&hl=en&sa=X&ved=0ahUKEwjk0tPMlIXcAhUHYo8KHaP9CK4Q6AEIOzAC#v=onepage&q&f=false