Configure Hot Backup (Physical) for PostgreSQL database on local or shared filesystem

PostgreSQL physical database backup (Hot) on local or shared filesystem.
Physical backup is copying of all the required database files to secondary location using OS command or any other available utility. Physical backup can be taken as hot or cold. The cold backup will be done while database or processes are completely stopped and the database files or directories will be copied to secondary or secure location.

On the other hand, the Hot backup will be done while the database and processes are up and running, application will be connected and using the database. user or application sessions might be doing either DDL or DML operations.

So lets get set go and configure the Hot backup for postgreSQL database on local file system.

Environment details: I have two machines currently configured.

pg01 -> postgreSQL database.

pg03 -> Used in future for pushing the database backup to remote.

Postgresql version -> 9.6.5

PGDATA = /u01/app/postgres/data (Database file location)

The steps are as follows:

1.) Create backup directory either on local or shared filesystem.

      [postgres@pg01 ~]$ mkdir -p /u01/backups/base

      [postgres@pg01 ~]$ mkdir -p /u01/backups/archive

2.) Set the archive parameter in postgresql.conf using your favorite editor and restart the server.

      wal_level = replica (you can set any value other then minimal)

      archive_mode = on

      archive_command = 'rsync -a %p /u01/backups/archive/%f'

3.) export the backup filename either on script or at OS level.

        [postgres@pg01 ~]$ export BACKUP_NAME=$(date '+%Y%m%d%H%M').tar

4.) Start the backup as follows at database level. You can also do the same by connecting to psql.

       [postgres@pg01 ~]$ psql -c "select pg_start_backup('standalone')"

        pg_start_backup
       -----------------
       0/F000028
       (1 row)

5.) Copy the database files excluding the pg_xlog directory using the below command.

[postgres@pg01~]$tar -cv --exclude='pg_xlog/*' ${PGDATA} -f /u01/backups/base/$BACKUP_NAME *

6.) Once copying is completed, stop the backup as follows,

[postgres@pg01 ~]$ psql -c "select pg_stop_backup(), current_timestamp"

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup |               now             
----------------+----------------------------------
 0/F01B410      | 2017-12-29 18:38:42.647298+05:30
(1 row)
[postgres@pg01 data]$

7.) Goto backup location and check the tar file will be available.

[postgres@pg01 data]$ cd /u01/backups/
[postgres@pg01 backups]$ ls -ltr
total 8
drwxrwxr-x. 2 postgres postgres 4096 Dec 29 17:19 base
drwxrwxr-x. 2 postgres postgres 4096 Dec 29 18:38 archive
[postgres@pg01 backups]$ cd base/
[postgres@pg01 base]$ ls -ltr
total 66380
-rw-rw-r--. 1 postgres postgres 67973120 Dec 29 18:38 1712291715.tar
[postgres@pg01 base]$

Check the archive or WAL files are also available.

[postgres@pg01 backups]$ cd archive/

[postgres@pg01 archive]$ ls -ltr

-rw-------. 1 postgres postgres 16777216 Dec 29 18:37 00000001000000000000000E

-rw-------. 1 postgres postgres 16777216 Dec 29 18:38 00000001000000000000000F

-rw-------. 1 postgres postgres      294 Dec 29 18:38 00000001000000000000000F.00000028.backup

[postgres@pg01 archive]$ 

8.) Copy the postgres.conf, pg_hba.conf and archive files and bundle with backup file.

   [postgres@pg01 ~]$ tar -rf $BACKUP_NAME /u01/backups/archive

9.) Create the recovery.conf file which can be used to recover the database.
echo "restore_command = 'cp /u01/backups/archive/%f %p'" > recovery.conf
echo "recovery_end_command = 'rm -R /u01/backups/archive' " >> recovery.conf

10.) Add the recovery.conf file into backup bundle.
 [postgres@pg01 ~]$ tar -rf $BACKUP_NAME /u01/backups/recovery.conf

11.) Store the backup file somewhere on secure location, if not on NFS.

Note: This method only help you to restore and recover the database until the last backup WAL file. You can't perform the PITR.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Recovering the database from standalone backup

Now some disaster is happens and you need to restore the database from backup. Follow the below steps:

1.) Stop the server, if its already running.

2.) Make sure you have sufficient free space available to restore the database. If you are restoring on the same server, remove all the existing files and subdirectories from the cluster and any directory which is used for tablespace.

3.) restore the datafiles from backup.

 [postgres@pg01 ~]$ cd /u01/backups/base

 [postgres@pg01 ~]$ ls -ltr 

-rw-rw-r--. 1 postgres postgres 67973120 Dec 29 18:38 1712291715.tar

[postgres@pg01 ~]$ tar -xvf 1712291715.tar -C /u01/app/postgres/data

Make sure recovery.conf file exist in the data directory, if file doesn't exist copy the file to data directory before starting the cluster. Also make sure if you are using tbl_spc, the symbolic link must exist.

4.) Cleanup the pg_xlog directory, if you have not already excluded during backup.

5.) start the server, the server will go into recovery mode and start reading the WAL files which require for recovery. Once the recovery if complete ,the recovery.conf file will be renamed to recovery.done. this is to avoid the server go back into recovery during next restart. The server will also move the backup_label file to backup_label.old.

6.) Verify the contents of database to see the recovery is done.