KingbaseES logical backup and restore tool provides online backup and restore function at database object level. Backup objects include:

database
pattern
surface
view
constraint
jurisdiction
trigger
function
sequence

The output format of logical backup includes:

Binary
SQL script

In addition, for table data, it also supports the export of multiple formats:

CSV
JSON
HTML

Thus, users can flexibly adjust and implement the data backup plan. When using the backup and recovery function of KingbaseES, it should be noted that it is not recommended to backup and restore between the database with the "case insensitive compatible heterogeneous database" feature and the database without the "case insensitive compatible heterogeneous database" feature.

Using sys_dump for backup

sys_ Common dump parameters are as follows

-U username --username=username #Which user do you want to connect as.
-f file --file=file #Transfer the output to the specified file. For files based on output format, this parameter can be ignored, in which case standard output will be used. However, this parameter must be given for the directory output format. What is specified in the directory output format is a directory rather than a file. In this case, the directory is managed by sys_dump is created and does not need to exist before. Multiple output files are not supported.
-F format --format=format #Only one of the output formats can be selected for a backup. format can be one of the following:
	p plain #Output a plain text SQL script file (default).
	c custom #Output one suitable as sys_restore the custom format archive entered. Together with the catalog output format, this is the most flexible output format, which allows manual selection and sorting of backed up items during recovery. This format is also compressed by default.
	d directory #Output a suitable as sys_restore the archive of the directory format entered. This will create a directory with a file for each table and object being backed up, plus a directory file in a sys format_ Restore describes the backed up object in a machine-readable format that can be read. A directory format archive can be manipulated by standard Unix tools. For example, files in an uncompressed archive can be compressed by gzip tool. This format is compressed by default and also supports parallel backups.
	t tar Output one suitable for input to sys_restore Medium tar Format archiving. tar The format can be compatible with the directory format. Extract one tar Format archiving will produce a legal directory format archiving. however, tar The format does not support compression. Also, in use tar When formatting, the relative order of table data items cannot be changed during recovery.
-d dbname --dbname=dbname #Specify the name of the database to connect to. This is equivalent to specifying dbname as the first non option parameter on the command line. If this parameter contains a = sign or starts with a legal URI prefix (kingbase: / / or kingbase: / /), it will be treated as a conninfo string.
-h host --host=host #Specifies the hostname of the machine on which the server is running. If the value starts with a slash, it is used as a directory for Unix domain sockets.
-p port --port=port #The file name of the local socket or Unix server that is listening for the TCP connection. The default value compiled in the program is used by default.
-E encoding --encoding=encoding #Creates a backup with the specified character set encoding. By default, the backup is created with the encoding of the database.
-n schema --schema=schema #Only the schema that matches the schema is backed up, which selects the schema itself and all the objects it contains. When this option is not specified, all non system modes in the target database will be backed up. Multiple modes can be selected by writing multiple - n switches. In addition, the schema parameter can be interpreted as a Pattern written according to the same rules used by the ksql's \d command (see Pattern), so that multiple patterns can also be selected by writing wildcards in the Pattern. When using wildcards, if you need to prevent the shell from expanding wildcards, you need to carefully reference this Pattern. See the example.
-N schema --exclude-schema=schema #No schema matching schema schema is backed up. The pattern is interpreted according to the same rules used by - n- N can be given multiple times to exclude patterns that match any one of several patterns. When both - N and - n are given, the behavior is to back up only the patterns that match at least one - N switch but do not match the - N switch. If there is only - N but no - N, the pattern matching - n will be excluded from a normal backup.
-s --schema-only #Only object definitions (schemas) are backed up, not data. This option is the inverse of -- data only. It is similar to specifying -- Section = pre data -- Section = post data, but it is different for historical reasons. (don't confuse this option with the -- schema option, which has different meanings in the use of "schema"). To exclude table data for a subset of tables in the database, see -- exclude table data.
-t table --table=table #Only the tables whose names match table are backed up. "Table" can also include views, materialized views, sequences and external tables. Multiple tables can be selected by writing multiple - t switches. In addition, the table parameter can be interpreted as a Pattern written according to the same rules used by the ksql's \d command (see Pattern), so that multiple tables can also be selected by writing wildcards in the Pattern. When using wildcards, if you need to prevent the shell from expanding wildcards, you need to carefully reference this Pattern. See the example. When - t is used, the - N and - N switches have no effect because the tables selected by - t will be backed up regardless of those switches, and non table objects will not be backed up.
-T table --exclude-table=table #Do not back up any tables that match the table pattern. This pattern is interpreted according to the same rules used by - T- T can be given multiple times to exclude patterns that match any of several patterns. When both - t and - t are given, the behavior is to back up only tables that match at least one - t switch but do not match the - t switch. If there is only - t but no - t, the table matching - t will be excluded from a normal backup.

