()

Physical PostgreSQL Backup

Is file system-level copy of files that comprise the database cluster can be either Offline or Online mode.


Advantage :

 1) Generally faster to backup and restore.
 2) Allow Point-In-Time-Recovery PITR (If WAL are archived)
 3) Full or Incremental backup.
 4) Will allow DDL during backup.

Disadvantage :
 1) Lack of flexibility:- Backup/Restore entire DB Cluster. Selective restoration options are not possible
 2) Inconsistent backup.

Backup Methods: 


Offline :
Shutdown the DB Cluster – Copy Datafiles – Startup DB Cluster.
           
Advantage:-

 Any file copy tool can be used to copy datafiles
 No need to copy associated WAL files
Disadvantage: 
Database unavailable during backup.
PITR is not possible.


Online :-
Tell PostgreSQL that you are taking a backup – Copy the database files – Copy the Wal files – Tell PostgreSql you have finished the backup.
           
Advantage
:- 

DB available during backup

Disadvantage:-
WAL files must be backed up
WAL parameters must be set correctly


Continuous Archiving :-
Extension of Online backup is Continuous archiving
  All WAL files are backed up
  Allow PITR


Storage Snapshots
:-
Is essentially an image of file system at given point of time
   Consistent snapshot of the file system
  WAL files must be backed up
  Perform checkpoint just before the snapshot
  Checkpoint of FS containing WAL and each tablespace must be simultaneous
  Example LVM Snapshot


Tools (Utilities) for Physical Backup :


pg_basebackup:-
       Use to take hot backup of postgresql cluster and its is not possible to take backup of the individual database or     
       database object.

       Does not affect other clients to the DB.
       Can be used for PITR.
       Makes a binary copy of database cluster files.
       Put a system in and out of backup mode.
       Does not manage the backup library.
       Backup is made over a regular PostgreSQL connection and uses the replication protocols.
       The connection must be made with a user having replication permission.
       The user must be granted explicit permissions in pg_hba.confg
       Eg: pg_basebackup -h myserverdb -z -D /my/backup/location

 

pg_BackRest:-
       Open Source tool.
       Parallel Backup & Restore.
       Local & Remote operation.
       Full, Incremental & differential backup.
       Backup rotation & archive expiration.
       Delta restore.
       Parallel, Async WAL push and get.
       Tablespace & Link support.
       Encryption.
       Limit restore of a specific database.
       Eg : pgbackrest stanza=<server name> backup
       Eg : pgbackrest stanza=<server name> restore
       Eg : pgbackrest stanza=<server name> info

 

BART (EDB Backup & Recovery Tool):-
      Proprietary licensed product by EDB (EnterpriseDB)
      Eg :  bart backup -s <server name>
      Eg : bart restore -s <server name>
      Eg : bart show-backups -s <server name>


Barman (Backup & Recovery Manager):-
      Open Source Tool.
      Remote backup and restore of multiple Servers.
      Backup catalogs.
      Incremental backup.
      Retention policies.
      Archiving and compression of Wal files and backups.
      rsync or postgresql protocols.
     Eg : barman backup <server name>
     Eg : barman restore <server name>
     Eg : barman list-backup <server name>

 

 

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Recent articles


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

2 thoughts on “Physical PostgreSQL Backup

  1. I am not sure where you are getting your info, but great tߋpic. I need to spend some time learning much moгe or understandіng more.
    Thanks for the great information I was looking for this info for mу mission.

  2. Hi cоlleagues, its wonderful post about teachingand cⲟmpletely explained, keep it սp all
    the time.

Comments are closed.