Restoring backups

• 1: Restoring a database from a full backup
• 2: Restoring a database to an alternate cluster
• 3: Restoring all objects from an object-level backup
• 4: Restoring individual objects
• 5: Restoring objects to an alternate cluster
• 6: Restoring hard-link local backups
• 7: Ownership of restored objects

$ vbr --task restore --config-file config-file

ERROR 2858:  Could not find function definition

1 - Restoring a database from a full backup

You can restore a full database backup to the database that was backed up, or to an alternate cluster with the same architecture. One reason to restore to an alternate cluster is to set up a test cluster to investigate a problem in your production cluster.

To restore a full database backup, you must verify that:

• Database is DOWN. You cannot restore a full backup when the database is running.

• All backup hosts are available.

• Backup directory exists and contains backups of the data to restore.

• Cluster to which you are restoring the backup has:

  • Same number of nodes as used to create the backup (Enterprise Mode), or at least as many nodes as the primary subclustes (Eon Mode)

  • Same architecture as the one used to create the backup

  • Identical node names

• Target database already exists on the cluster where you are restoring data.

  • Database can be completely empty, without any data or schema.

  • Database name must match the name in the backup

  • All node names in the database must match the names of the nodes in the configuration file.

• The user performing the restore is the database administrator.

• If you are restoring an Eon Mode database, you have met the Eon Mode database requirements.

You can use only a full database backup to restore a complete database. If you have saved multiple backup archives, you can restore from either the last backup or a specific archive.

Restoring from a full database backup injects the OIDs from each backup into the restored catalog of the full database backup. The catalog also receives all archives. Additionally, the OID generator and current epoch are set to the current epoch.

You can also restore a full backup to a different database than the one you backed up. See Restoring a database to an alternate cluster.

Restoring the most recent backup

Usually, when a node or cluster is DOWN, you want to return the cluster to its most-recent state. Doing so requires restoring a full database backup. You can restore any full database backup from the archive by identifying the name in the configuration file.

To restore from the most recent backup, use the vbr restore task with the configuration file. If your password configuration file does not contain the database superuser password, vbr prompts you to enter it.

The following example shows how you can use the db.ini configuration file for restoration:

> vbr --task restore --config-file db.ini
Copying...
1871652633 out of 1871652633, 100%
All child processes terminated successfully.
restore done!

Restoring an archive

If you saved multiple backups, you can specify an archive to restore. To list the archives that exist to choose one to restore, use the vbr --listbackup task, with a specific configuration file. See Viewing backups.

To restore from an archive, add the --archive parameter to the command line. The value is the date_timestamp suffix of the directory name that identifies the archive to restore. For example:

$ vbr --task restore --config-file fullbak.ini --archive=20121111_205841

The --archive parameter identifies the archive created on 11-11-2012 (_archive20121111), at time 205841 (20:58:41). You need specify only the _archive suffix, because the configuration file identifies the backup name of the subdirectory, and the OID identifier indicates the backup is an archive.

Restore failures in Eon Mode

When a restore operation fails, vbr can leave extra files in the communal storage location. If you use communal storage in the cloud, those extra files cost you money. To remove them, restart the database and call CLEAN_COMMUNAL_STORAGE with an argument of true.

2 - Restoring a database to an alternate cluster

Vertica supports restoring a full backup to an alternate cluster.

Requirements

The process is similar to the process for Restoring a database from a full backup, with the following additional requirements.

The destination database must:

• Be DOWN.

• Share the same name as the source database.

• Have the same number of nodes as the source database.

• Have the same names as the source nodes.

• Use the same catalog directory location as the source database.

• Use the same port numbers as the source database.

Procedure

1. Copy the vbr configuration file that you used to create the backup to any node on the destination cluster.

2. If you are using a stored password, copy the password configuration file to the same location as the vbr configuration file.

3. From the destination node, issue a vbr restore command, such as:

   $ vbr -t restore -c full.ini
   

4. After the restore has completed, start the restored database.

3 - Restoring all objects from an object-level backup

