This chapter describes how to back up the database.
The following backup methods enable you to recover data to a backup point or to the state immediately preceding disk physical breakdown or data logical failure.
Backup using WebAdmin
This method enables you to back up data through intuitive window operations using the GUI.
WebAdmin is used for recovery.
Backup using the pgx_dmpall command
Execute the pgx_dmpall command with a script to perform automatic backup.To back up data automatically, you must register the process in the automation software of the operating system. Follow the procedure given in the documentation for your operating system.
Additionally, you can also take backups periodically by using any external scheduler.
The pgx_rcvall command is used for recovery.
Use the selected backup method continuously.There are several differences, such as the data format, across the backup methods. For this reason, the following restrictions apply:
It is not possible to use one method for backup and another for recovery.
It is not possible to convert one type of backup data to a different type of backup data.
Information
By using a copy command created by the user, the pgx_dmpall command and the pgx_rcvall command can back up database clusters and tablespaces to any destination and recover them from any destination using any copy method. Refer to "Chapter 16 Backup/Recovery Using the Copy Command" for details.
The formula for deriving the approximate backup time when you use WebAdmin or the pgx_dmpall command is as follows:
backupTime = dataStorageDestinationUsage / diskWritePerformance x 1.5
dataStorageDestinationUsage: Disk usage at the data storage destination
diskWritePerformance: Maximum data volume (bytes/second) that can be written per second in the system environment where operation is performed
1.5: Coefficient to factor in tasks other than disk write (which is the most time-consuming step)
If using the copy command with the pgx_dmpall command, the backup time will depend on the implementation of the copy command.
If you have defined a tablespace, back it up. If you do not back it up, directories for the tablespace are not created during recovery, which may cause the recovery to fail. If the recovery fails, refer to the system log, create the tablespace, and then perform the recovery process again.
There are several considerations for the backup of the keystore and backup of the database in case the data stored in the database is encrypted. Refer to the following for details:
Information
The following methods can also be used to perform backup. Performing a backup using these methods allows you to restore to the point when the backup was performed.
Backup using an SQL-based dump
Dump the data by using SQL. This backup method also enables data migration.
File system level backup
This backup method requires you to stop the instance and use OS commands to backup database resources as files.
Backup by continuous archiving
This is the standard backup method for PostgreSQL.
Refer to "Backup and Restore" in "Server Administration" in the PostgreSQL Documentation for information on these backup methods.
The following backup methods are available for the features provided by Enterprise Postgres:
Category | Backup method | Backup target | Enterprise Postgres | |||||
|---|---|---|---|---|---|---|---|---|
Transparent Data Encryption | Policy-based Login Security | Data Masking | Confidentiality Management | Audit Log Feature | Database Multiplexing | |||
Physical Backup | WebAdmin | Database cluster | Y | Y | Y | Y | Y | Y |
pgx_dmpall command | Y | Y | Y | Y | Y | Y | ||
Backup by continuous archiving | Y | Y | Y | Y | Y | Y | ||
File system level backup | Y | Y | Y | Y | Y | Y | ||
Logical backup | pg_dumpall command | N | Y | N | Y | N | Y | |
pg_dump command | Database | N | N | N | Y | N | Y | |
COPY command | Table | N | N | N | N | N | N | |
Y: Can be used
N: Cannot be used
In a logical backup, the restore process recreates the indexes. For example, if the index size is large, such as for vector data handled by pgvector, the restoration time may be long. If you want to restore quickly, use a physical backup.
Some features are important to keep in mind when backing up. For information about the features, see the following:
Transparent Data Encryption : "5.7 Backing Up and Restoring/Recovering the Database"
Policy-based Login Security："7.6 Backup and Recovery"
Confidentiality Management："Backup/Restore" in the Security Operation Guide
Database Multiplexing："Backup Operation" in the Cluster Operation Guide(Database Multiplexing)
