Backing Up and Restoring Databases

Performing backups regularly ensures that you can restore your data or rebuild your LightDB-A Database system if data corruption or a system failure occurs. You can also use backups to migrate data from one LightDB-A Database system to another.

LightDB-A Database supports parallel and non-parallel methods for backing up and restoring databases. Parallel operations scale regardless of the number of segments in your system, because segment hosts each write their data to local disk storage simultaneously. With non-parallel backup and restore operations, the data must be sent over the network from the segments to the coordinator, which writes all of the data to its storage. In addition to restricting I/O to one host, non-parallel backup requires that the coordinator have sufficient local disk storage to store the entire database.

Parallel Backup with gpbackup and gprestore

gpbackup and gprestore are the recommended LightDB-A Database backup and restore utilities. gpbackup utilizes ACCESS SHARE locks at the individual table level, instead of EXCLUSIVE locks on the pg_class catalog table. This enables you to run DML statements during the backup, such as CREATE, ALTER, DROP, and TRUNCATE operations, as long as those operations do not target the current backup set. Backup files created with gpbackup are designed to provide future capabilities for restoring individual database objects along with their dependencies, such as functions and required user-defined datatypes.

gpbackup, gprestore, and related utilities are provided as a separate download, VMware LightDB-A® Backup and Restore. Follow the instructions in the VMware LightDB-A Backup and Restore Documentation to install and use these utilities.

Non-Parallel Backup with pg_dump

The PostgreSQL pg_dump and pg_dumpall non-parallel backup utilities can be used to create a single dump file on the coordinator host that contains all data from all active segments.

The PostgreSQL non-parallel utilities should be used only for special cases. They are much slower than using the LightDB-A backup utilities since all of the data must pass through the coordinator. Additionally, it is often the case that the coordinator host has insufficient disk space to save a backup of an entire distributed LightDB-A database.

The pg_restore utility requires compressed dump files created by pg_dump or pg_dumpall. To perform a non-parallel restore using parallel backup files, you can copy the backup files from each segment host to the coordinator host, and then load them through the coordinator.

Another non-parallel method for backing up LightDB-A Database data is to use the COPY TO SQL command to copy all or a portion of a table out of the database to a delimited text file on the coordinator host.

Parent topic: Managing a LightDB-A System