wxbackup - tool for backing up a Kognitio database

SYNOPSIS

wxbackup -s server -u user -p password options

DESCRIPTION

wxbackup backs up all or parts of a Kognitio database.

It can be used to back up users and groups, tables, views, schemas, system tables, security classes, plugins, character sets, query queues, external scripts and script environments.

OPTIONS

Connection Options

-s DSN

Hostname, address, or DSN of the server to connect to.

-u user

Kognitio user name to connect as. This can be a non-SYS user, although some metadata can only be read by the SYS user so backups are usually run as SYS.

-p password

Password for the user specified in -u.

Basic Options

-d directory

Destination directory for the backup. This directory is created if it does not exist. If the directory does exist and contains a backup, wxbackup will not overwrite it - it will exit with an error. This option is required for most operations.

-h

Show help.

What to back up

If none of -U, -T, -S, -Y, -C, -L, -H, -Q, -\-external-scripts, -\-script-environments are given, a full backup is performed. Otherwise only those items specified are backed up.

A full backup is equivalent to: -U -T -S -Y -C -L -H -Q -\-script-environments -\-external-scripts

-U user1 user2

Specify a list of users or groups to back up. If no user or group names are given, all users and groups are backed up. Members of the specified groups are also backed up unless -\-nogroupdeps is set.

-T sch1.table1 sch2.table2

Specify a list of tables, views, external scripts or schemas to back up. If no object names are given, all tables are backed up. Names must be given in the form “SCHEMA.TABLE”. If an argument contains no dot, it is assumed to be a schema name and all non-system tables in that schema will be backed up. Tables on which the specified tables depend are automatically added to the backup unless -\-notabdeps is set. If a schema name is given, all users for whom this is the default schema are backed up, unless -\-noschemausers is set. (N.B. this only backs up a schema’s users, not groups. To back up groups, specify them with -U). SQL wildcards (e.g. ‘%’) can be used in the arguments to -T to match multiple objects. Privileges on tables, as with privileges on any objects, are stored as attributes of the object the privileges refer to, and not with the user who has the privileges.

-S

Back up all system tables. Only system tables that can be directly restored (i.e. by simply re-inserting data into the table) have their data backed up.

-Y

Back up non-object-specific metadata. This includes system-wide parameters, privileges on the system that do not correspond to a schema, table, user or any other object, and slab information.

-L

Back up all plugin information. This does not back up the plugin binaries; it only backs up the information about which modules are loaded and the pathnames of the plugin binaries.

-C

Back up all security classes. This is done automatically if any users are backed up.

-H

Back up all character set entries. Most common character sets are present in IPE_CHARACTER_SET anyway, but this is useful if custom characters sets have been defined. Character sets required by tables that are being backed up are backed up automatically without the need for this option. N.B. At present wxrestore does not restore custom character sets.

-Q

Back up all query queues.

-\-external-scripts

Back up all external scripts. Any script environments depended upon by these external scripts will be backed up as well.

-\-script-environments

Back up all external script environments.

-X sch1.table1 sch2.table2

Specify list of schemas, tables or views to exclude from the backup. SQL wildcards (e.g. the ‘%’ character) may be used to match multiple tables. These arguments always override the arguments given to -T, regardless of the order in which they are given on the command line. Note that excluded tables may still be backed up if this is necessary to satisfy dependencies, unless the -\-notabdeps option is used.

-\-no-data sch1.table1 sch2.table2

Specify list of schemas or tables for which the data should not be backed up. The metadata will still be saved. SQL wildcards may be used. If -\-no-data is given without arguments, no table data is backed up.

Other Options

-J numthreads

Specify number of concurrently running backup threads. The default is 10.

-M limit

Only back up data for tables with limit rows or fewer. Table metadata (create text, privileges, etc) will still be backed up. -M 0 does not back up table data at all, and is equivalent to -\-no-data.

-m createonly

wxbackup creates a new manifest in the system’s manifest table with the jobs specified under “What to back up” above, and outputs the ID of the manifest so created. This number can be given to -m later to run the backup.

-m [manifestid]

Use a manifest in the database’s manifest table. This is only really useful if you are running many instances of wxbackup, or if you want wxbackup to use the manifest tables and write diagnostic information for failed jobs to the IPE_BACKUP_MANIFEST_DIAG table. If a numeric argument is given, wxbackup ignores the jobs given to it under “What to back up” above, and uses the manifest with the given ID. If no argument is given, a new manifest is created in the manifest table, and the backup is performed with it. This means giving the -m option without an argument is equivalent to running with -m createonly to get the manifest ID then running with -m manifestid to perform the backup. N.B. some command line options have no effect if an existing manifest ID is specified with -m. For example, the list of tables to back up (see the -T option) is saved along with the manifest, and the setting when the manifest is created will override any setting given when the manifest is run. A warning will be logged if this occurs.

