Point in time recovery in PostgreSQL

By parwez on August 21, 2020
LinkedIn0

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

LinkedIn0

Leave a Reply

Your email address will not be published. Required fields are marked *