pgbackrest is open source solution for PostgreSQL backup. It has capability to perform full database backup, incremental backup, differential backup. It is very robust tool to perform full cluster recovery, particular database restore/recovery or point in time recovery. With pgbackrest, you can specify backup retention and purging policy. If your cluster size is huge and you need to restore a particular database, pgbackrest can achieve the objective easily.

In this blog, we will go through pgbackrest capability for cluster/database restoration for Fujitsu Enterprise Postgres version 9.5 on RHEL 7 for pgbackrest 2.02. I believe same steps can be applied to other version of PostgreSQL OSS and Linux without any significant change.

 

Please find my blog Pgbackrest 2.02 Installation & backup PostgreSQL Cluster for pgbackrest 2.02 installation/configuration and backup scenarios.

 

Restore and recovery

 

Restore/Recover Entire cluster:

 

Step 1:

Remove all files/directories from PostgreSQL cluster home directory.

Step 2:

[testuser@myserver database]$ pgbackrest –stanza=PD2 restore

 

Step 3: start and verify the PostgreSQL cluster.

[testuser@myserver database]$ pg_ctl start -D $PD2 -w

waiting for server to start….LOG:  redirecting log output to logging collector process

HINT:  Future log output will appear in directory “pg_log”.

.. done

server started

[testuser@myserver database]$ psql -p $PP1 -d postgres

psql (9.6.2)

Type “help” for help.

 

postgres=# \l

List of databases

Name      |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

—————+———-+———-+————-+————-+———————–

testuser      | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

postdb        | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

postgres      | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

supportcenter | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

tdb1          | rol1     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

template0     | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/testuser          +

|          |          |             |             | testuser=CTc/testuser

template1     | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/testuser          +

|          |          |             |             | testuser=CTc/testuser

test          | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

(8 rows)

 

Step4: drop and recreate stanza.

pg_ctl stop –D $CLUSTERDIR -w

pgbackrest –stanza=PD2 stop

pgbackrest –stanza=PD2 stanza-delete

pgbackrest –stanza=PD2  –log-level-console=detail –db-port=5102  stanza-create

pg_ctl start –D $CLUSTERDIR –w

 

Now you can login and verify that cluster has been restored with all databases.

Restore a particular database:

In case you want to restore database in same cluster from where it was dropped,

  • You have to restore the database & you have to restore cluster with point in time recovery command as explained below. Time specified for Cluster PIT restore should be before “drop database” command was executed.

 

e.g if database test was dropped just after “2018-06-18 14:51:31.969592+10”

 

pgbackrest –stanza=PD2 –delta –db-include=test restore

 

pgbackrest –stanza=PD2 –delta –type=time “–target=2018-06-18 14:51:31.969592+10” restore

 

pg_ctl start –D $CLUSTERDIR –w

 

Now you can login and verify that database has been restored.

Perform point in time recovery –

You may need to perform cluster point in time recovery to restore an accidently dropped table.

pgbackrest –stanza=PD2 –delta –type=time “–target=2018-06-12 12:50:55.744” restore

 

This command will automatically create recovery.conf accordingly.

restore_command = ‘pgbackrest –stanza=PD2 archive-get %f “%p”‘

recovery_target_time = ‘2018-06-12 12:50:55.744’

 

Start the cluster

pg_ctl start –D $CLUSTERDIR –w

Now you can login and verify the data.

 

Pgbackrest 2.02 for Restore & Recover PostgreSQL Cluster