Logical backup and recovery

Logical backup

Logical backup can write data stored in the database to a file while PostgreSQL is running. This means that you do not have to stop the database to perform backup. Since backup data is used for recovery, logical backup can only recover to the point when the backup was performed.

There are the following three commands to perform logical backup.

• pg_dumpall
  Backs up the contents of the entire database cluster in SQL format. Run the psql command to restore the database cluster to the point in time when the backup was taken.
• pg_dump
  Backs up the specified database contents in SQL format. The resulting backup can be used to restore the database in a script file run via the psql command or executing the pg_restore command, which will use the backup in archive file format.
• COPY
  Backs up the data in the specified table in its own text format, binary format, or CSV format. Use the command COPY to recover the data to the latest backup point.

The table below summarizes the 3 types of logical backup

Remember that you can select the backup method depending on the data range you need - the entire database cluster, a specific database, or a table. Also, you can specify the contents of the backup with options, such as just the table definition, only the data in the table, or both the table definition and the data.

Compared to physical backup, the backup data size is smaller, but the processing time is longer. Since this method only uses backup data, you can only restore to the time when backup is performed.

Logical backup for recovery

As an example, we assume an employee information system built with a PostgreSQL database to explain the backup and recovery of the PostgreSQL database.

Based on this specification, we will go through a sample operation.

Back up and recover the entire database cluster

The following is an example of using the pg_dumpall command to back up the entire database cluster and then using the psql command for recovery.

Assumed business requirements

The current instance (inst1) of server A needs to be migrated to a new instance (inst2) on the same server.

1. In the current instance (inst1), use the pg_dumpall command to back up the database cluster.
2. Create a new instance (inst2) and use the psql command to migrate the backup data of the current instance (inst1).
   At this time, multiple instances will exist on the same server, so the port number set to the new instance (inst2) needs to be different from the current instance (inst1).

The image below illustrates this example.

Detailed procedure

Follow the steps below to achieve the above.

1. Display the database list of the current instance (inst1) and display the contents of the table.
2. Back up the database cluster of the current instance (inst1) using the pg_dumpall command.
3. Check the contents of the generated backup file.
4. Create a new instance (inst2).
• Create a directory for storing data
• Create a directory for storing backup data
• Create a directory for storing transaction logs
• Create a new instance (inst2) database cluster with the initdb command
5. Edit the port number in the configuration file.
6. Start the new instance (inst2).
7. Migrate the backup files to the new instance (inst2) using the psql command.
8. Check if the database contents of the new instance (inst2) are correct.

Back up and recover specific tables

The following is an example of backing up a specific table using the pg_dump command and recovering using the psql command.

Assumed business requirements

In this example, let's assume that the department code (code) of the employee table 1 (jtbl1) needs to be changed due to an organizational change.

1. Back up the current employee table 1 (jtbl1) using the pg_dump command.
2. Suppose you have changed the code in employee table 1 (jtbl1) to the wrong values by using an unintended file for the update.
3. Use the psql command to restore the values to the time of backup.
4. Correctly change the code of employee table 1 (jtbl1).

Detailed procedure

To address the requirements like above, you can follow the steps below.

1. Display employee table 1 (jtbl1).
2. Back up employee table 1 (jtbl1) using the pg_dump command.
3. Check the contents of the backup file.
4. Suppose you changed the code of employee table 1 (jtbl1) incorrectly and have detected the error.
5. Use the psql command to return to the point when the backup was taken.
6. Check whether the data in employee table 1 (jtbl1) has restored to the backup time.
7. Change the code in employee table 1 (jtbl1) correctly.

Caution

If you specify the -c or --clean option when executing the pg_dump command, the SQL command that deletes database objects will also be output before the SQL command that creates database objects. Carefully consider if this option is necessary.

Backup and recovery using COPY

Take a backup using the SQL command COPY. This command outputs the data stored in a table to a file.

Assumed business requirements

Next, we will create employee table 2 (jtbl2) with the same structure as employee table 1 (jtbl1).

1. Back up the current employee table 1 (jtbl1) by using the SQL command COPY.
2. Create employee table 2 (jtbl2) and use the SQL command COPY to store the backup data.

Detailed procedure

To address the requirements like above, you can follow the steps below.

1. Display employee table 1 (jtbl1).
2. Back up the data of employee table 1 (jtbl1) using COPY.
3. Check the contents of the backup file.
4. Create employee table 2 (jtbl2).
5. Use COPY to store the backup data of employee table 1 (jtbl1) in employee table 2 (jtbl2).
6. Check the data in employee table 2 (jtbl2).

Caution about data encoding issues

In some cases, when data is copied from a table to a file with COPY, the resulting data may not be encoded with the expected character code. This is because copied data is encoded according to the ENCODING option or the current client encoding. If the ENCODING parameter of COPY is omitted, then the client encoding is adopted, which may result in a different character code. To make sure the correct encoding is used, specify the desired encoding in the ENCODING parameter of COPY.

Leverage your business data with a peace of mind by performing the logical backup according to the requirements of your business.