9.2 EDB Clone Schema Functions

Table of Contents Previous Next


9 EDB Clone Schema : 9.2 EDB Clone Schema Functions

The EDB Clone Schema functions are created in the edb_util schema when the parallel_clone and edb_cloneschema extensions are installed.
You are connected to the target or local database as the database superuser defined in the CREATE USER MAPPING command for the foreign server of the target or local database. See Section 9.1.4.1 for information on the user mapping for the localcopyschema or localcopyschema_nb function. See Section 9.1.4.2 for information on the user mapping for the remotecopyschema or remotecopyschema_nb function.
The edb_util schema is in the search path, or the cloning function is to be invoked with the edb_util prefix.
When using the remote copy functions, if the on_tblspace parameter is to be set to true, then the target database cluster contains all tablespaces that are referenced by objects in the source schema, otherwise creation of the DDL statements for those database objects will fail in the target schema. This causes a failure of the cloning process.
When using the remote copy functions, if the copy_acls parameter is to be set to true, then all roles that have GRANT privileges on objects in the source schema exist in the target database cluster, otherwise granting of privileges to those roles will fail in the target schema. This causes a failure of the cloning process.
The localcopyschema function copies a schema and its database objects within a local database specified within the source_fdw foreign server from the source schema to the specified target schema within the same database.
source_fdw TEXT,
source_schema TEXT,
target_schema TEXT,
log_filename TEXT
[, on_tblspace BOOLEAN
[, verbose_on BOOLEAN
[, copy_acls BOOLEAN
[, worker_count INTEGER ]]]]
A BOOLEAN value is returned by the function. If the function succeeds, then true is returned. If the function fails, then false is returned.
The source_fdw, source_schema, target_schema, and log_filename are required parameters while all other parameters are optional.
Name of the foreign server managed by the postgres_fdw foreign data wrapper from which database objects are to be cloned.
BOOLEAN value to specify whether or not database objects are to be created within their tablespaces. If false is specified, then the TABLESPACE clause is not included in the applicable CREATE DDL statement when added to the target schema. If true is specified, then the TABLESPACE clause is included in the CREATE DDL statement when added to the target schema. If the on_tblspace parameter is omitted, the default value is false.
BOOLEAN value to specify whether or not the DDLs are to be printed in log_filename when creating objects in the target schema. If false is specified, then DDLs are not printed. If true is specified, then DDLs are printed. If omitted, the default value is false.
BOOLEAN value to specify whether or not the access control list (ACL) is to be included while creating objects in the target schema. The access control list is the set of GRANT privilege statements. If false is specified, then the access control list is not included for the target schema. If true is specified, then the access control list is included for the target schema. If the copy_acls parameter is omitted, the default value is false.
The following example shows the cloning of schema edb containing a set of database objects to target schema edbcopy, both within database edb as defined by local_server.
Foreign server (local_server) and user mapping (see Section 9.1.4.1) with the information of the preceding bullet points
Database superuser to invoke localcopyschema: enterprisedb
The localcopyschema_nb function copies a schema and its database objects within a local database specified within the source_fdw foreign server from the source schema to the specified target schema within the same database, but in a non-blocking manner as a job submitted to pgAgent.
source_fdw TEXT,
source TEXT,
target TEXT,
log_filename TEXT
[, on_tblspace BOOLEAN
[, verbose_on BOOLEAN
[, copy_acls BOOLEAN
[, worker_count INTEGER ]]]]
An INTEGER value job ID is returned by the function for the job submitted to pgAgent. If the function fails, then null is returned.
The source_fdw, source, target, and log_filename are required parameters while all other parameters are optional.
After completion of the pgAgent job, remove the job with the remove_log_file_and_job function (see Section 9.2.6).
Name of the foreign server managed by the postgres_fdw foreign data wrapper from which database objects are to be cloned.
BOOLEAN value to specify whether or not database objects are to be created within their tablespaces. If false is specified, then the TABLESPACE clause is not included in the applicable CREATE DDL statement when added to the target schema. If true is specified, then the TABLESPACE clause is included in the CREATE DDL statement when added to the target schema. If the on_tblspace parameter is omitted, the default value is false.
BOOLEAN value to specify whether or not the DDLs are to be printed in log_filename when creating objects in the target schema. If false is specified, then DDLs are not printed. If true is specified, then DDLs are printed. If omitted, the default value is false.
BOOLEAN value to specify whether or not the access control list (ACL) is to be included while creating objects in the target schema. The access control list is the set of GRANT privilege statements. If false is specified, then the access control list is not included for the target schema. If true is specified, then the access control list is included for the target schema. If the copy_acls parameter is omitted, the default value is false.
The same cloning operation is performed as the example in Section 9.2.1, but using the non-blocking function localcopyschema_nb.
If pgAgent is not running, it can be started as shown by the following. The pgagent program file is located in the bin subdirectory of the Advanced Server installation directory.
Note: the pgagent -l 2 option starts pgAgent in DEBUG mode, which logs continuous debugging information into the log file specified with the -s option. Use a lower value for the -l option, or omit it entirely to record less information.
The localcopyschema_nb function returns the job ID shown as 4 in the example.
The remotecopyschema function copies a schema and its database objects from a source schema in the remote source database specified within the source_fdw foreign server to a target schema in the local target database specified within the target_fdw foreign server.
source_fdw TEXT,
target_fdw TEXT,
source_schema TEXT,
target_schema TEXT,
log_filename TEXT
[, on_tblspace BOOLEAN
[, verbose_on BOOLEAN
[, copy_acls BOOLEAN
[, worker_count INTEGER ]]]]
A BOOLEAN value is returned by the function. If the function succeeds, then true is returned. If the function fails, then false is returned.
The source_fdw, target_fdw, source_schema, target_schema, and log_filename are required parameters while all other parameters are optional.
Name of the foreign server managed by the postgres_fdw foreign data wrapper from which database objects are to be cloned.
Name of the foreign server managed by the postgres_fdw foreign data wrapper to which database objects are to be cloned.
BOOLEAN value to specify whether or not database objects are to be created within their tablespaces. If false is specified, then the TABLESPACE clause is not included in the applicable CREATE DDL statement when added to the target schema. If true is specified, then the TABLESPACE clause is included in the CREATE DDL statement when added to the target schema. If the on_tblspace parameter is omitted, the default value is false.
Note: If true is specified and a database object has a TABLESPACE clause, but that tablespace does not exist in the target database cluster, then the cloning function fails.
BOOLEAN value to specify whether or not the DDLs are to be printed in log_filename when creating objects in the target schema. If false is specified, then DDLs are not printed. If true is specified, then DDLs are printed. If omitted, the default value is false.
BOOLEAN value to specify whether or not the access control list (ACL) is to be included while creating objects in the target schema. The access control list is the set of GRANT privilege statements. If false is specified, then the access control list is not included for the target schema. If true is specified, then the access control list is included for the target schema. If the copy_acls parameter is omitted, the default value is false.
Note: If true is specified and a role with GRANT privilege does not exist in the target database cluster, then the cloning function fails.
The following example shows the cloning of schema srcschema within database srcdb as defined by src_server to target schema tgtschema within database tgtdb as defined by tgt_server.
Foreign server (src_server) and user mapping (see Section 9.1.4.2) with the information of the preceding bullet points
Foreign server (tgt_server) and user mapping (see Section 9.1.4.2) with the information of the preceding bullet points
Database superuser to invoke remotecopyschema: enterprisedb
Before invoking the function, the connection is made by database user enterprisedb to database tgtdb. A worker_count of 4 is specified for this function.
When the remotecopyschema function was invoked, four background workers were specified.
The following portion of the log file clone_rmt_src_tgt shows the status of the parallel data copying operation using four background workers:
Note that the DATA-COPY log message includes two, square bracket numbers (for example, [0][3]).
In case two clone schema jobs are running in parallel, the first log file will have 0 as the job index whereas the second will have 1 as the job index.
The remotecopyschema_nb function copies a schema and its database objects from a source schema in the remote source database specified within the source_fdw foreign server to a target schema in the local target database specified within the target_fdw foreign server, but in a non-blocking manner as a job submitted to pgAgent.
source_fdw TEXT,
target_fdw TEXT,
source TEXT,
target TEXT,
log_filename TEXT
[, on_tblspace BOOLEAN
[, verbose_on BOOLEAN
[, copy_acls BOOLEAN
[, worker_count INTEGER ]]]]
An INTEGER value job ID is returned by the function for the job submitted to pgAgent. If the function fails, then null is returned.
The source_fdw, target_fdw, source, target, and log_filename are required parameters while all other parameters are optional.
After completion of the pgAgent job, remove the job with the remove_log_file_and_job function (see Section 9.2.6).
Name of the foreign server managed by the postgres_fdw foreign data wrapper from which database objects are to be cloned.
Name of the foreign server managed by the postgres_fdw foreign data wrapper to which database objects are to be cloned.
BOOLEAN value to specify whether or not database objects are to be created within their tablespaces. If false is specified, then the TABLESPACE clause is not included in the applicable CREATE DDL statement when added to the target schema. If true is specified, then the TABLESPACE clause is included in the CREATE DDL statement when added to the target schema. If the on_tblspace parameter is omitted, the default value is false.
Note: If true is specified and a database object has a TABLESPACE clause, but that tablespace does not exist in the target database cluster, then the cloning function fails.
BOOLEAN value to specify whether or not the DDLs are to be printed in log_filename when creating objects in the target schema. If false is specified, then DDLs are not printed. If true is specified, then DDLs are printed. If omitted, the default value is false.
BOOLEAN value to specify whether or not the access control list (ACL) is to be included while creating objects in the target schema. The access control list is the set of GRANT privilege statements. If false is specified, then the access control list is not included for the target schema. If true is specified, then the access control list is included for the target schema. If the copy_acls parameter is omitted, the default value is false.
Note: If true is specified and a role with GRANT privilege does not exist in the target database cluster, then the cloning function fails.
The same cloning operation is performed as the example in Section 9.2.3, but using the non-blocking function remotecopyschema_nb.
The following command starts pgAgent on the target database tgtdb. The pgagent program file is located in the bin subdirectory of the Advanced Server installation directory.
The remotecopyschema_nb function returns the job ID shown as 2 in the example.
The process_status_from_log function provides the status of a cloning function from its log file.
log_file TEXT
Displays either STARTING, RUNNING, FINISH, or FAILED.
Displays either INFO, ERROR, or SUCCESSFUL.
Displays either STARTUP, INITIAL, DDL-COLLECTION, PRE-DATA, DATA-COPY, POST-DATA, or FINAL.
The following shows usage of the process_status_from_log function:
The remove_log_file_and_job function performs cleanup tasks by removing the log files created by the schema cloning functions and the jobs created by the non-blocking functions.
{ log_file TEXT |
job_id INTEGER |
log_file TEXT, job_id INTEGER
If only log_file is specified, then the function will only remove the log file.
If only job_id is specified, then the function will only remove the job.

9 EDB Clone Schema : 9.2 EDB Clone Schema Functions

Table of Contents Previous Next