Backing up the database and having a recovery plan to recover from any disaster or human error is one of the most important task of the administrators
You can have a replication cluster, high availability setup on multiple data centers can help you to handle business continuity during the node failure, but if you want to go back to some of the previous states or recover from the accidental delete or drop only backups can help you to recover
Read more about the RPO and RTO Backup terms here
In this blog we will discuss about the taking backup of the PostgreSQL server
If you are interested on Ways of taking MySQL Backup, read here.
Check out my earlier blog on using Nimesa to take MySQL application consistent backup
There are multiple ways you can backup your Postgre Server
- Logical backups
- Physical Backups
Logical Backups of the PostgreSQL
Logical Backup of the PostgreSQL database can be achieved using the pg_dump, its the utility created to perform the logical backups of the database
This will generate the data definition of the tables and the data of the tables, they contain the SQL statement used to create the database and tables and insert the data to the tables, Restoring from the Logical backup is very straight forward, you can load the dump created using the pg_dump to the pg_restore command
pg_dump is application consistent backup, it uses the single transaction to take the dump of the database relying on the multi version concurrency control
The disadvantage of this approach is the long restoration process, SQL statements have to be executed in order to bring back the database and you should compress and copy the database dump to another filesystem and archive storage.
Additional effort has to be taken to backup the global objects.
Physical Backups of the PostgreSQL
Physical backups also known as filesystem level backups are basically the snapshot of the files of the database
Its merely copying the database related files to the backup storage but the complex part in this bring in the application consistency, to create consistent physical backups you need to create a checkpoint using the pg_start_backup before copying the files and run the pg_stop_backup as soon as you copy the files.
In addition to this, you can enable the WAL ( Write-Ahead Logging) archive for the PIT ( Point in time ) recovery of the database. the WAL is the technique in which the changes are first recorded to the log before the changes are stored in the database.
Restoring is very easy, copy the data to the PostgreSQL data directory and start the service, you are done.
The disadvantage of this approach is bringing consistency to the backups. Using the snapshotting technology of the Cloud volumes will give you the faster RPO and the RTO along with the data consistency of the databases.
NIMESA is a unified cloud data protection solution and an application-aware for major business applications running in AWS EC2 Instances. It delivers simple and cost-effective solutions for business continuity and AWS disaster recovery with SLA driven backup policies using AWS EBS snapshot technology.
You can use Nimesa Cloud data protection for backing up the PostgreSQL running on EC2 instance
Also published on Medium.
