Choosing Between PITR and Dump
The following table helps choosing between backup techniques supported by the Bacula Enterprise PostgreSQL Plugin. Major functionalities such as being able to restore databases to any point in time, or being able to filter objects during backup or restore should be used to guide through the backup design. It is quite common to combine Dump and PITR techniques for the same Cluster. In the table, the Custom format corresponds to the Custom Dump format of pg_dump and the Dump format of our Plugin corresponds to the plain format of pg_dump.
Note
Regardless the backup method used, no temporary local disk space is necessary to save any temporary file.
Custom |
Dump |
PITR |
|
|---|---|---|---|
Can restore directly a single object (table, schema, …) |
Yes |
No |
No |
Backup speed |
Slow |
Slow |
Fast |
Restore speed |
Slow |
Very Slow |
Fast |
Backup size |
Small |
Small |
Big |
Can restore at any point in time |
No |
No |
Yes |
Incremental/Differential support |
No |
No |
Yes |
Can restore in parallel |
Yes |
No |
– |
Online backup |
Yes |
Yes |
Yes |
Consistent |
Yes |
Yes |
Yes |
Can restore to previous major version of PostgreSQL |
No |
Yes |
No |
Can restore to newer major version of PostgreSQL |
Yes |
Yes |
No |
1 Custom Dump format is the default.
2 Run the most time-consuming parts of pg_restore - those which load data,
create indexes, or create constraints - using multiple concurrent jobs. This
option can dramatically reduce time to restore a large database to a server
running on a multiprocessor machine.
It requires to store the Dump to the disk first.
3 To restore a SQL plain Dump to a previous version of
PostgreSQL, you might have to edit the SQL file if you are using some features that are not
available in the previous version. Generally, restoring to a previous version of
PostgreSQL is not supported or not guaranteed.
Go back to the Backup Strategies article.
Go back to the main PostgreSQL Plugin page.