To restore everything in an object-level backup to the database from which it was taken, use the vbr restore task with the configuration file you used to create the backup, as in the following example:

$ vbr --task restore --config-file MySchema.ini
Copying...
1871652633 out of 1871652633, 100%
All child processes terminated successfully.
restore done!

The database must be UP.

You can specify how Vertica reacts to duplicate objects by setting the objectRestoreMode parameter in the configuration file.

Object-level backup and restore are not supported for HDFS storage locations.

Restoring objects to a changed cluster

Unlike restoring from a full database backup, vbr supports restoring object-level backups after adding nodes to the cluster. Any nodes that were not in the cluster when you created the object-level backup do not participate in the restore. You can rebalance your cluster after the restore to distribute data among the new nodes.

You cannot restore an object-level backup after removing nodes, altering node names, or changing IP addresses. Trying to restore an object-level backup after such changes causes vbr to fail and display this message:

Preparing...
Topology changed after backup; cannot restore.
restore failed!

Projection epoch after restore

All object-level backup and restore events are treated as DDL events. If a table does not participate in an object-level backup, possibly because a node is down, restoring the backup affects the projection in the following ways:

• Its epoch is reset to 0.

• It must recover any data that it does not have by comparing epochs and other recovery procedures.

Catalog locks during restore

As with other databases, Vertica transactions follow strict locking protocols to maintain data integrity.

When restoring an object-level backup into a cluster that is UP, vbr begins by copying data and managing storage containers. If necessary, vbr splits the containers. This process does not require any database locks.

After completing data-copying tasks, vbr first requires a table object lock (O-lock) and then a global catalog lock (GCLX).

In some circumstances, other database operations, such as DML statements, are in progress when the process attempts to get an O-lock on the table. In such cases, vbr is blocked from progress until the DML statement completes and releases the lock. After securing an O-lock first, and then a GCLX lock, vbr blocks other operations that require a lock on the same table.

While vbr holds its locks, concurrent table modifications are blocked. Database system operations, such as the Tuple Mover (TM) transferring data from memory to disk, are canceled to permit the object-level restore to complete.

Catalog restore events

Each object-level backup includes a section of the database catalog, or a snippet. A snippet contains the selected objects, their dependent objects, and principal objects. A catalog snippet is similar in structure to the database catalog but consists of a subset representing the object information. Objects being restored can be read from the catalog snippet and used to update both global and local catalogs.

Each object from a restored backup is updated in the catalog. If the object no longer exists, vbr drops the object from the catalog. Any dependent objects that are not in the backup are also dropped from the catalog.

vbr uses existing dependency verification methods to check the catalog and adds a restore event to the catalog for each restored table. That event also includes the epoch at which the event occurred. If a node misses the restore table event, it recovers projections anchored on the given table.

Reverting object DDL changes

If you restore the database to an epoch that precedes changes to an object's DDL, the restore operation reverts the object to its earlier definition. For example, if you change a table column's data type from CHAR(8) to CHAR(16) in epoch 10, and then restore the database from epoch 5, the column reverts to CHAR(8) data type.

Restoring objects to a higher Vertica version

Vertica supports restoration to a database that is no more than one minor version higher than the current database version. For example, you can restore objects from a 12.0.x database to a 12.1.x database.

If restored objects require a UDx library that is not present in the later-version database, Vertica displays the following error:

ERROR 2858:  Could not find function definition

You can resolve this issue by installing compatible libraries in the target database.

Catalog size limitations

Object-level restores can fail if your catalog size is greater than five percent of the total memory available in the node performing the restore. In this situation, Vertica recommends restoring individual objects from the backup. For more information, refer to Restoring individual objects.

See also

• Failure recovery
• Transactions

4 - Restoring individual objects

You can use vbr to restore individual tables and schemas from a full or object-level backup: qualify the restore task with --restore-objects, and specify the objects to restore as a comma-delimited list:

$ vbr --task restore --config-file=filename --restore-objects='objectname[,...]' [--archive=archive-id] [--target-namespace=namespace-name]

The following requirements and restrictions apply:

• The database must be running, and nodes must be UP.

