PITR allows restoring to a point between the full backup and the last WAL file.
Recovery can be done to the desired point with the recovery_target_time/name/xid/lsn parameters.
Since the database cannot be closed in live environments and other objects continue to be updated (DML), PITR is performed on a different server and the necessary data can be imported to the live environment with SQL/PLpgSQL codes.
Example
# 21:00 - full backup is taken. pg_basebackup -D /RA/basebackup/fullbackup -l “`date`” -c fast -P -v -F tar -z -X fetch -R # 21:15 - 1000 rows are accidentally updated without a ‘where’ condition with auto-commit turned on. update film set release_year = 2022 ; UPDATE 1000
To restore to one minute before the update sentence i.e., 21:14, follow the steps
below. Mostly point-in recovery is done on another machine.
Most of the PITR and full recovery steps are the same. However, in order not to cause
confusion, all the steps have been re-done one by one and re-explained independently.
Note: If it is a table that receives heavy updates, it is not recommended to revert to the
previous day’s pg_dump backup
Step 1
# Recovery processes are delayed. The owner of the folders must be the postgres user. postgres@srv1:/$ sudo mkdir /pg/data/13.6/ postgres@srv1:/$ sudo chown -R postgres:postgres /pg postgres@srv1:/$ sudo mkdir /erp postgres@srv1:/$ sudo chown -R postgres:postgres /erp postgres@srv1:/$ sudo mkdir /ik postgres@srv1:/$ sudo chown -R postgres:postgres /ik postgres@srv1:/$ sudo mkdir /tmp_tbs postgres@srv1:/$ sudo chown -R postgres:postgres /tmp_tbs/
The necessary folders are created. If restoring from backup on a different machine,
postgresql version 13.6 (i.e., the same version as the backed up postgresql) must be
installed first (without running initdb). Since the source code installation in the chapter
1 is explained and the installation from ready packages with apt is explained in the
chapter 16, it is not mentioned here again. (The postgres user’s .profile file also
needs to be edited.)
Step 2
# Data files are restored to original locations. tar xvf /RA/basebackup/fullbackup/base.tar.gz -C /pg/data/13.6/ tar xvf /RA/basebackup/fullbackup/33016.tar.gz -C /erp tar xvf /RA/basebackup/fullbackup/33024.tar.gz -C /ik tar xvf /RA/basebackup/fullbackup/33029.tar.gz -C /tmp_tbs/ # Wal files copied with the operating system are deleted and archived wal files are copied. rm -rf /pg/data/13.6/pg_wal/* cp /RA/archive/* /pg/data/13.6/pg_wal/
tar files are extracted to their original locations. It should be noted that disk space
is needed for both backup and data. It can be a problem in environments with 100s
of terabytes of data. Therefore, the required disk space should be calculated before
starting the restoration. The script should be created. If the network speed is sufficient,
the disks with the backups can be attached to the system over the network (NFS
mount).
Step 3
ln -s /erp /pg/data/13.6/pg_tblspc/33016 ln -s /ik /pg/data/13.6/pg_tblspc/33024 ln -s /tmp_tbs /pg/data/13.6/pg_tblspc/33029
Links are created. OID and folder information are available in /pg/data/13.6/
tablespace_map file.
Step 4
# It is placed into recovery mode. postgres@srv1:/$ rm /pg/data/13.6/standby.signal postgres@srv1:/$ touch /pg/data/13.6/recovery.signal # The configuration file is edited. postgres@srv1:/$ vim /pg/data/13.6/postgresql.conf restore_command = ‘cp /RA/archive/%f “%p”’ recovery_target_time = ‘2022-08-13 18:14:00 GMT’
To start in recovery mode, the recovery.signal file is created in the $PGDATA directory
and the standby.signal is deleted. If both files are found at the same time,
standby mode is enabled.
The restore_command and recovery_target_time parameters are added to the
postgresql.conf file.
In order to restore to 21:14 GMT, 18:14 was written for Turkey local time, which is
GMT+3.
SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_
minute FROM now());
Note: Before version 12, these parameters were written to the recovery.conf file.
Step 5
postgres@srv1:~$ which pg_ctl /pg/home/13.6/bin/pg_ctl pg_ctl -o ‘--config-file=/pg/data/13.6/postgresql.conf’ -D /pg/ data/13.6/ -l pg.log start # Logs are examined. It may give warnings and errors that it cannot find some files tail -f postgresql-Sat.log starting PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit listening on IPv4 address “0.0.0.0”, port 5434 starting point-in-time recovery to 2022-08-13 21:14:00+03 restored log file “000000020000000000000059” from archive redo starts at 0/59000028 consistent recovery state reached at 0/59000138 database system is ready to accept read only connections restored log file “00000002000000000000005A” from archive restored log file “00000002000000000000005B” from archive redo done at 0/5B000060 recovery ended before configured recovery target was reached startup process (PID 7066) exited with exit code 1 terminating any other active server processes database system is shut down
Note: WARNING: archiving write-ahead log file
“0000000200000000000000055” failed too many times, will try again
later
If warning messages come, it is because the same files are in the pg_wal and archive directory.
If the archive directory is deleted or moved to another location, there will be no problem.