You are here

All You Should Know of PostgreSQL full backup

It is necessary to protect the valuable data by through the regular backup of the PostgreSQL databases. For an easy and secure backup the company N2WS provides the facility for PostgreSQL full backup.

This can be achieved by 3 ways:

1.SQL dump

2.File system level backup

3.Continuous archiving

Types of backup:

1.Physical backup or filesystem-level backup: It involves snapshots of the database files which can’t be made consistent because of the constant writes made to the files.

So, to bring consistency to the physical backups, there has to be atomicity and durability of the transactions. This can be ensured through PostgreSQL. The continuous archiving process takes care of the write-ahead log segment files and the consistency of the database is maintained through the information stored in WAL segments. This also enables to establish data consistency after a crash.

In order to avoid the creation of inconsistent snapshots while performing filesystem backup, a low-level API is provided by the PostgreSQL for taking physical backup.

The execution of the following commands ensures the prevention of any usage changes to the files:

1.pg_start_backups() should be executed before the process.

2.pg_stop_backup() should be executed after  the process.

During the above process, the WAL segments produced between the execution of the above functions along with the filesystem-level snapshot is required for restoration which is termed as “base backup”

For a functional backup, the WAL segments that were generated during the backup creation is required. In case the segments generated after the backup are not archived, one can only have a “stand-alone physical backup”. But a continuous archiving is possible by continuing the archiving of write-ahead log segments.

This enables the restoration of the database from the base backup on which the replay of WAL segments can be continued. Since one can have a consistent state in spite of terminating the replaying of the WAL, it is termed as “point-in-time recovery”. Thus, the restoration of a database is possible from the point of its creation to any point,

2.Logical backups or SQL dumps:

At any given point in time, this ensures the database consistency. Besides placing the locks, it can be compelled to wait till other sessions release their locks.

The dumping software scans the tables in an order to fetch the relations, such that the constraints are met during the restoration process

There are some extra objects required to ensure full operation of the restored database, although a single database dumping can efficiently restore all the schema and data. The global objects such as the memberships, database roles, and attributes exist on the instance level, because of which they are not part of the single database dump even when all the privileges are dumped with the database.

2 tools are provided by PostgreSQL to create logical backups:

1.pg_dumpall :

It works as a complementary tool to the other tools for dumping global objects as well as used for dumping all the objects in an instance.


A single database can be dumped from an instance using this command.

Backups supported by

This provides for standalone physical backups which implies that extra care has to be taken while choosing the PostgreSQL version to be used with the backups.

It is not possible to use pg_dumpall for dumping the instance objects of the global database even when one can to create SQL dumps of a Compose-hosted database because of the lack of access to the database superuser account

One can use the tools available in Compose’sWebUI for restoration from the backups. Partial restoration of SQL dumps is possible but if someone opts for full restoration, he would get the error.

Related posts