• Tables must include their schema names.

• Do not embed spaces before or after comma delimiters of the --restore-objects list; otherwise, vbr interprets the space as part of the object name.

• Object-level restore is not supported for HDFS storage locations. To restore an HDFS storage location you must do a full restore.

If the schema has a disk quota and restoring the table would exceed the quota, the operation fails.

By default, --restore-objects restores the specified objects from the most recent backup. You can restore from an earlier backup with the --archive parameter.

The --target-namespace parameter is only valid for Eon Mode databases with multiple namespaces. The parameter specifies the namespace in the target cluster to which objects are restored. For more information, see Eon Mode database requirements.

The following example uses the db.ini configuration file, which includes the database administrator's password:

> vbr --task restore --config-file=db.ini --restore-objects=salesschema,public.sales_table,public.customer_info
Preparing...
Found Database port:  5433
Copying...
[==================================================] 100%
All child processes terminated successfully.
All extract object child processes terminated successfully.
Copying...
[==================================================] 100%
All child processes terminated successfully.
restore done!

Object dependencies

When you restore an object, Vertica does not always restore dependent objects. For example, if you restore a schema containing views, Vertica does not automatically restore the tables of those views. One exception applies: if database tables are linked through foreign keys, you must restore them together, unless drop_foreign_constraints is set in the vbr configuration file to true.

Duplicate objects

You can specify how restore operations handle duplicate objects by configuring objectRestoreMode. By default, it is set to createOrReplace, so if a duplicate object exists, the restore operation overwrites it with the archived version.

Interactions with data loaders

When doing a restore with objectRestoreMode set to coexist, vbr creates new data loaders and their corresponding state tables, but does not change the table names in the loader COPY clauses. After the restore, you can use ALTER DATA LOADER to update the COPY statement in the restored data loader to use the new table name.

Eon Mode considerations

Restoring objects to an Eon Mode database can leave unneeded files in cloud storage. These files have no effect on database performance or data integrity. However, they can incur extra cloud storage expenses. To remove these files, restart the database and call CLEAN_COMMUNAL_STORAGE with an argument of true.

See also

• Monitoring recovery
• Viewing backups
• Restoring a database from a full backup
• Restoring all objects from an object-level backup
• Ownership of restored objects
• Including and excluding objects

5 - Restoring objects to an alternate cluster

You can use the restore task to copy objects from one database to another. You might do this to "promote" tables from a development environment to a production environment, for example. All restrictions described in Restoring individual objects apply when restoring to an alternate cluster.

To restore to an alternate database, you must make changes to a copy of the configuration file that was used to create the backup. The changes are in the [Mapping] and [NodeMapping] sections. Essentially, you create a configuration file for the restore operation that looks to vbr like a backup of the target database, but it actually describes the backup from the source database. See Restore object from backup to an alternate cluster for an example configuration file.

The following example uses two databases, named source and target. The source database contains a table named sales. The following source_snapshot.ini configuration file is used to back up the source database:

[Misc]
snapshotName = source_snapshot
restorePointLimit = 2
objectRestoreMode = createOrReplace

[Database]
dbName = source
dbUser = dbadmin
dbPromptForPassword = True

[Transmission]

[Mapping]
v_source_node0001 = 192.168.50.168:/home/dbadmin/backups/

The target_snapshot.ini file starts as a copy of source_snapshot.ini. Because the [Mapping] section describes the database that vbr operates on, we must change the node names to point to the target nodes. We must also add the [NodeMapping] section and change the database name:

[Misc]
snapshotName = source_snapshot
restorePointLimit = 2
objectRestoreMode = createOrReplace

[Database]
dbName = target
dbUser = dbadmin
dbPromptForPassword = True

[Transmission]

[Mapping]
v_target_node0001 = 192.168.50.151:/home/dbadmin/backups/
[NodeMapping]
v_source_node0001 = v_target_node0001

As far as vbr is concerned, we are restoring objects from a backup of the target database. In reality, we are restoring from the source database.

The following command restores the sales table from the source backup into the target database:

