Best Practices for Backup and Restore of Database Content

This page is intended to give guidance on the process of the backup and restore of database contents. It is important to understand the difference between a full database backup and an export of repositories. The export of repositories may lead to inconsistent data, if during the export timeframe the repositories are still being worked on.

Maintenance Mode

We recommend setting a maintenance window for production critical activities like database or repository backups to avoid user actions. It’s best to perform backup or repository backup actions when there are no users present on the system.

Cadenza can show a banner to users, announcing such a timeframe, but the applicaton itself will not be shut down and will not prevent users from working with the application.

As Cadenza is usually being served through a reverse proxy or load balancer, these components can be used and configured to block access to Cadenza during the timeframe of the backup.

Continuous access might lead to inconsistent data at worst, particularly with Self-Service-Data / repositories.

Best Practices for PostgreSQL:

  • If possible, use or create just one database (including CDS) with multiple schemas contained in this one database.

  • execute pg_dump on the database (and not on the schema) — doing so will ensure the backup is consistent across all schemas.

Best Practices for complex setups:

Sometimes it’s not possible to store all data, including subject databases, within one database system or host. Sometimes also parts of data is being served through a different type of database. If this is the case, this setup needs to be taken into account when planning the backup strategy: Backing up the different databases / components on the same backup schedule (i.e. during the same timeframe, possibly in order) should get you consistent data, but due to the nature and complexity of the configurations, we can’t go into details here. Please get in contact with us if you need specific information for your Cadenza installation.

Point in Time Recovery

Point-in-Time Recovery (PITR) in PostgreSQL is a powerful feature that allows Administrators to restore the database cluster to a specific moment in the past. It’s important to be aware of the limitations of the PITR. PITR should not be used as a global Undo function.

  • Not for Partial Restorations: PITR can only restore the entire database cluster, not individual tables or schemas.

  • Requires WAL Archiving: Continuous archiving of WAL files is necessary for PITR to function, which can be resource-intensive.

  • Not Suitable for Logical Backups: PITR relies on file-system-level backups and WAL files, so tools like pg_dump cannot be used as part of a PITR solution.

PITR helps recover from application bugs, hardware failures, or disk corruption by restoring a clean snapshot and replaying valid changes.

Also in case of catastrophic failures like natural disasters or cyberattacks, PITR ensures business continuity by quickly restoring the database to its last consistent state.

Repositories, Backup and Export

Cadenza allows to import and export repositories.

  • The lifecycle import and export of repositories is used to transfer relevant parts of a repository and import it into a different system (for example from test to production). The import includes the replacement of existing versions of the repository.

  • The import then includes only the lastest or published versions of the items.

  • Overriding will not merge the content into existing entries like personal snapshots or report scheduling jobs.

  • The import should run as an zero trust admin.