Copying database objects from a local source to a target v17

Suggest edits

There are two functions you can use with EDB Clone Schema to perform a local copy of a schema and its database objects:

  • 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.

Performing a local copy of a schema

The localcopyschema function copies a schema and its database objects in a local database specified in the source_fdw foreign server from the source schema to the specified target schema in the same database.

localcopyschema(
  <source_fdw> TEXT,
  <source_schema> TEXT,
  <target_schema> TEXT,
  <log_filename> TEXT
  [, <on_tblspace> BOOLEAN
  [, <verbose_on> BOOLEAN
  [, <copy_acls> BOOLEAN
  [, <worker_count> INTEGER ]]]]
)

The function returns a Boolean value. 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.

Parameters

source_fdw

Name of the foreign server managed by the postgres_fdw foreign data wrapper from which to clone database objects.

source_schema

Name of the schema from which to clone database objects.

target_schema

Name of the schema into which to clone database objects from the source schema.

log_filename

Name of the log file in which information from the function is recorded. The log file is created under the directory specified by the log_directory configuration parameter in the postgresql.conf file.

on_tblspace

Boolean value to specify whether to create database objects in their tablespaces. If false, then the TABLESPACE clause isn't included in the applicable CREATE DDL statement when added to the target schema. If true, then the TABLESPACE clause is included in the CREATE DDL statement when added to the target schema. The default value is false.

verbose_on

Boolean value to specify whether to print the DDLs in log_filename when creating objects in the target schema. If false, then DDLs aren't printed. If true, then DDLs are printed. The default value is false.

copy_acls

Boolean value to specify whether to include the access control list (ACL) while creating objects in the target schema. The access control list is the set of GRANT privilege statements. If false, then the access control list isn't included for the target schema. If true, then the access control list is included for the target schema. The default value is false.

worker_count

Number of background workers to perform the clone in parallel. The default value is 1.

Example

This example shows the cloning of schema edb containing a set of database objects to target schema edbcopy. Both schemas are in database edb as defined by local_server.

The example is for the following environment:

  • Host on which the database server is running: localhost
  • Port of the database server: 5444
  • Database source/target of the clone: edb
  • Foreign server (local_server) and user mapping with the information of the preceding bullet points
  • Source schema: edb
  • Target schema: edbcopy
  • Database superuser to invoke localcopyschema: enterprisedb

Before invoking the function, database user enterprisedb connects to to database edb:

edb=# SET search_path TO "$user",public,edb_util;
SET
edb=# SHOW search_path;
Output
        search_path
---------------------------
 "$user", public, edb_util
(1 row)
edb=# SELECT localcopyschema ('local_server','edb','edbcopy','clone_edb_edbcopy');
Output
 localcopyschema
-----------------
 t
(1 row)

The following displays the logging status using the process_status_from_log function:

edb=# SELECT process_status_from_log('clone_edb_edbcopy');
Output
                                    process_status_from_log
------------------------------------------------------------------------------------------------
 (FINISH,"2017-06-29 11:07:36.830783-04",3855,INFO,"STAGE: FINAL","successfully cloned schema")
(1 row)

Results

After the clone is complete, the following shows some of the database objects copied to the edbcopy schema:

edb=# SET search_path TO edbcopy;
SET
edb=# \dt+
Output
                       List of relations
 Schema  |  Name   | Type  |    Owner     |    Size    | Description
---------+---------+-------+--------------+------------+-------------
 edbcopy | dept    | table | enterprisedb | 8192 bytes |
 edbcopy | emp     | table | enterprisedb | 8192 bytes |
 edbcopy | jobhist | table | enterprisedb | 8192 bytes |
(3 rows)
edb=# \dv
Output
         List of relations
 Schema  |   Name   | Type |    Owner
---------+----------+------+--------------
 edbcopy | salesemp | view | enterprisedb
(1 row)
edb=# \di
Output
                 List of relations
 Schema  |     Name      | Type  |    Owner     |  Table
---------+---------------+-------+--------------+---------
 edbcopy | dept_dname_uq | index | enterprisedb | dept
 edbcopy | dept_pk       | index | enterprisedb | dept
 edbcopy | emp_pk        | index | enterprisedb | emp
 edbcopy | jobhist_pk    | index | enterprisedb | jobhist
(4 rows)
edb=# \ds
Output
            List of relations
 Schema  |    Name    |   Type   |    Owner
---------+------------+----------+--------------
 edbcopy | next_empno | sequence | enterprisedb
(1 row)
edb=# SELECT DISTINCT schema_name, name, type FROM user_source WHERE
schema_name = 'EDBCOPY' ORDER BY type, name;
Output
 schema_name |              name              |     type