-n

Only print a list of what would be backed up, without performing the backup.

-D

Used in conjunction with -m manifestid, print summary and diagnostic information about manifest manifestid.

-t [tno]

Back up the system as it stood at transaction number tno. N.B. See LIMITATIONS below. If tno is not supplied, wxbackup will take the latest transaction number, and the list of uncommitted transactions at that transaction number, and use that; this is also the default behaviour if -t is not given. To perform ordinary exports from the table without transaction filters, use -t none. Note that if you do this, the backup may be inconsistent if tables were changed during the backup, and you won’t be able to use this backup as part of a chain of incrementals.

-\-uctlist tnolist

Tells wxbackup the list of uncommitted transactions at the point transaction tno (the argument to -t) was opened. tnolist should be a comma-separated list of transaction numbers. These transaction numbers will be considered to have taken place “after” the selected transaction number, and so will not be counted in the backup. You should not normally need to use this option.

-i basedirectory

Perform an incremental backup which is an increment from the backup at basedirectory. This must refer to a backup which refers to the state of the system at a specific transaction number, which is ensured by the -t option or (after 8.1) the -\-expect-incremental or -i option. The -i option implies -t. In an incremental backup, some tables may still be backed up in full; see the LIMITATIONS section below. If this is undesirable, use -\-nofullbackup.

-\-nofullbackup

If doing an incremental backup, then if a table cannot be backed up incrementally, consider this a failure to back up that table. This option has no effect if wxbackup is not doing an incremental backup.

-\-notabdeps

Don’t follow dependencies while backing up tables and views.

-\-nogroupdeps

Don’t automatically back up a group’s members when backing up a group.

-\-noschemausers

Don’t automatically back up those users whose default schema is specified with -T.

-\-noqueuedeps

Don’t automatically back up a user’s query queue when backing up the user.

-\-ignore-reclaim-param

Only useful for servers earlier than version 7.1. If -i is given, do an incremental backup regardless of when the last reclaim was. This only applies to servers earlier than 7.1, which did not respect the last_backup_tno parameter when reclaiming. For those older servers it was impossible to take an incremental of a backup if a reclaim had occurred in between. For such servers, if this option is given and there has been a reclaim since the backup we’re incrementing from, the data in the incremental backup will be silently wrong.

-\-force-row-counts

Record an accurate row count with each table. The row count is used when deciding whether to back up a table if the -M option is given. Without the -\-force-row-counts option, the row count of the table will be estimated.

-\-expect-incremental

Tells wxbackup to set the last_backup_tno parameter on the server, which tells the server not to reclaim any history after that point. If this option is not given, the last_backup_tno parameter is dropped unless -\-leave-backup-param is given.

-\-leave-backup-param

Do not touch the last_backup_tno parameter.

-\-unloader-no-compress

When using the new wxunloader API, which is the default if the server supports it and -\-use-export is not set, don’t unload compressed WCB files. Instead unload ordinary WCB files.

-\-backup-ram-only

Back up data for RAM-only tables. By default, data for RAM-only tables is not backed up, only the metadata is.

-data-dir path

Back up the data files to a different directory. If path begins with hdfs:// then it’s taken as an HDFS path, and the data files are written to that location in HDFS using the hadoop fs -put … command.

MULTI-HEADED BACKUP

Once a manifest has been written to the manifest table using -m createonly, many instances of wxbackup can be started, all working on the same manifest. wxbackup claims some jobs from the manifest table, marks them as “processing”, executes them, marks them as succeeded or failed, and continues until there are no more jobs. The manifest table, IPE_MANIFEST, is used to co-ordinate this.

Each node running the backup should be given the manifest number with the -m option. The wxbackup instances will then use the IPE_MANIFEST table to co-ordinate which jobs get done by which instances, until all the jobs have been attempted.

Each instance of wxbackup gives summary and diagnostic information relating only to the jobs it executed. The -D option, in conjunction with a manifest ID specified with -m, will give counts of jobs that succeeded and failed, and diagnostic information for jobs that failed.

MANIFEST TABLE

If the -m option is given, the IPE_MANIFEST table is used to store details of all the backup jobs. The JOB_STATUS column of this table indicates the state of the job: 1 means the job is awaiting processing, 2 means the job has been claimed for execution or is in progress, 3 means the job completed successfully, and 4 means the job failed. Run wxbackup with the -D option, and the manifest ID supplied to the -m option, to get a summary of the backup manifest.

INCREMENTAL BACKUP

The -i option, which takes as its argument an existing backup directory, performs a backup which is identical to an ordinary backup except that the table data is not backed up in full; for each table, only the changes made since the existing backup are saved. Only table data is incremented in this way; metadata is backed up in full.

