Support for Importing and Exporting Specific Objects

Availability

This feature is available as of MogDB 5.0.8.

Introduction

This feature supports the logical backup tool (gs_dump) and the restore tool (gs_restore) to specify the import and export of packages, functions, procedures, triggers, and types.

Benefits

Enhances the capabilities of logical backup and restore tools, improving the usability of MogDB.

Description

gs_dump supports the export of specified basic objects

New command-line parameters have been added to specify the backup and export of specific objects, enabling the export functionality for packages, functions, procedures, triggers, and types.

You can specify one or more of these five basic objects;
Multiple parameter names can be specified for the same basic object (e.g., --trigger name1 --trigger name2);
Only the specified objects are exported, regardless of the object's dependencies;
For triggers, both the definition and the associated functions that trigger the action must be exported;
The exported backup files can be imported using the gs_restore tool;
It does not affect the settings of existing parameters.

Usage Instructions

-- Specify exporting a trigger
gs_dump -f backup_dir/filename -F p --trigger trigger_name

-- Specify exporting a function
gs_dump -f backup_dir/filename -F p --function function_name(args)

-- Specify exporting a type
gs_dump -f backup_dir/filename -F p --type type_name

-- Specify exporting a package
gs_dump -f backup_dir/filename -F p --package package_name

-- Specify exporting a procedure
gs_dump -f backup_dir/filename -F p --procedure procedure_name(args)

--trigger trigger_name

Specify the trigger for export
--function function_name(args)

Specify the function for export
--type type_name

Specify the type for export
--package package_name

Specify the package for export
--procedure procedure_name(args)

Specify the procedure for export

gs_restore supports the import of specified basic objects

New command-line parameters have been added to specify the backup and import of specific objects, enabling the import functionality for packages, functions, procedures, triggers, and types.

It supports importing custom archive formats, directory archive formats, and tar archive formats;
Only the specified objects are imported, regardless of the object's dependencies;
It supports importing specified types of objects from a full backup;
It supports importing specified objects from an archive file exported with gs_dump;
You can specify one or more of these five basic objects;
Multiple parameter names can be specified for the same basic object (e.g., --trigger name1 --trigger name2);
It does not affect the settings of existing parameters.

Usage Instructions

-- Specify importing a trigger
gs_restore backup/MPPDB_backup.tar -p 8000 -d backupdb -e -T trigger_name
-- or
gs_restore backup/MPPDB_backup.tar -p 8000 -d backupdb -e --trigger trigger_name

-- Specify importing a function
gs_restore backup/MPPDB_backup.tar -p 8000 -d backupdb -e -P function_name(args)
-- or
gs_restore backup/MPPDB_backup.tar -p 8000 -d backupdb -e --function function_name(args)

-- Specify importing a type
gs_restore backup/MPPDB_backup.tar -p 8000 -d backupdb -e --type type_name

-- Specify importing a package
gs_restore backup/MPPDB_backup.tar -p 8000 -d backupdb -e --package package_name

-- Specify importing a procedure
gs_restore backup/MPPDB_backup.tar -p 8000 -d backupdb -e --procedure procedure_name(args)

-T, --trigger trigger_name

Specify the trigger for import
-P, --function function_name(args)

Specify the function for import
--type type_name

Specify the type for import
--package package_name

Specify the package for import
--procedure procedure_name(args)

Specify the procedure for import

Notes

For functions and stored procedures with parameters, the parameter types must be specified.

For example, define a function func(a INTEGER, b INTEGER), the function name should be indicated as: "func(integer, integer)"

To ensure compatibility with other SQL syntax, the database may convert certain parameter types to another type, for example, VARCHAR2 will be converted to character varying, func(a INTEGER, table_name IN VARCHAR2) will be converted to: "func(integer, character varying)". To ensure the correct input of parameter types, you can use the following SQL statement to query the function parameter types in the database:

SELECT p.proname AS function_name,
p.proargtypes AS parameter_types,
pg_catalog.pg_get_function_identity_arguments(p.oid) AS funcargs
FROM PG_PROC AS p
WHERE p.proname  = 'func_gs_dump_0001';

Query results:

function name      |   parameter types   |     funcargs
-------------------+---------------------+---------------------------------
func gs dump 0001  | 1043                | table name character varing

Through the SQL statement, you can check that the parameter type of func_gs_dump_0001 is character varying, so the correct object name is "func_gs_dump_0001(character varying)".

Example

-- Export a trigger named update_time
gs_dump -f backup_dir/db.sql -F p --trigger update_time

-- Import a trigger named update_time
gs_restore backup/MPPDB_backup.tar -p 8000 -d backupdb -e --trigger update_time

gs_dump, gs_restore