PostgreSQL Backup & Recovery Overview
In this tutorial i explained about how to take PostgreSQL Backup and how to restore it and what are the method in postgresql 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.
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:
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
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.
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:
You can restore the dump two method
PRACTICAL 1.
PostgreSQL pg_dump Backup:
--PostgreSQL plain Format Dump
PostgreSQL pg_dump Restore:
If your database already exists you only need to run the following restore method:
--For restoring plain text format
Cluster Level Dump:
PostgreSQL Logical Backup:
- pg_dump,pg_dumpall 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.
- Plain SQL format(readable,large and is also Default format )
- Custom Format (readable ,small and is also called Binary format)
- Tar Format(ideal for restore)
You can restore the dump two method
- psql
- 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
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
Note: Schema Level and Tables Level dumps can be performed in the same way by adding related options. $ 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
Cluster Level Dump:
$ pg_dumpall -p portnumber > filename.txt
For restoring use psql command$ psql -f filename.txt
Comments
Post a Comment