Restore a PostgreSQL database in case of disaster.
On every server, a full backup of the database should be run in the night by pg_basebackup service. Aso, pg_receivewal service should run continuously for archiving (for Point-In-Time-Recovery in case of a disaster).
We have to take additional storage on different mount points for a full backup and archiving of PostgreSQL.
PostgreSQL database should be installed by Ansible play book and Ansible will create OS aliases, i.e. pgstatus to check 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.
[postgres@de-nue-sri data]$ pgstatus
You should find that the following services are running on the DB server:
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 recovery
In our example, we have taken additional storage with different mount points, i.e. /mnt/sdb for a full DB backup and archiving.
/mnt/sdb/backup/
--> Contains the daily full backup by "pg_basebackup"
/mnt/sdb/archive/ -->
Contains the continuously archiving
Example
Find an example of how to recover the database from disaster by "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:
-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: The disaster case occurs
It is recommended to use the "systemctl" to manage the Postgres services i.e. stop/start/restart/status. If version is PostgreSQL 9.6 then change the Postgres service name accordingly.
-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 and recover
Restore the database from the current full pg_basebackup, for example, "20190123_021704" as below from postgres user:
-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:
[postgres@de-nue-sri ~]$ source .bash_profile
You can also x-check the $PGDATA path as below:
[postgres@de-nue-sri ~]$ echo $PGDATA Result should be like as below:/var/lib/pgsql/10/data
Step 4: Prepare the recovery
To create the recovery.conf file in PostgreSQL the $PGDATA directory, we have to mention the below parameters for recovery in the recovery.conf file.
[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 the time of disaster came. You can also mentioned the approximate time as per your requirement.
Step 5: After creating the "recovery.conf", start the PostgreSQL database service from systemctl as below:
bash-4.2$ systemctl start postgresql-10.service
After restarting the database, the recovery.conf file should be converted to recovery.done.
All of the records (1, 2, and 3) of table a should be available in the postgres database after point-in-time-recovery.
-bash-4.2$ psql psql (10.4) Type "help" for help. postgres=# select * from a; a --- 1 2 3 (3 rows) postgres=#