Point in time recovery in PostgreSQL
Today’s post is about point in time recovery for a PostgreSQL database. In this post I will demonstrate, by creating a new db in PostgreSQL and creating tables and inserting some values in it . After that we will take a note of the time, and will destroy the database and restore from the backup.
First of all, we need to create a directory for keeping all the WAL log, Its like an arhive_dest (for people coming from Oracle background) , which keeps the changes in the wal log at some other location, which will help us to restore the database in point in time.
–bash-4.2$ mkdir -p /opt/pgfiles/pg_archive_log/primary
NOTE:
Whenever we want to restore or replicate , then we need to give an archive location to archive the WAL, since each wal is only 16 MB and it will overwrite it whenever the wal is filled .
To have the wal data for PITR , we need to archive that in a separate area for restore .
Next , make sure all these parameters are set accordingly in order to perform PITR.
-bash-4.2$ grep wal_level postgresql.conf
wal_level = replica # minimal, replica, or logical
-bash-4.2$
-bash-4.2$ grep archive_mode postgresql.conf
archive_mode = on # enables archiving; off, on, or always
-bash-4.2$
-bash-4.2$ grep archive_command postgresql.conf
archive_command = ‘test ! -f /opt/pgfiles/pg_archive_log/primary%f && cp %p /opt/pgfiles/pg_archive_log/primary%f’
After making the above changes , please restart your pg server , to make sure all the changes are applied at the cluster level.
-bash-4.2$ pg_ctl -D /opt/PostgreSQL/10/data reload
server signaled
Now , create a database on the same cluster
-bash-4.2$ psql -c "create database PITR_DEMO" ; Password: CREATE DATABASE -bash-4.2$ -bash-4.2$ -bash-4.2$ -bash-4.2$ -bash-4.2$ psql Password: psql.bin (10.13) Type "help" for help. postgres=# postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --------------+----------+----------+-------------+-------------+----------------------- newdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | pitr_demo | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | standby_sync | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (7 rows) postgres=# postgres=# \c pitr_demo You are now connected to database "pitr_demo" as user "postgres". pitr_demo=# pitr_demo=# pitr_demo=# create table PITR ( pitr_demo(# id integer, pitr_demo(# title character varying(100), pitr_demo(# content text, pitr_demo(# published_at timestamp without time zone, pitr_demo(# type character varying(100) pitr_demo(# ); CREATE TABLE pitr_demo=# pitr_demo=# pitr_demo=# insert into PITR (id, title, content, published_at, type) values (100, 'testing point in time recovery', 'testing before taking backup', '2020-08-21', 'Parwez_PostgreSQL') ; INSERT 0 1
pitr_demo=# select * from PITR ;
id | title | content | published_at | type
—–+——————————–+——————————+———————+——————-
100 | testing point in time recovery | testing before taking backup | 2020-08-21 00:00:00 | Parwez_PostgreSQL
(1 row)
Now , note the system (db) time , because we will restore the database any time after this particular point in time
After inserting the values in the tables , switch the wal logs to make sure , the wal is archived in the destination directory, that we had created in the beginning .
pitr_demo=# select pg_switch_wal(); pg_switch_wal 0/12060878 (1 row)
Now backup the database(cluster) using pgbasebackup, I am using gzip to compress the backup and -X to make sure all required WAL files have been archived properly , so that during restore , we can restore them from the archive dest directory with certainty.
-bash-4.2$ pg_basebackup -Ft -X none -D - | gzip > /opt/pointInTimebackup/db_file_backup.tar.gz NOTICE: pg_stop_backup complete, all required WAL segments have been archived -bash-4.2$ -bash-4.2$ -bash-4.2$ ls -ltr /opt/pointInTimebackup/db_file_backup.tar.gz -rw-rw-r-- 1 postgres postgres 7086786 Aug 21 00:08 /opt/pointInTimebackup/db_file_backup.tar.gz
Stop the database and destroy the data.
-bash-4.2$ pg_ctl -D /opt/PostgreSQL/10/data/ status pg_ctl: server is running (PID: 4793) /opt/PostgreSQL/10/bin/postgres "-D" "/opt/PostgreSQL/10/data" -bash-4.2$ -bash-4.2$ pg_ctl -D /opt/PostgreSQL/10/data/ stop waiting for server to shut down…. done server stopped -bash-4.2$ cd /opt/PostgreSQL/10/data/ -bash-4.2$ -bash-4.2$ ls -ltr total 72 -rw------- 1 postgres postgres 3 Jul 22 08:43 PG_VERSION drwx------ 2 postgres postgres 6 Jul 22 08:43 pg_twophase drwx------ 2 postgres postgres 6 Jul 22 08:43 pg_snapshots drwx------ 2 postgres postgres 6 Jul 22 08:43 pg_serial drwx------ 4 postgres postgres 36 Jul 22 08:43 pg_multixact drwx------ 2 postgres postgres 6 Jul 22 08:43 pg_dynshmem drwx------ 2 postgres postgres 6 Jul 22 08:43 pg_commit_ts drwx------ 2 postgres postgres 18 Jul 22 08:43 pg_xact -rw------- 1 postgres postgres 1636 Jul 22 08:43 pg_ident.conf drwx------ 2 postgres postgres 6 Aug 2 00:30 pg_replslot -rw------- 1 postgres postgres 129 Aug 2 00:40 postgresql.auto.conf -rw-rw-r-- 1 postgres postgres 23098 Aug 2 15:36 postgresql.conf -rw------- 1 postgres postgres 4398 Aug 20 23:03 pg_hba.conf drwx------ 2 postgres postgres 6 Aug 20 23:09 pg_tblspc -rw------- 1 postgres postgres 0 Aug 20 23:10 tablespace_map.old -rw------- 1 postgres postgres 208 Aug 20 23:10 backup_label.old -rw-rw-r-- 1 postgres postgres 107 Aug 20 23:32 recovery.done drwx------ 2 postgres postgres 6 Aug 21 00:09 pg_stat_tmp drwx------ 2 postgres postgres 84 Aug 21 00:09 pg_stat -bash-4.2$ rm -rf * -bash-4.2$ ls -ltr total 0 -bash-4.2$ -bash-4.2$ ls -ltr /opt/PostgreSQL/10/data/ total 0
The database is deleted completely now , and we dont have anything in our data directory .
Now , Restore the database from the backup taken earlier
-bash-4.2$ tar xvfz /opt/pointInTimebackup/db_file_backup.tar.gz -C /opt/PostgreSQL/10/data/ backup_label tablespace_map pg_wal/ ./pg_wal/archive_status/ global/ global/1262 global/1262_fsm global/2964 global/1213 log/postgresql-2020-08-20_233244.log log/postgresql-2020-08-21_000000.log postgresql.conf pg_hba.conf current_logfiles backup_label.old tablespace_map.old recovery.done global/pg_control -bash-4.2$
Create a recovery.conf file under main PG data to specify the recovery method , in our case it is up to certain point in time.
-bash-4.2$ vi /opt/PostgreSQL/10/data/recovery.conf bash-4.2$ cat /opt/PostgreSQL/10/data/recovery.conf restore_command = 'cp /opt/pgfiles/pg_archive_log/primary%f %p' recovery_target_time = '2020-08-21 00:09:03'
Start the database now check if the database is recovered to the time , we have specified in the recovery.conf file
-bash-4.2$ pg_ctl -D /opt/PostgreSQL/10/data/ start waiting for server to start….2020-08-21 00:13:27.309 UTC [5608] LOG: listening on IPv4 address "0.0.0.0", port 5432 2020-08-21 00:13:27.309 UTC [5608] LOG: listening on IPv6 address "::", port 5432 2020-08-21 00:13:27.311 UTC [5608] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2020-08-21 00:13:27.322 UTC [5608] LOG: redirecting log output to logging collector process 2020-08-21 00:13:27.322 UTC [5608] HINT: Future log output will appear in directory "log". done server started -bash-4.2$ -bash-4.2$ -bash-4.2$ psql Password: psql.bin (10.13) pitr_demo=# select * from pitr ; id | title | content | published_at | type -----+--------------------------------+------------------------------+---------------------+------------------- 100 | testing point in time recovery | testing before taking backup | 2020-08-21 00:00:00 | Parwez_PostgreSQL (1 row) We can see the table is restored properly , which we have created before taking the backup and database is restored to the point in time we have specified
Below are logs from the recovery time
1,073 total views, 1 views today