EDB Clone Schema key concepts and limitations v17

Suggest edits

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.

Prerequisites

Verify the following conditions before using an EDB Clone Schema function:

  • You're 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.

  • The edb_util schema is in the search path, or invoke the cloning function with the edb_util prefix.

  • The target schema doesn't exist in the target database.

  • When using the remote copy functions, if the on_tblspace parameter is set to true, then the target database cluster contains all tablespaces that are referenced by objects in the source schema. Otherwise, creating the DDL statements for those database objects fails in the target schema, which causes a failure of the cloning process.

  • When using the remote copy functions, if you set the copy_acls parameter to true, then all roles that have GRANT privileges on objects in the source schema exist in the target database cluster. Otherwise granting privileges to those roles fails in the target schema, which causes a failure of the cloning process.

  • Add the name of the database on which the clone schema is to be installed or used in the postgresql.conf file. For example, if the clone schema is to be installed or used on the edb database then add the following entry in the postgresql.conf file:

    edb_job_scheduler.database_list='edb'

    Also, add parallel_clone and edb_job_scheduler to the shared libraries in the postgresql.conf file:

    shared_preload_libraries='....,$libdir/parallel_clone, $libdir/edb_job_scheduler'

    Restart the database server to load the changes in postgresql.conf file.

Overview of the functions

Use the following functions with EDB Clone Schema:

  • localcopyschema. This function copies a schema and its database objects from a source database into the same database (the target) but with a different schema name from the original. Use this function when the source schema and the copy will reside within the same database. See localcopyschema for more information.
  • localcopyschema_nb. This function performs the same purpose as localcopyschema but as a background job, which frees up the terminal from which the function was initiated. This function is referred to as a non-blocking function. See localcopyschema_nb for more information.
  • remotecopyschema. This function copies a schema and its database objects from a source database to a different target database. Use this function when the source schema and the copy will reside in separate databases. The separate databases can reside in the same EDB Postgres Advanced Server database clusters or in different ones. See remotecopyschema for more information.
  • remotecopyschema_nb. This function performs the same purpose as remotecopyschema but as a background job, which frees up the terminal from which the function was initiated. This function is a non-blocking function. See remotecopyschema_nb for more information.
  • process_status_from_log. This function displays the status of the cloning functions. The information is obtained from a log file you specify when invoking a cloning function. See process_status_from_log for more information.
  • remove_log_file_and_job. This function deletes the log file created by a cloning function. You can also use this function to delete a job created by the non-blocking form of the function. See remove_log_file_and_job for more information.

List of supported database objects

You can clone these database objects from one schema to another:

  • Data types
  • Tables including partitioned tables, excluding foreign tables
  • Indexes
  • Constraints
  • Sequences
  • View definitions
  • Materialized views
  • Private synonyms
  • Table triggers, but excluding event triggers
  • Rules
  • Functions
  • Procedures
  • Packages
  • Comments for all supported object types
  • Access control lists (ACLs) for all supported object types

You can't clone the following database objects:

  • Large objects (Postgres LOBs and BFILEs)
  • Logical replication attributes for a table
  • Database links
  • Foreign data wrappers
  • Foreign tables
  • Event triggers
  • Extensions

For cloning objects that rely on extensions, see the limitations that follow.

  • Row-level security
  • Policies
  • Operator class

Limitations

The following limitations apply:

  • EDB Clone Schema is supported on EDB Postgres Advanced Server when you specify a dialect of Compatible with Oracle on the EDB Postgres Advanced Server Dialect dialog box during installation. It's also supported when you include the --redwood-like keywords during a text-mode installation or cluster initialization.
  • The source code in functions, procedures, triggers, packages, and so on, aren't modified after being copied to the target schema. If such programs contain coded references to objects with schema names, the programs might fail when invoked in the target schema if such schema names are no longer consistent in the target schema.
  • Cross-schema object dependencies aren't resolved. If an object in the target schema depends on an object in another schema, this dependency isn't resolved by the cloning functions.
  • For remote cloning, if an object in the source schema depends on an extension, then you must create this extension in the public schema of the remote database before invoking the remote cloning function.
  • At most, 16 copy jobs can run in parallel to clone schemas. Each job can have at most 16 worker processes to copy table data in parallel.
  • You can't cancel queries run by background workers.
← Prev

Copying a database

↑ Up

Copying a database

Next →

Setting up EDB Clone Schema