Setting up EDB Clone Schema v16

To use EDB Clone Schema, you must first install several extensions along with the PL/Perl language on any database used as the source or target database by an EDB Clone Schema function.

In addition, it might help to modify some configuration parameters in the postgresql.conf file of the database servers.

Installing extensions

Perform this installation on any database to be used as the source or target database by an EDB Clone Schema function.

1. Install the following extensions on the database: postgres_fdw, dblink, adminpack and pgagent.

2. Ensure that pgAgent is installed before creating the pgagent extension. On Linux, you can use the edb-as<xx>-pgagent RPM package, where <xx> is the EDB Postgres Advanced Server version number to install pgAgent. On Windows, use StackBuilder Plus to download and install pgAgent.

   Install the extensions:

   → WrapCopy

   CREATE EXTENSION postgres_fdw SCHEMA public;
   CREATE EXTENSION dblink SCHEMA public;
   CREATE EXTENSION adminpack;
   CREATE EXTENSION pgagent;

   For more information about using the CREATE EXTENSION command, see the PostgreSQL core documentation.

Modifying the configuration file

Modify the postgresql.conf file by adding $libdir/parallel_clone to the shared_preload_libraries configuration parameter:

shared_preload_libraries = '$libdir/dbms_pipe,$libdir/dbms_aq,$libdir/parallel_clone'

Installing PL/Perl

1. Install the Perl procedural language (PL/Perl) on the database, and run the CREATE TRUSTED LANGUAGE plperl command. For Linux, install PL/Perl using the edb-as<xx>-server-plperl RPM package, where <xx> is the EDB Postgres Advanced Server version number. For Windows, use the EDB Postgres Language Pack. For information on EDB Language Pack, see the EDB Postgres Language Pack.

2. Connect to the database as a superuser and run the following command:

   → WrapCopy

   CREATE TRUSTED LANGUAGE plperl;

For more information about using the CREATE LANGUAGE command, see the PostgreSQL core documentation.

Setting configuration parameters

You might need to modify configuration parameters in the postgresql.conf file.

Performance configuration parameters

You might need to tune the system for copying a large schema as part of one transaction. Tuning of configuration parameters is for the source database server referenced in a cloning function.

You might need to tune the following configuration parameters in the postgresql.conf file:

• work_mem. Specifies the amount of memory for internal sort operations and hash tables to use before writing to temporary disk files.
• maintenance_work_mem. Specifies the maximum amount of memory for maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY to use.
• max_worker_processes. Sets the maximum number of background processes that the system can support.
• checkpoint_timeout. Maximum time between automatic WAL checkpoints, in seconds.
• checkpoint_completion_target. Specifies the target of checkpoint completion as a fraction of total time between checkpoints.
• checkpoint_flush_after. Whenever more than checkpoint_flush_after bytes are written while performing a checkpoint, attempt to force the OS to issue these writes to the underlying storage.
• max_wal_size. Maximum size to let the WAL grow to between automatic WAL checkpoints.
• max_locks_per_transaction. Controls the average number of object locks allocated for each transaction. Individual transactions can lock more objects as long as the locks of all transactions fit in the lock table.

For information about the configuration parameters, see the PostgreSQL core documentation.

Status logging

Status logging by the cloning functions creates log files in the directory specified by the log_directory parameter in the postgresql.conf file for the database server to which you're connected when invoking the cloning function.

The default location is PGDATA/log:

#log_directory = 'log'        # directory where log files are written,
                              # can be absolute or relative to PGDATA

This directory must exist before running a cloning function.

The name of the log file is determined by what you specify in the parameter list when invoking the cloning function.

To display the status from a log file, use the process_status_from_log function.

To delete a log file, use the remove_log_file_and_job function, or delete it manually from the log directory.

Installing EDB Clone Schema

Install the EDB Clone Schema on any database to be used as the source or target database by an EDB Clone Schema function.

1. If you previously installed an older version of the edb_cloneschema extension, run the following command:

   → WrapCopy

   DROP EXTENSION parallel_clone CASCADE;

   This command also drops the edb_cloneschema extension.

2. Install the extensions. Make sure that you create the parallel_clone extension before creating the edb_cloneschema extension.

   → WrapCopy

   CREATE EXTENSION parallel_clone SCHEMA public;
   
   CREATE EXTENSION edb_cloneschema;

Creating the foreign servers and user mappings

When using one of the local cloning functions localcopyschema or localcopyschema_nb, one of the required parameters includes a single, foreign server. This server is for identifying the database server and its database that's the source and receiver of the cloned schema.

When using one of the remote cloning functions remotecopyschema or remotecopyschema_nb, two of the required parameters include two foreign servers. The foreign server specified as the first parameter identifies the source database server and its database that's the provider of the cloned schema. The foreign server specified as the second parameter identifies the target database server and its database that's the receiver of the cloned schema.

For each foreign server, you must create a user mapping. When a selected database superuser invokes a cloning function, that superuser must be mapped to a database user name and password that has access to the foreign server that's specified as a parameter in the cloning function.

For general information about foreign data, foreign servers, and user mappings, see the PostgreSQL core documentation.

