Point-in-time recovery for PostgreSQL

How to restore the PostgreSQL database in case of disaster.

Introduction

We recommend to run a full backup of the database on every server, during the night by the pg_basebackup or pg_receivewal service. It runs continuously for archiving for point-in-time recovery in case of disaster.

You have to take additional storage on different mount points for a full backup and archiving of PostgreSQL.

PostgreSQL database should be installed by Ansible playbook. Ansible creates OS aliases, that is pgstatus to see which systemd services are running on the DB server and sp (switch user to postgres from root). You can run the pgstatus command from root or postgres user to check the systemd services.

CODE

[postgres@de-nue-sri data]$ pgstatus

You should find below services running on the DB server:

CODE

mnt-sdb.mount  ---->  This is the dependent service of Postgres service which will make sure that backup directory should be mounted.

postgresql-10.service ----> This service is used for to manage the Postgres database (start/stop/restart/status).

postgresql_receivewal.service ----> This service is used for continuous archiving

postgresql_receivewal_cleaner.service ----> This service is used for cleaning the archiving

postgresql_basebackup.timer ----> This is the timer service which will call the pg_basebackup to take the backup (work same as crond)

postgresql_receivewal_cleaner.timer ----> This is the timer service which will call the pg_receivewal cleaner to clean the archive log (wal file)

Recover the database in case of disaster

In our example, we have taken up additional storage with a different mount point, i.e. /mnt/sdb for a full database backup and archiving.

CODE

/mnt/sdb/backup/ --> Contains the daily full backup by "pg_basebackup" /mnt/sdb/archive/ --> Contains the continuously archiving

Below is an example of how to recover the database during a disaster with point-in-time-recovery:

Step 1

We have created a table "a" with some data in the "postgres" database and noted down the time:

Connect to PostgreSQL DB:

CODE

-bash-4.2$ psql  psql (10.4)  
Type "help" for help. 
postgres=# create table a(a int); 
postgres=# insert into a values (1); 
postgres=# select * from current_timestamp; 

current_timestamp  
-------------------------------  
2019-01-23 02:13:47.870165-04  
postgres=# insert into a values (2);  
INSERT 0 1 
postgres=# select * from current_timestamp;  

current_timestamp 
-------------------------------  
2019-01-23 02:43:53.758413-04 
postgres=# insert into a values (3);  
INSERT 0 1  
postgres=# select * from current_timestamp;  

current_timestamp 
-------------------------------  
2019-01-23 02:44:55.575508-04

Step 2

We suppose that the disaster occurs.

We recommended to use the systemctl to manage the Postgres services i.e. stop/start/restart/status. If the version is PostgreSQL 9.6, then change the Postgres service name accordingly.

CODE

-bash-4.2$ systemctl stop  
postgresql-10.service 
waiting for server to shut down.... 
done server stopped 

-bash-4.2$ systemctl stop postgresql_receivewal.service

Step 3

Restore & recover

Restore the database from the current full pg_basebackup named for example 20190123_021704 as below from the postgres user:

CODE

-bash-4.2$ rsync -avx --delete /mnt/sdb/backup/20190123_021704 $PGDATA/

Note: Hope $PGDATA is set for your Postgres database, if not then set it in the ".bash_profile" of your PostgreSQL database home directory with postgres user.

How: Suppose, your PostgreSQL home directory for PosgreSQL-10 version is "/var/lib/pgsql" then edit the ".bash_profile" and add below lines & source it. 

[postgres@de-nue-sri ~]$ vi .bash_profile 

export PATH=/usr/pgsql-10/bin:$PATH 
export PGDATA=/var/lib/pgsql/10/data 
export PGDATABASE=postgres 
export PGUSER=postgres 
export PGPORT=5432 
export PGLOCALEDIR=/usr/pgsql-10/share/locale 
export MANPATH=$MANPATH:/usr/pgsql-10/share/man

Source it as below:

CODE

[postgres@de-nue-sri ~]$ source .bash_profile

You can also x-check the $PGDATA path as below:

CODE

[postgres@de-nue-sri ~]$ 
echo $PGDATA

The result should be as below: /var/lib/pgsql/10/data.

Step 4

Prepare for recovery

To create a recovery.conf file in the PostgreSQL $PGDATA directory, we have to mention the below parameters for recovery in the recovery.conf file.

CODE

[postgres@censhare-db ~]$ cd $PGDATA 
[postgres@censhare-db ~]$ vi recovery.conf 

restore_command = 'cp /mnt/sdb/archive/%f "%p"' 
recovery_target_time =  '2019-01-23 02:44:55.575508-04'

Note: Provide the time as above at which point-in-time you want to recover the database or at which time the disaster occurred. You can also mention the approximate time as per your requirement.

Step 5

After creating the recovery.conf, start the PostgreSQL database service from systemctl as below:

CODE

bash-4.2$ systemctl start  postgresql-10.service

After restarting the database, convert the recovery.conf file as "recovery.done".

All records (1, 2, and 3) of table a should be available in the Postgres database after a point-in-time recovery.

CODE

-bash-4.2$ psql psql (10.4) 

Type "help" for help. 
postgres=# select * from a; 
a 
--- 
1 
2 
3 
(3 rows) 
postgres=#