Use the pgx_dmpall command and pgx_rcvall command to perform backup and check the backup result.
Preparing for backup
You must prepare for backup before actually starting the backup process.
Follow the procedure below.
See
Refer to " Preparing Directories to Deploy Resources" in the Installation and Setup Guide for Server for information on the location of directories required for backup and for points to take into account.
Prepare the backup data storage disk
For backup, prepare a separate disk unit from the database storage disk and mount it using the operating system commands.
Create a directory where the backup data will be stored
Create an empty directory. Set appropriate permissions so that only the instance administrator can access the directory.
# mkdir /backup/inst1 # chown symfo:symfo /backup/inst1 # chmod 700 /backup/inst1
Specify the settings required for backup
Stop the instance, and set the following parameters in the postgresql.conf file.
Start the instance after editing the postgresql.conf file.
Parameter name | Setting | Description |
|---|---|---|
backup_destination | Name of the directory where the backup data will be stored | Specify the name of the directory where the backup data will be stored. Appropriate privileges that allow only the instance administrator to access the directory must already be set. Place the backup data storage destination directory outside the data storage destination directory, the tablespace directory, and the transaction log storage destination directory. |
wal_level | archive or hot_standby | Specify the output level for the transaction log. |
archive_mode | on | Specify the archive log mode. Specify [on] (execute). |
archive_command | 'installationDirectory/bin/pgx_xlogcopy.cmd "%p" "backupDataStorageDestinationDirectory/archived_xlog/%f"' | Specify the path name of the command that will save the transaction log and the storage destination. |
Refer to "Appendix A Parameters" and "Write Ahead Log" under "Server Administration" in the PostgreSQL Documentation for information on the parameters.
You can create a named point in the database for use when restoring data.
Use the psql command to set a point.
Set a restore point before starting operations if you want to recover the database to a point other than when a backup was taken, or the latest point preceding an error. At recovery, specify this restore point as the recovery end point. It is useful to set a restore point, for example, before you execute an application.
Example
The following example uses the psql command to connect to the database and execute the SQL statement to set a restore point.
However, when considering continued compatibility of applications, do not use functions directly in SQL statements. Refer to "Notes on Application Compatibility" in the Application Development Guide for details.
postgres=# SELECT pg_create_restore_point('batch_20130703_1');
LOG: restore point "batch_20130703_1" created at 0/20000E8
STATEMENT: select pg_create_restore_point('batch_20130703_1');
pg_create_restore_point
-------------------------
0/20000E8
(1 row)Refer to "7.3.2 Using the pgx_rcvall Command" for information on using a restore point to recover the database.
Note
Name restore points so that they are unique within the database. Add the date and time of setting a restore point to distinguish it from other restore points, as shown below:
YYMMDD_HHMMSS
YYMMDD: Indicates the date
HHMMSS: Indicates the time
There is no way to check restore points you have set. Keep a record in, for example, a file.
See
Refer to "System Administration Functions" under "Functions and Operators" in the PostgreSQL Documentation for information on pg_create_restore_point.
Use the pgx_dmpall command to perform backup. You can even embed the pgx_dmpall command in OS automation software to perform backup.
The backup data is stored in the directory specified in the backup_destination parameter of postgresql.conf.
Specify the data storage destination in the -D option. If the -D option is omitted, the value of the PGDATA environment variable is used by default.
Example
> pgx_dmpall -D /database/inst1
Note
Backup stores the data obtained during the backup and the backup data of the data obtained during previous backup.
If the data to be stored in the database is encrypted, refer to the following and back up the keystore:
Use the pgx_rcvall command to check the backup status.
Specify the following values in the pgx_rcvall command:
The -l option indicates backup data information.
Specify the data storage destination in the -D option. If the -D option is omitted, the value of the PGDATA environment variable is used by default.
> pgx_rcvall -l -D /database/inst1 Date Status Dir 2013-07-01 13:30:40 COMPLETE /backup/inst1/2013-07-01_13-30-40
If an error occurs and backup fails, a message is output to the system log.
In this case, the backup data is not optimized. Ensure that you check the backup result whenever you perform backup. If backup fails, remove the cause of failure and perform backup again.
See
Refer to "pgx_dmpall" and "pgx_rcvall" in the Reference for information on the pgx_dmpall command and pgx_rcvall command.