Foreign server and user mapping for local cloning functions

For the localcopyschema and localcopyschema_nb functions, the source and target schemas are both in the same database of the same database server. You must define and specify only one foreign server for these functions. This foreign server is also referred to as the local server because this server is the one to which you must be connected when invoking the localcopyschema or localcopyschema_nb function.

The user mapping defines the connection and authentication information for the foreign server. You must create this foreign server and user mapping in the database of the local server in which the cloning occurs.

The database user for whom the user mapping is defined must be a superuser and connected to the local server when invoking an EDB Clone Schema function.

This example creates the foreign server for the database containing the schema to clone and to receive the cloned schema:

CREATE SERVER local_server FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(
    host 'localhost',
    port '5444',
    dbname 'edb'
);

For more information about using the CREATE SERVER command, see the PostgreSQL core documentation.

The user mapping for this server is:

CREATE USER MAPPING FOR enterprisedb SERVER local_server
  OPTIONS (
    user 'enterprisedb',
    password 'password'
);

For more information about using the CREATE USER MAPPING command, see the PostgreSQL core documentation.

These psql commands show the foreign server and user mapping:

edb=# \des+

List of foreign servers
-[ RECORD 1 ]--------+----------------------------------------------
Name                 | local_server
Owner                | enterprisedb
Foreign-data wrapper | postgres_fdw
Access privileges    |
Type                 |
Version              |
FDW options          | (host 'localhost', port '5444', dbname 'edb')
Description          |

edb=# \deu+

                        List of user mappings
    Server    |  User name   |                 FDW options
--------------+--------------+----------------------------------------------
 local_server | enterprisedb | ("user" 'enterprisedb', password 'password')
(1 row)

When database superuser enterprisedb invokes a cloning function, the database user enterprisedb with its password is used to connect to local_server on the localhost with port 5444 to database edb.

In this case, the mapped database user, enterprisedb, and the database user, enterprisedb, used to connect to the local edb database are the same database user. However, that's not required.

For specific use of these foreign server and user mapping examples, see the example given in localcopyschema.

Foreign server and user mapping for remote cloning functions

For the remotecopyschema and remotecopyschema_nb functions, the source and target schemas are in different databases of either the same or different database servers. You must define and specify two foreign servers for these functions.

The foreign server defining the originating database server and its database containing the source schema to clone is referred to as the source server or the remote server.

The foreign server defining the database server and its database to receive the schema to clone is referred to as the target server or the local server. The target server is also referred to as the local server because this server is the one to which you must be connected when invoking the remotecopyschema or remotecopyschema_nb function.

The user mappings define the connection and authentication information for the foreign servers. You must create all of these foreign servers and user mappings in the target database of the target/local server. The database user for whom the user mappings are defined must be a superuser and the user connected to the local server when invoking an EDB Clone Schema function.

This example creates the foreign server for the local, target database that receives the cloned schema:

CREATE SERVER tgt_server FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(
    host 'localhost',
    port '5444',
    dbname 'tgtdb'
);

The user mapping for this server is:

CREATE USER MAPPING FOR enterprisedb SERVER tgt_server
  OPTIONS (
    user 'tgtuser',
    password 'tgtpassword'
);

This example creates the foreign server for the remote, source database that's the source for the cloned schema:

CREATE SERVER src_server FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(
    host '192.168.2.28',
    port '5444',
    dbname 'srcdb'
);

The user mapping for this server is:

CREATE USER MAPPING FOR enterprisedb SERVER src_server
  OPTIONS (
    user 'srcuser',
    password 'srcpassword'
);

Displaying foreign servers and user mappings

These psql commands show the foreign servers and user mappings:

tgtdb=# \des+

List of foreign servers
-[ RECORD 1 ]--------+---------------------------------------------------
Name                 | src_server
Owner                | tgtuser
Foreign-data wrapper | postgres_fdw
Access privileges    |
Type                 |
Version              |
FDW options          | (host '192.168.2.28', port '5444', dbname 'srcdb')
Description          |
-[ RECORD 2 ]--------+---------------------------------------------------
Name                 | tgt_server
Owner                | tgtuser
Foreign-data wrapper | postgres_fdw
Access privileges    |
Type                 |
Version              |
FDW options          | (host 'localhost', port '5444', dbname 'tgtdb')
Description          |

tgtdb=# \deu+

                      List of user mappings
   Server   |  User name   |                FDW options
------------+--------------+--------------------------------------------
 src_server | enterprisedb | ("user" 'srcuser', password 'srcpassword')
 tgt_server | enterprisedb | ("user" 'tgtuser', password 'tgtpassword')
(2 rows)

When database superuser enterprisedb invokes a cloning function, the database user tgtuser with password tgtpassword is used to connect to tgt_server on the localhost with port 5444 to database tgtdb.

In addition, database user srcuser with password srcpassword connects to src_server on host 192.168.2.28 with port 5444 to database srcdb.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    srcdb           srcuser         192.168.2.27/32         md5

For specific use of these foreign server and user mapping examples, see the example given in remotecopyschema.