PostgreSQL Backup & Recovery Overview

In this tutorial i explained about how to take PostgreSQL Backup,how to restore it and how to recover it and  i explained also what are the method in postgresql  to taking backup or restore.

• In PostgreSQL Backup & Recovery are very user friendly comparing with other database. 
• My pictorial presentation on PostgreSQL Backup and Recovery gives a complete conceptial idea. Looking into Diagram you can make out which backups can be used to restore or recover.

PostgreSQL Physical Backup:
1.Hot backup
2.Cold backup

1.Hot backup:

• cluster will be up and running and the Database should be in Archive Log Mode which is mandatory for Online Backups. 
• Two system functions will notify the instance about starting and stopping the Hot Backup process(pg_start_backup(),pg_stop_backup()). 
• this method is not a fully accurate consistency backup but before issue cp command  you specified "select pg_start_backup('label_name') " so your backup get much better consistency .
• when online backup is in process, couple of the files created and deleted.
• pg_start_backup('label') and pg_stop_backup are the two system functions to perform the Online Backup. 
• pg_start_backup('label') a file backup_label is created under $PGDATA directory and with pg_stop_backup() a file 'wal-segement-number.backup' file created under $PGDATA/pg_xlog.
•  Backup_label will give the start time and Checkpoint location of WAL Segment, it will also notify the PostgreSQL instance that Cluster is in BACKUP-MODE. 
• 'wal-segment-number.backup' file under $PGDATA/pg_xlog directory describes the start and stop time, Checkpoint location with WAL segement number.  
• After pg_stop_backup(), backup_label file is deleted by the PostgreSQL instance.

PostgreSQL Hot backup steps:-
Step 1.Database should be online no need to stop the server.
Step 2.PostgreSQL server should be archivelog mode.
Step 3.Issue the "select pg_start_backup('lable_name');" function in psql terminal before  issuing backup copy command 
Step 4.Set the PGDATA Environment variable  becouse The PGDATA variable should point to the data directory of PostgreSQL,We’ll backup PGDATA directory, so it WIll contain all databases in the instance (remember that PostgreSQL is a multitenant database)
Step 5.Issuse the Linux backup copy command following any one 

cp -r $PGDATA  /your_backup_location
              (or)
tar czf backup.tar.gz $PGDATA
              (or)
rsync -a $PGDATA /wherever/data

Step 6.Issue the "select pg_start_backup('lable_name');" function  in psql terminal after backup completed.

2.cold backup:

• its a simple file system backup of /data directory when Postgres Instance is down .
• the database server should be shut down before copying. 
• PostgreSQL gives flexibility to keep pg_xlog and pg_tblspce in different mount points via softlink. 
• While copying the /data directory including the soft link's data, use the below command.
• If you used this method you will get fully consistency backup than hot backup.

PostgreSQL cold backup steps:-
Step 1.Stop the server using "pg_ctl stop -D /your_database_data_directory_path
Step 2.Set the PGDATA Environment variable.
Step 3.Issuse the Linux backup copy command following any one

cp -r $PGDATA  /your_backup_location
              (or)
tar czf backup.tar.gz $PGDATA
              (or)
rsync -a $PGDATA /wherever/data

Step 4.After backup completed start the server using "pg_ctl start -D /your_database_data_directory_path"

PostgreSQL Logical Backup:

• pg_dump,pg_dumpall,pg_basebackup and pg_restore utilities are  used for logical backups. 
• pg_dump will help in taking backups for Database level, Schema level and Table Level. 
• Pg_dumpall used for cluster level dump.

Three formats are supported to taking PostgreSQL Backup they are:

1. Plain SQL format(readable,large and is also Default format )
2. Custom Format (readable ,small and  is  also called Binary format)
3. Tar Format(ideal for restore)

Restore:
You  can restore the dump two method 

1. psql 
2. pg_restore

• If your dumps(backup) are Custom or Tar Format  you need to use only pg_restore utility 
• If your dumps(backup) are Plain SQL format you need to use  psql utility 

Note: I hope you haven’t surprised by $PGDATA. The PGDATA variable should point to the data directory of PostgreSQL. In my previous post,I recommend to declare these variables in .bash_profile script.

PRACTICAL 1.
PostgreSQL pg_dump Backup:
--PostgreSQL plain Format Dump

$ pg_dump -U username -Fp dbname  >  filename.txt
$ pg_dump -U username dbname -f  filename.txt
$ pg_dump -Fp -U username dbname -f  filename.txt

--PostgreSQL custom Format Dump 

$ pg_dump -Fc dbname -f filename.bak

--PostgreSQL tar Format Dump 

$ pg_dump -Ft dbname -f filename.tar

PostgreSQL pg_dump  Restore:
If your database already exists you only need to run the following restore method:
--For restoring plain text format

$ psql -U username -f filename db_name

--For restoring custom format

$ pg_restore -Fc -U username -d dbname filename.bak

--For restoring tar format

$ pg_restore -U username -d dbname filename.tar

However, if you're creating your database new from the restore you'll want to run a command similar to the following:

--For restoring plain text format

$ psql -C -U username -f filename db_name

--For restoring custom format

 pg_restore -Fc -C -U username -d dbname filename.bak$

--For restoring tar format

$ pg_restore -C -U username -d dbname filename.tar

Note: Schema Level and Tables Level dumps can be performed in the same way by adding related options. 

Cluster Level Dump:

$pg_dumpall -p portnumber > filename.txt

For restoring use psql command

$ psql -f filename.txt