Any incremental backup, or any backup which is intended to be incremented from, must be a backup of the system as it stood at one transaction number. In version 8.1 and earlier this is achieved using the -t option. In later versions this is automatic for any backup performed with the -\-expect-incremental or -i option.

It is also required to use the -\-expect-incremental option if an incremental backup is expected to be done from this one. This causes the last_backup_tno parameter to be set to the appropriate transaction number, which inhibits the reclaim of history information after that point. This means a subsequent incremental backup will have all the necessary history information it needs, preventing an incremental backup from reverting to a full backup.

An ordinary backup writes a file, data.wcb, to the appropriate subdirectory in the backup directory. An incremental backup writes two files, inserts.wcb and deletes.wcb, which specify the added and removed rows since the last backup. In each table’s directory, a “transaction” file is written that contains the transaction number of this backup, and an “increment” file is written that contains the location of the base backup and the transaction number of that backup. wxrestore will refuse to restore a table if the base transaction number in the “increment” file does not match the transaction number in the base backup’s “transaction” file.

The location of a previous incremental backup can be supplied to the -i option. Incremental backups may be chained in this fashion with no arbitrary length limit.

LIMITATIONS

Incremental backup

In certain circumstances, it might not be possible to back up a table incrementally. In this case the table will be backed up in full unless the -\-nofullbackup option was given.

If a table has been truncated or dropped and recreated since the backup being incremented from, or if wxbackup cannot ascertain that this has not happened, or if a reconfigure with the full-add option or reconfigure down has been done since the previous backup, or if the previous backup was not a snapshot at one transaction number (i.e. was not run with the -t option or another option which implies it), then an incremental backup of that table will not be done. A full backup will be done instead, unless -\-nofullbackup was given, in which case the job is failed and an error message is given.

If a table is truncated during the backup, after wxbackup gets its transaction number at the beginning of the backup, but before that table actually gets backed up, wxbackup will notice this after making the incremental backup. The incremental backup files will be wrong, so they will be removed and the table will be fully backed up; this will be an empty table. Additionally, because the truncation would have taken place after the backup’s transaction number, the next incremental of that table will be a full backup because the table has been truncated since the previous backup’s transaction number.

Backup at transaction number

If a table is dropped between the time the transaction number started and the time the table is exported, the export will fail. If the table is truncated in this time, data that was in the table before the truncate will not be included in the backup. This is why it is recommended to get the transaction number and do the backup immediately (-t with no argument), to minimise the time period in which a table truncation can cause unexpected behaviour.

The “backup at transaction number tno” feature is less likely to work correctly the further in the past tno is, because truncations of tables cause history information to be lost. If a reclaim or reconfigure was done since transaction number tno, wxbackup detects this and refuses to perform the backup.

Backups at a transaction number can only be performed by the SYS user. Backups performed by a non-SYS user will therefore back up the tables containing the data at the time they were exported, which could result in an inconsistent backup if data on the system changes mid-backup.

EXAMPLES

Back up all system metadata (i.e. all tables, schemas, users, groups, privileges, and so on, but no user data) to a subdirectory of the current directory called ‘backup_here’:

wxbackup -s dsn -u user -p password -M 0 -d ./backup_here

Back up a schema called ‘SCHEMA_TEST’ and all its dependent objects (tables, users, privileges … ) and all the user data:

wxbackup -s dsn -u user -p password -U -T SCHEMA_TEST -d ./backup_here

Back up the whole system as it stood at one transaction number, which is obtained automatically:

wxbackup -s dsn -u user -p password -t -d ./backup_here

Make a full backup on which you intend to make an incremental backup at a later date:

wxbackup -s dsn -u user -p password -t -\-expect-incremental -d /path/to/base

Make an incremental backup from the backup at /path/to/base:

wxbackup -s dsn -u user -p password -t -\-expect-incremental -d /path/to/inc1 -i /path/to/base

Make another incremental backup from that one, but we aren’t going to want to create another increment from this so we should unrestrict reclaims:

wxbackup -s dsn -u user -p password -t -d /path/to/inc2 -i /path/to/inc1

FILES

odbc.ini

Used by ODBC to resolve DSNs to hostnames and to set ODBC options.

EXIT STATUS

wxbackup returns 0 if all its jobs succeeded, 1 if any of its jobs failed, 2 if the only failures were due to tables being truncated after the backup started, or 99 if the backup could not start because of a fatal error such as an invalid command line argument or failure to connect to the server.

BUGS

A multi-headed incremental backup can perform unnecessary full backups of tables if the backup is spread across several locations. It is recommended to have all wxbackup processes in a multi-headed backup write to the same directory (e.g. using NFS) anyway.

AUTHOR

Kognitio Limited