-------------+--------------------------------+--------------
 EDBCOPY     | EMP_COMP                       | FUNCTION
 EDBCOPY     | HIRE_CLERK                     | FUNCTION
 EDBCOPY     | HIRE_SALESMAN                  | FUNCTION
 EDBCOPY     | NEW_EMPNO                      | FUNCTION
 EDBCOPY     | EMP_ADMIN                      | PACKAGE
 EDBCOPY     | EMP_ADMIN                      | PACKAGE BODY
 EDBCOPY     | EMP_QUERY                      | PROCEDURE
 EDBCOPY     | EMP_QUERY_CALLER               | PROCEDURE
 EDBCOPY     | LIST_EMP                       | PROCEDURE
 EDBCOPY     | SELECT_EMP                     | PROCEDURE
 EDBCOPY     | EMP_SAL_TRIG                   | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_a_19991" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_a_19992" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_a_19999" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_a_20000" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_a_20004" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_a_20005" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_c_19993" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_c_19994" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_c_20001" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_c_20002" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_c_20006" | TRIGGER
 EDBCOPY     | "RI_ConstraintTrigger_c_20007" | TRIGGER
 EDBCOPY     | USER_AUDIT_TRIG                | TRIGGER
(24 rows)

Performing a local copy of a schema as a batch job

The localcopyschema_nb function copies a schema and its database objects in a local database specified in the source_fdw foreign server from the source schema to the specified target schema in the same database. The copy occurs in a non-blocking manner as a job submitted to pgAgent.

localcopyschema_nb(
  <source_fdw> TEXT,
  <source> TEXT,
  <target> TEXT,
  <log_filename> TEXT
  [, <on_tblspace> BOOLEAN
  [, <verbose_on> BOOLEAN
  [, <copy_acls> BOOLEAN
  [, <worker_count> INTEGER ]]]]
)

The function returns an INTEGER value job ID for the job submitted to pgAgent. If the function fails, then null is returned.

The source_fdw, source, target, and log_filename parameters are required. All other parameters are optional.

After the pgAgent job completes, remove it with the remove_log_file_and_job function.

Parameters

source_fdw

Name of the foreign server managed by the postgres_fdw foreign data wrapper from which to clone database objects.

source

Name of the schema from which to clone database objects.

target

Name of the schema into which to clone database objects from the source schema.

log_filename

Name of the log file in which to record information from the function. The log file is created under the directory specified by the log_directory configuration parameter in the postgresql.conf file.

on_tblspace

Boolean value to specify whether to create database objects in their tablespaces. If false, then the TABLESPACE clause isn't included in the applicable CREATE DDL statement when added to the target schema. If true, then the TABLESPACE clause is included in the CREATE DDL statement when added to the target schema. The default value is false.

verbose_on

Boolean value to specify whether to print the DDLs in log_filename when creating objects in the target schema. If false, then DDLs aren't printed. If true, then DDLs are printed. The default value is false.

copy_acls

Boolean value to specify whether to include the access control list (ACL) while creating objects in the target schema. The access control list is the set of GRANT privilege statements. If false, then the access control list isn't included for the target schema. If true, then the access control list is included for the target schema. The default value is false.

worker_count

Number of background workers to perform the clone in parallel. The default value is 1.

Example

The same cloning operation is performed as the example in localcopyschema but using the non-blocking function localcopyschema_nb.

You can use this command to see whether pgAgent is running on the appropriate local database:

[root@localhost ~]# ps -ef | grep pgagent
root       4518      1  0 11:35 pts/1    00:00:00 pgagent -s /tmp/pgagent_edb_log 
hostaddr=127.0.0.1 port=5444 dbname=edb user=enterprisedb password=password
root       4525   4399  0 11:35 pts/1    00:00:00 grep --color=auto pgagent

If pgAgent isn't running, start it by executing the pgagent option. The pgagent program file is located in the bin subdirectory of the EDB Postgres Advanced Server installation directory.

[root@localhost bin]# ./pgagent -l 2 -s /tmp/pgagent_edb_log hostaddr=127.0.0.1 port=5444 
dbname=edb user=enterprisedb password=password
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.

edb=# SELECT edb_util.localcopyschema_nb ('local_server','edb','edbcopy','clone_edb_edbcopy');
Output
 localcopyschema_nb
--------------------
               4
(1 row)

The following displays the job status:

edb=# SELECT edb_util.process_status_from_log('clone_edb_edbcopy');
Output
                                   process_status_from_log
---------------------------------------------------------------------------------------------------
 (FINISH,"29-JUN-17 11:39:11.620093 -04:00",4618,INFO,"STAGE: FINAL","successfully cloned schema")
(1 row)

The following removes the pgAgent job:

edb=# SELECT edb_util.remove_log_file_and_job (4);
Output
 remove_log_file_and_job
-------------------------
 t
(1 row)
← Prev

Setting up EDB Clone Schema

↑ Up

Copying a database

Next →

Copying database objcts from a remote source