Including and Excluding Objects Using Wildcards

When specifying objects to back up, restore, or replicate, you can specify groups using wildcards (globs). Further, after you specify a list to include, you can specify another list to exclude. For example, you might include all tables in a schema and then exclude tables matching a given pattern.

You can use wildcards in your vbr .ini file or as vbr command line parameters.

Wildcards

Matching Schemas

Any pattern without a . character represents a schema. For example, the pattern

includeObjects = customer*,s?

matches any schema beginning with the word customer and any schema consisting of s and one letter.

When you back up or restore a schema without referencing any of the tables of that schema, vbr automatically includes all of the tables in that schema. If you include a schema by name alone, you cannot exclude individual tables from that schema. For example, the following is invalid syntax.

; invalid:
includeObjects = VMart
excludeObjects = VMart.?table?

You can exclude objects from an included schema by identifying the schema with the pattern <schemaname>.*. The following example shows a valid way to include a schema and exclude specific tables and patterns.

--include-objects 'VMart.*'
--exclude-objects 'VMart.sales,VMart.*account*'

Matching Tables

Any pattern including the . character represents a table. For example, in a configuration file, the following pattern:

includeObjects = sales.newclients,sales.??

would match any table named newclients belonging to the schema sales, and any table name with two characters belonging to the sales schema.

You can also match all schemas and tables in a database or backup by using the pattern *.*. For example, you could restore all of the tables and schemas in a backup using this command:

--include-Objects '*.*'

Because a vbr parameter is evaluated on the command line, you must enclose the wildcards in single quote marks to prevent Linux from misinterpreting them.

Testing Wildcard Patterns

You can test the results of any pattern by using the --dry-run parameter with a backup or restore command. Commands that include --dry-run do not affect your database. Instead, vbr displays the result of the command without executing it. For more information on --dry-run, refer to the vbr Reference.

Using Wildcards with Backups

You can identify objects to include in your object backup tasks using the includeObjects and excludeObjects parameters in your configuration file. A typical configuration file might include this content:

[Misc]
snapshotName = dbobjects
restorePointLimit = 1
enableFreeSpaceCheck = True
includeObjects = VMart.*,online_sales.*
excludeObjects = *.*temp*

In this example, the backup would include all tables from the VMart and online_sales schemas, while excluding any table containing the string 'temp' in its name belonging to any schema.

After it evaluates included objects, vbr evaluates excluded objects and removes excluded objects from the included set. If, for example, you included schema1.table1 and then excluded schema1.table1, that object would be excluded. If no other objects were included in the task, the task would fail. The same is true for wildcards. If an exclusion pattern removes all included objects, the task fails.

Using Wildcards with Restore

You can identify objects to include in your restore tasks using the --include-objects and --exclude-objects parameters.

As with backups, vbr evaluates excluded objects after it evaluates included objects and removes excluded objects from the included set. If no objects remain, the task fails.

A typical restore command might include this content. (Line wrapped in the documentation for readability, but this is one command.)

vbr -t restore -c verticaconfig --include-objects 'customers.*,sales??' 
    --exclude-objects 'customers.199?,customers.200?'

This example includes the schema customers, minus any tables with names matching 199 and 200 plus one character, as well as all any schema matching 'sales' plus two characters.

Another typical restore command might include this content.

vbr -t restore -c replicateconfig --include-objects '*.transactions,flights.*' 
    --exclude-objects 'flights.DTW*,flights.LAS*,flights.LAX*'

This example includes any table named transactions, regardless of schema, and any tables beginning with DTW, LAS, or LAX belonging to the schema flights. Although these three-letter airport codes are capitalized in the example, vbr is case-insensitive.