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.
The pgx_rcvall command is used for recovery.
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 15 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.
Note
Backup operation cannot be performed on an instance that is part of a streaming replication cluster in standby mode.
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.
Mirrored WALs can be used only for backup/recovery using the pgx_dmpall command or WebAdmin.
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:
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.
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.