Databases store critical business data, so enterprise data backup must support data protection, business continuity, and recovery point and time objectives (RPO and RTO). This article covers PostgreSQL and Fujitsu Enterprise Postgres backup methods, recovery testing, and data security.
Why enterprise data backup needs a strategy, not a schedule
An effective enterprise data backup strategy defines acceptable data loss, recovery times, and validation requirements. A completed backup job doesn't prove that files and archive logs are recoverable within the required timeframe.
Two objectives guide the choice of backup and recovery solution:
- Recovery point objective: The maximum amount of data loss the business can tolerate, expressed as a period of time.
- Recovery time objective: The maximum tolerable downtime before a system or service must be restored.
RPO and RTO depend on workload criticality, scalability, and infrastructure, including virtual machines (VMs), data center deployments, and cloud environments. A reporting database may tolerate overnight recovery, while a payments ledger may require minimal transaction loss.
Database backup supports disaster recovery and business continuity alongside high availability. Connection pooling, load balancing, and automated failover with Pgpool-II or an active-active reference architecture can help maintain availability.
However, replication may reproduce human error, corruption, or ransomware damage. PostgreSQL backup and recovery provides a separate recovery mechanism for different RPO and RTO requirements.
What is PostgreSQL backup/recovery?
PostgreSQL backup and recovery involves creating copies of database data and restoring them after data loss, corruption, or system failure. PostgreSQL database backup methods include logical backup and physical backup.
- Logical backup: Extracts database data into SQL scripts or archive files, offering flexibility for migration between PostgreSQL versions or systems. Longer restore times may make it less suitable for large databases with strict RTO requirements.
- Physical backup: Copies database files directly, supporting efficient whole-cluster recovery after hardware failure. Combined with continuous WAL archiving, it enables point-in-time recovery.
Logical backup
Physical backup
Logical backup
A logical backup extracts the database data to a file while the PostgreSQL database is running. The following commands are available to perform a logical backup.
- pg_dumpall command
Backs up the contents of the entire database cluster in SQL format. You can then run the psql command to restore the database cluster using the latest backup.
- pg_dump command
Backs up the contents of the specified database in SQL script or archive format. SQL scripts can be restored using psql, while archive-format backups can be restored using pg_restore.
- SQL command COPY
Exports the data in the specified table in text, binary, or CSV format. The COPY command can also import this data to restore the table's contents from the exported file. It does not back up the table's schema, indexes, or dependencies.
Physical backup
Physical backup copies database files while the database is stopped (offline) or running (online).
Offline backup
The entire database cluster is backed up by copying all database files using the commands provided with the OS while the PostgreSQL database is stopped. The acquired backup data can be used to restore the cluster to the backup point by running the command provided with the OS and then starting the PostgreSQL database.
Online backup
Online backup is acquired with the PostgreSQL database still running.
Online recovery uses a base backup and write-ahead log (WAL) records, which capture database changes during and after the backup. The base backup and required WAL archives enable recovery after failure.
What is WAL / transaction log?
The WAL (write-ahead log) or transaction log is the history of changes made to the database by transactions. It plays a central role in PostgreSQL transaction logging, replication, and recovery.
The following shows the flow from acquiring an online backup to recovering to the latest status after a problem occurs.
Online backup flow
Using archived WAL records, it is possible to recover to the backup point, the latest point, or any time between them, provided the required WAL records are available.
The online backup features of PostgreSQL are as follows.
- Online backup with continuous archiving
You initiate a base backup using PostgreSQL's supported backup tools or backup API, copy the required database files, and complete the backup procedure. The acquired backup data will be recovered using point-in-time recovery. For the restore point, you can set the backup point, any specific time, or any point covered by the available WAL archives.
In addition to the features of PostgreSQL, Fujitsu Enterprise Postgres also provides the following online backup features.
- pgx_dmpall command
A command provided by Fujitsu Enterprise Postgres to perform an online backup by a continuous archive with one touch. The acquired backup data can be recovered using the pgx_rcvall command or WebAdmin. For the restore point, you can set the backup point, any specific time, or any point.
- WebAdmin
A GUI provided by Fujitsu Enterprise Postgres to perform a one-touch online backup with a continuous archive. You can recover to the point when the backup was acquired or the latest time.
Both methods back up the entire database cluster.
What is point-in-time recovery?
It is a feature that specifies an arbitrary time between the backup point and failure and restores the data to the specified time. For example, if a database has been updated incorrectly, you can restore it by specifying the date and time before the incorrect update occurred.
What is the pgx_rcvall command?
It is a command provided by Fujitsu Enterprise Postgres to perform recovery using a backup created by the pgx_dmpall command and the archive WAL. For details on the command, refer to Fujitsu Enterprise Postgres Reference Guide > Chapter 3 - Server commands > pgx_rcvall.
How to decide the backup method
As you can see, there are various ways to back up a PostgreSQL database, so how should you choose a method that suits your business?
The selection criteria must consider factors such as:
- To what point in time should the data be recovered
- Whether the service running can be stopped for a backup
- What types of failure the backup must protect against
The following lists some sample requirements with the optimal backup method.
- To recover only one table after its data was lost due to incorrect operation
Logical backup using the pg_dump command, which supports granular recovery without restoring the entire database cluster.
- To build a new system on another server, migrate all the data from the old system, and continue operation
Logical backup using the pg_dumpall command, because the entire system will be migrated.
- To recover data to the latest point just before a disk hardware failure
Physical backup via online backup by continuous archiving, WebAdmin, or the pgx_dmpall command.
- To recover to a point before the data was logically corrupted
Physical backup via online backup by continuous archiving or the pgx_dmpall command.
Restore testing should demonstrate that the chosen backup method meets the recovery time objective (RTO), rather than relying on estimates.
An enterprise backup solution must balance operational overhead and storage efficiency. A full backup captures the complete dataset; incremental backups and differential backup methods reduce subsequent data transfers. Backup software may also offer compression and deduplication, but third-party tools introduce integration and maintenance requirements.
Fujitsu Enterprise Postgres provides native tools, including pgx_dmpall and WebAdmin, reducing reliance on separate utilities.
The following shows the flow of selecting a backup method that addresses these requirements. The Fujitsu Enterprise Postgres features are indicated with red boxes.
Examples of selecting a backup method
Testing and validating recovery, not just backup jobs
A successful backup job does not prove that recovery works. Restore testing checks data integrity and verifies recovery against the RTO through three practices:
- Scheduled restore drills.
Restore backups to a separate environment on a defined schedule, based on business criticality and recovery requirements. Include the relevant WAL archives and verify that the restored database is accessible, consistent, and functioning correctly with its applications.
- Measured recovery times.
Record the time required to retrieve backup data, restore the base backup, and replay WAL. Compare the total recovery time against the agreed RTO to identify bottlenecks and determine whether the disaster recovery plan is achievable.
- Documented recovery procedures.
Maintain a version-controlled recovery runbook covering restore steps, required credentials, dependencies, and validation checks. Update it when systems or configurations change so recovery doesn't depend on individual knowledge.
Restore drills can also reveal problems that routine backup monitoring misses, including expired storage credentials, missing WAL archives, and backup retention gaps. Identifying these issues before an incident helps reduce the risk of extended downtime and supports business continuity.
Securing backup data: encryption, access control, and audit logging
Backup files and WAL archives require the same data protection controls as production databases. Even when enterprise backups cover servers, endpoints, and other systems, database recovery has specific requirements.
Ransomware attacks and other cyberattacks can target backup repositories. Encryption, access control, audit logging, and isolated or immutable copies strengthen ransomware protection and cyber resilience.
Apply production database governance controls to backup storage and recovery environments. Threat detection and monitoring can help identify unauthorized access or attempts to modify or delete backups.
In Fujitsu Enterprise Postgres, Transparent Data Encryption extends automatically to backup data, WAL, and archive logs at the same tablespace-level encryption strength as the live database, without separate encryption configuration. The platform's dedicated audit logging capability also supports tracking access to sensitive database information.
These capabilities complement broader PostgreSQL security best practices, including those for regulated environments and the enterprise database features regulated industries should prioritize.
Summary of PostgreSQL backup and recovery
The table below summarizes the types of backup and recovery available with PostgreSQL and Fujitsu Enterprise Postgres features.
Choose enterprise data backup methods against RPO, RTO, and operational requirements, then validate recovery through regular testing.
Backup and recovery backed by enterprise PostgreSQL support
With more than 15 years of PostgreSQL engineering experience, Fujitsu Enterprise Postgres combines PostgreSQL compatibility with enterprise data backup and recovery capabilities.
Key capabilities include:
- One-touch backup and recovery
The pgx_dmpall command and WebAdmin provide one-touch online backup and recovery, reducing manual administration.
- High-speed recovery
Supports TB-scale backup and recovery operations against selected tablespaces, helping organizations manage large enterprise databases.
- WAL duplication
Provides additional protection against WAL storage failure, reducing a potential single point of failure.
- Encryption and auditing
Transparent Data Encryption and Dedicated Audit Log support data security, traceability, and governance across database operations and backups.
- 24/7 global enterprise support
Provides access to specialist PostgreSQL engineers for troubleshooting, recovery issues, and ongoing database operations.
These capabilities support the operational requirements of mission-critical PostgreSQL environments, including database modernization in manufacturing, where service availability and recovery performance are essential.
Evaluate Fujitsu Enterprise Postgres backup, encryption, and recovery capabilities in your own environment with a free trial.
Frequently asked questions about enterprise PostgreSQL backup and recovery
What is the difference between RPO and RTO in a backup strategy?
The recovery point objective (RPO) defines the maximum tolerable data loss, while the recovery time objective (RTO) defines the maximum acceptable downtime following an incident. RPO determines how far back an organization may need to restore data, influencing backup frequency and WAL archiving requirements. RTO determines how quickly database services must be restored, influencing the choice of backup method, recovery infrastructure, and disaster recovery procedures. Both should be defined according to business continuity requirements and verified through recovery testing.
How often should PostgreSQL restore tests be performed?
PostgreSQL restore tests should follow a defined schedule based on database criticality, recovery objectives, and the organization's disaster recovery plan. Mission-critical databases generally require more frequent testing than lower-priority systems, with additional tests following significant infrastructure or configuration changes. Each test should restore backup data to a separate environment, verify data integrity and application functionality, and measure actual recovery time against the agreed RTO. Documenting the results helps identify recovery failures before a real incident.
Can PostgreSQL backup data be encrypted?
Yes, PostgreSQL backup data can be encrypted using appropriate backup software, storage-level encryption, or database platform capabilities. In Fujitsu Enterprise Postgres, Transparent Data Encryption extends automatically to backup data, WAL, and archive logs associated with encrypted tablespaces, without separate encryption configuration. Encryption helps protect sensitive information from unauthorized disclosure, but should be combined with access control, secure key management, and audit logging as part of a wider enterprise data protection strategy.
Is pg_dump or physical backup better for a large production database?
Physical backup is generally better suited to large production PostgreSQL databases where recovery speed and point-in-time recovery are priorities. It copies database files directly and, when combined with continuous WAL archiving, supports recovery to a specific point before data loss or corruption. The pg_dump command provides greater flexibility for granular recovery, individual database backups, and migrations between PostgreSQL versions. The best approach depends on database size, RPO, RTO, and operational requirements.