1. -The behavior of the T switch is not fully forward compatible with KingbaseES versions before V8. Previously, writing - t tab would back up all tables named tab, but now it only backs up the one visible in your default search path. The old behavior can be written as * tab’. Also, you must write something like - t Sch Tab to select a table in a specific pattern instead of using the old idiom - n sch -t tab.
2. If - t is assigned, - n is ignored. If - t, - n are not specified, all objects are backed up.
3. In case sensitive databases, when backing up lowercase table name / schema name objects or there are special characters in the table name, the table name / schema name should be enclosed in single quotation marks. For example, table t can be written as "t" or "t", but table "t" should be written as' "t"; The table "T.T" with special characters shall be written as "T.T"; Table "sc" "T.T" shall be written as' sc '. " “t.t”’.

For more detailed parameters, please refer to the official documents: sys_dump backup tool

Backup binaries:

#Backup TEST library 
/home/kingbase/ES/V8/Server/bin/sys_dump -U username -F c -f /home/kingbase/test.dmp TEST
#Backup test mode under test library
/home/kingbase/ES/V8/Server/bin/sys_dump -U username -F c -n test -f /home/kingbase/test.dmp TEST
#Back up the test table of the test mode under the test library
/home/kingbase/ES/V8/Server/bin/sys_dump -U username -F c -t test.test -f /home/kingbase/test.dmp TEST

Backup sql files:

#Backup TEST library 
/home/kingbase/ES/V8/Server/bin/sys_dump -U username -F p -f /home/kingbase/test.sql TEST
#Backup the test mode under the test library??
/home/kingbase/ES/V8/Server/bin/sys_dump -U username -F p -n test -f /home/kingbase/test.sql TEST
#Back up the test table of the test mode under the test library?
/home/kingbase/ES/V8/Server/bin/sys_dump -U username -F p -t test.test -f /home/kingbase/test.sql TEST

Database restore

Restore DMP (binary) files.

#Restore TEST library 
/home/kingbase/ES/V8/Server/bin/sys_restore -U username -d TEST /home/kingbase/test.dmp
#Restore test mode in test library
/home/kingbase/ES/V8/Server/bin/sys_restore -U username -n test -d TEST /home/kingbase/test.dmp
#Restore the test table of the test mode in the test library
/home/kingbase/ES/V8/Server/bin/sys_restore -U username -t test.test -d TEST /home/kingbase/test.dmp

Restore SQL files

#Restore TEST library 
/home/kingbase/ES/V8/Server/bin/sys_restore -U username -d TEST /home/kingbase/test.sql
#Restore test mode in test library
/home/kingbase/ES/V8/Server/bin/sys_restore -U username -n test -d TEST /home/kingbase/test.sql
#Restore the test table of the test mode in the test library
/home/kingbase/ES/V8/Server/bin/sys_restore -U username -t test.test -d TEST /home/kingbase/test.sql

Restore sql files (available under R3)

ksql -U username -f /home/kingbase/test.sql -d TEST