Viswamitra's blog

To take the cluster backup we have two methods

• pg_basebackup tool.
• manual execution of commands.

Here I am using the pg_basebackup tool to take the backup.

To take the backup first we need to put the database in archivelog mode.

Enabling archive log mode in postgres :-

We need to set three parameters in postgresql.conf ,

wal_level : Set this parameter to archive or higher. Based on the level postgres write the data into the WAL logs.

archive_mode : Set this to “on”

archive_command : This is a shell command to copy the WAL logs which are filled to archive location. As the WAL logs default size is 16 MB even though the actual data size in the WAL log is 1MB but the WAL logs will occupy 16MB. To reduce the space utilization we can zip the files before copying to archive location. Use the below command for the same.

test ! -f /ARCHIVE_POST/%f && cp %p /ARCHIVE_POST/%f && gzip /ARCHIVE_POST/%f

This command first check the file already exists in the archive location. If not it will copy the WAL log to the archive location and zip it.

NOTE :  gzip will take cpu more , check before using the same.

After setting the parameters , restart the postgres cluster.

export PGDATA=/DATA
pg_ctl restart

3) Now we can take the backup using pg_basebackup.

Execute the below command to take the backup.

pg_basebackup –pgdata=/BKP –format=t –xlog –gzip –label 09-06-2016-0751 –progress –verbose

–pgdata : Backup destination
–format : tar
–xlog : Include the WAL logs in the backup tar (so we do not need to take any WAL logs backup to restore the database ,we can directly start server using this backup without recovery).
–gzip : zip the backup files to reduce the size of backup.
–label : label for the backup.
–progress : print the progress of backup in stdout.

This command will create a backup_label, tablespace_map files in the DATA directory before starting the backup, which contain the data related to the starting time , label, WAL logs in use information and tablespace information.

EX:-

[postgres@localhost BKP]$ cat backup_label
START WAL LOCATION: 2/6A000028 (file 0000000100000002000000D4)
CHECKPOINT LOCATION: 2/6A000028
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-06-08 23:52:09 PDT
LABEL: 09-06-2016-0751

[postgres@localhost DATA]$ cat tablespace_map
17102 /pg_tablespace/data

executing the backup command.

[postgres@localhost BKP]$ pg_basebackup –pgdata=/BKP –format=t –write-recovery-conf –xlog –gzip –label 09-06-2016-0751 –progress –verbose

transaction log start point: 2/6A000028 on timeline 1
39536/39536 kB (100%), 2/2 tablespaces                                        
transaction log end point: 2/6A0000C0
pg_basebackup: base backup completed

After completion of the backup , this will create a backup history file in the Archive log location with the name of the START and ENDING WAL logs which are created during the backup and start and stop time.

EX:-

[postgres@localhost ARCHIVE_POST]$ cat 0000000100000002000000D4.00000028.backup

START WAL LOCATION: 2/6A000028 (file 0000000100000002000000D4)
STOP WAL LOCATION: 2/6A0000C0 (file 0000000100000002000000D4)
CHECKPOINT LOCATION: 2/6A000028
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-06-08 23:52:09 PDT
LABEL: 09-06-2016-0751
STOP TIME: 2016-06-08 23:52:40 PDT

Now the backup is complete ,this created below directories

base.tar.gz ==> this contain the data directory.
17102.tar.gz ==> this is the tar of  the tablespace.

How to recover the database from base backup :-

1) On the destination server create the same directory structure. In my case

/DATA for PGDATA
/ARCHIVE_POST for archival
/pg_tablespace/data for tablespace

untar the base.tar.gz in /DATA ==> tar xvf /BKP/base.tar.gz –create /DATA
untar 17102.tar.gz in /pg_tablespace/data ==> tar xvf /BKP/17102.tar.gz –create /pg_tablespace/data

2) Create a recovery.conf file with below command in /DATA directory. If you want to recover more than base backup , then only follow this step ,otherwise we don’t need to follow this step.

[postgres@localhost DATA]$ cat recovery.conf
restore_command = ‘gunzip /ARCHIVE_POST/%f.gz && cp /ARCHIVE_POST/%f %p’

3) start the postgres cluster

export PGDATA=/DATA
pg_ctl start