$ vbr --task restore --config-file target_snapshot.ini --restore-objects sales
Starting object restore of database target.
Participating nodes: v_target_node0001.
Objects to restore: sales.
Enter vertica password:
Restoring from restore point: source_snapshot_20160204_191920
Loading snapshot catalog from backup.
Extracting objects from catalog.
Syncing data from backup to cluster nodes.
[==================================================] 100%
Finalizing restore.
Restore complete!

6 - Restoring hard-link local backups

You restore from hard-link local backups the same way that you restore from full backups, using the restore task. If you used hard-link local backups to back up to external media, you need to take some additional steps.

Transferring backups to and from remote storage

When a full hard-link local backup exists, you can transfer the backup to other storage media, such as tape or a locally-mounted NFS directory. Transferring hard-link local backups to other storage media may copy the data files associated with the hard file links.

You can use a different directory when you return the backup files to the hard-link local backup host. However, you must also change the backupDir parameter value in the configuration file before restoring the backup.

Complete the following steps to restore hard-link local backups from external media:

1. If the original backup directory no longer exists on one or more local backup host nodes, re-create the directory.
   
   The directory structure into which you restore hard-link backup files must be identical to what existed when the backup was created. For example, if you created hard-link local backups at the following backup directory, you can then re-create that directory structure:

   /home/dbadmin/backups/localbak
   

2. Copy the backup files to their original backup directory, as specified for each node in the configuration file. For more information, refer to [Mapping].

3. Restore the backup, using one of three options:

   1. To restore the latest version of the backup, move the backup files to the following directory:

      /home/dbadmin/backups/localbak/node_name/snapshotname
      

   2. To restore a different backup version, move the backup files to this directory:

      /home/dbadmin/backups/localbak/node_name/snapshotname_archivedate_timestamp
      

4. When the backup files are returned to their original backup directory, use the original configuration file to invoke vbr. Verify that the configuration file specifies hardLinkLocal = true. Then restore the backup as follows:

   $ vbr --task restore --config-file localbak.ini
   

7 - Ownership of restored objects

For a full restore, objects have the owners that they had in the backed-up database.

When performing an object restore, Vertica inserts data into existing database objects. By default, the restore does not affect the ownership, storage policies, or permissions of the restored objects. However, if the restored object does not already exist, Vertica re-creates it. In this situation, the restored object is owned by the user performing the restore. Vertica does not restore dependent grants, roles, or client authentications with restored objects.

If the storage policies of a restored object are not valid, vbr applies the default storage policy. Restored storage policies can become invalid due to HDFS storage locations, table incompatibility, and unavailable min-max values at restore time.

Sometimes, Vertica encounters a catalog object that it does not need to restore. When this situation occurs, Vertica generates a warning message for that object and the restore continues.

Examples

Suppose you have a full backup, including Schema1, owned by the user Alice. Schema1 contains Table1, owned by Bob, who eventually passes ownership to Chris. The user dbadmin performs the restore. The following scenarios might occur that affect ownership of these objects.

Scenario 1:

Schema1.Table1 has been dropped at some point since the backup was created. When dbadmin performs the restore, Vertica re-creates Schema1.Table1. As the user performing the restore, dbadmin takes ownership of Schema1.Table1. Because Schema1 still exists, Alice retains ownership of the schema.

Scenario 2:

Schema1 is dropped, along with all contained objects. When dbadmin performs the restore, Vertica re-creates the schema and all contained objects. dbadmin takes ownership of Schema1 and Schema1.Table1.

Scenario 3:

Schema1 and Schema1.Table1 both exist in the current database. When dbadmin rolls back to an earlier backup, the ownership of the objects remains unchanged. Alice owns Schema1, and Bob owns Schema1.Table1.

Scenario 4:

Schema1.Table1 exists and dbadmin wants to roll back to an earlier version. In the time since the backup was made, ownership of Schema1.Table1 has changed to Chris. When dbadmin restores Schema1.Table1, Alice remains owner of Schema1 and Chris remains owner of Schema1.Table1. The restore does not revert ownership of Schema1.Table1 from Chris to Bob.