Version: KingbaseES V008R006C008B0014

Sys_dump is the logical backup tool for KingbaseES, enabling backup of a single database to various file formats without blocking concurrent user access. Sys_restore is the corresponding tool for restoring data from these backup files. To back up an entire cluster, including roles and tablespaces, use sys_dumpall.

1. Syntax and Parameters

1.1. Common Connection Parameters

These parameters are required for establishing a database connection.

sys_dump -U system -d kingbase -p 54321
sys_restore -U system -d kingbase -p 54321

• -h: Server IP address (defaults to localhost).
• -U: Database username (defaults to current OS user).
• -d: Database name.
• -p: Database port (defaults to 54321).
• -W: Prompt for password (optional).

1.2. Sys_dump Parameters

Common Options:

• -f <filename>: Output to the specified file or directory.
• -F <format>: Selects the output format.
  • -Fp: Output as a plain SQL script (default).
  • -Fc: Output as a custom-format archive (single compressed file, e.g., .dmp).
  • -Fd: Output to a directory (one file per table, suitable for large databases).
  • -Ft: Output as a tar archive (no compression).
• -Z <0-9>: Compression level (0=none, 9=maximum, default is moderate).
• -j <njobs>: Run dump in parallel with specified number of jobs.
• -v: Verbose mode.

Control Options:

• -a: Dump only data, excluding schema.
• -s: Dump only schema (object definitions), excluding data.
• -t <table>: Dump only tables matching the pattern.
• -n <schema>: Dump only objects in the specified schema(s).
• -N <schema>: Exclude the specified schema(s).
• -T <table>: Exclude the specified table(s).
• -c: Include SQL commands to drop objects before creating them.
• -C: Include SQL commands to create the database.
• -b: Include large objects in the dump.
• -B: Exclude large objects from the dump.
• -x: Do not dump privileges (GRANT/REVOKE).
• -O: Do not output commands to set ownership of objects.
• -E <encoding>: Dump data in the specified encoding.
• -S <superuser>: Specify the superuser name for disabling triggers in plain-text format.

1.3. Sys_restore Parameters

Common Options:

• -f <filename>: Input file or directory name (- for stdin).
• -F <format>: Backup format (usually auto-detected). Must be specified if needed.
  • -Fc: For custom-format archives.
  • -Fd: For directory-format archives.
  • -Ft: For tar-format archives.
• -C: Create the database before restoring.
• -c: Clean (drop) database objects before recreating them.
• -v: Verbose mode.
• -l: List the table of contents (TOC) of the archive.

Control Options:

• -a: Restore only data, not schema.
• -s: Restore only schema, not data.
• -t <relation>: Restore named relations (tables, views, etc.).
• -n <schema>: Restore only objects in this schema.
• -N <schema>: Do not restore objects in this schema.
• -P <function>: Restore the named function.
• -T <trigger>: Restore the named trigger.
• -I <index>: Restore the named index.
• -g <source_schema>: Use with -G; restore objects from source_schema.
• -G <target_schema>: Use with -g; restore objects to target_schema.
• -j <njobs>: Restore using multiple parallel jobs.
• -e: Exit on error (default is to continue).
• -E <filename>: Skip COPY rows that cause errors, logging to file.
• -L <filename>: Use the specified TOC file for sorting output.
• -x: Do not restore privileges (GRANT/REVOKE).
• -O: Do not restore object ownership.
• -S <superuser>: Use specified superuser to disable triggers.
• -1: Execute restoration as a single transaction.
• -K <key>: Decryption key for encrypted backups.

2. Backup Procedures

2.1. Exporting to a SQL File

The default operation creates a plain SQL file.

sys_dump -U system -d db1 -p 54321 > backup.sql
sys_dump -U system -d db1 -p 54321 -f backup.sql

2.2. Exporting to a Custom Format Archive

Creates a compressed, single-file archive.

sys_dump -U system -d db1 -p 54321 -Fc -f backup.dmp

2.3. Fast Export (Directory Format)

The -Fd format creates one file per table, enabling parallelism and efficient handling of large databases.

# Standard directory format
sys_dump -U system -d db1 -p 54321 -Fd -f /path/to/backup_dir/
# With parallel jobs
sys_dump -U system -d db1 -p 54321 -Fd -j 10 -f /path/to/backup_dir/

2.4. Selective Table Export

• Export only table t1 (assumed in public schema):

sys_dump -U system -d db1 -p 54321 -t t1 > t1_only.sql

• Export all tables starting with 't' in public schema:

sys_dump -U system -d db1 -p 54321 -t 't*' > t_tables.sql

• Export all objects except tables starting with 't' in public schema:

sys_dump -U system -d db1 -p 54321 -T 't*' > exclude_t.sql

• Parallel export of a specific table:

sys_dump -U system -d db1 -p 54321 -t t1 --parallel-lob-data -j 10 > t1_fast.sql

3. Restoration Procedures

3.1. Restoring to a Specific Database

Sys_restore typically auto-detects the backup format.

sys_restore -U system -d target_db -p 54321 backup.dmp

For directory format:

sys_restore -U system -d target_db -p 54321 /path/to/backup_dir/

Note: Sys_restore cannot restore plain SQL (-Fp) dumps. Use ksql enstead:

ksql -U system -d target_db -p 54321 -f backup.sql

3.2. Restoring with Automatic Database Creation

The -C flag instructs sys_restore to create the database that was originally dumped, not the data base specified by -d. This is suitable for migration scenarios.

Process:

1. Dump the source database (db1).

   sys_dump -U system -d db1 -p 54321 -Fc -f db1_backup.dmp
   

2. Attempt to restore to a different target database (target_db) using -C. This will fail if db1 already exists.

   # This will try to create and restore into 'db1', not 'target_db'.
   sys_restore -U system -d target_db -p 54321 -C db1_backup.dmp
   

3. To succeed, the originally dumped database (db1) must not exist. This command effectively migrates the db1 database.

The -d parameter in the restore command primarily provides connection context; the -C flag overrides the target data base name with the name from the backup archive.