9.1 Setup Process

Table of Contents Previous Next


9 EDB Clone Schema : 9.1 Setup Process

In addition, some configuration parameters in the postgresql.conf file of the database servers may benefit from some modification.
Step 1: The following extensions must be installed on the database:
Ensure that pgAgent is installed before creating the pgagent extension. You can use StackBuilder Plus to download and install pgAgent.
For more information about using the CREATE EXTENSION command, see the PostgreSQL core documentation at:
Step 2: Modify the postgresql.conf file.
Modify the postgresql.conf file by adding $libdir/parallel_clone to the shared_preload_libraries configuration parameter as shown by the following example:
Step 3: The Perl Procedural Language (PL/Perl) must be installed on the database and the CREATE TRUSTED LANGUAGE plperl command must be run.
Step 4: Once the installation has been completed, edit the configuration file plLanguages.config located in the Advanced Server installation directory under subdirectory etc/sysconfig to point to the directory where PL/Perl was installed.
INSTALLER_DIR/etc/sysconfig/plLanguages.config
INSTALL_DIR represents the installation directory of Advanced Server such as /opt/edb/as10, for example.
Replace the PERL_INSTALL_PATH variable in the plLanguages.config file with the PL/Perl directory:
Step 5: Restart Advanced Server.
Step 6: Connect to the database as a superuser where PL/Perl was installed and run the following command:
For more information about using the CREATE LANGUAGE command, see the PostgreSQL core documentation at:
The configuration parameters in the postgresql.conf file that may need to be tuned include the following:
work_mem. Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.
maintenance_work_mem. Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.
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 have been 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. This parameter 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.
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 are connected when invoking the cloning function.
The default location is PGDATA/log as shown by the following:
To display the status from a log file, use the process_status_from_log function as described in Section 9.2.5.
To delete a log file, use the remove_log_file_and_job function as described in Section 9.2.6, or simply navigate to the log directory and delete it manually.
Step 1: If you had previously installed an older version of the edb_cloneschema extension, then you must run the following command:
This command also drops the edb_cloneschema extension.
Step 2: Install the extensions using the following commands:
Make sure you create the parallel_clone extension before creating the edb_cloneschema extension.
When using one of the local cloning functions, localcopyschema or localcopyschema_nb, one of the required parameters includes a single, foreign server for identifying the database server along with its database that is the source and the 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 along with its database that is the provider of the cloned schema. The foreign server specified as the second parameter identifies the target database server along with its database that is the receiver of the cloned schema.
For the localcopyschema and localcopyschema_nb functions, the source and target schemas are both within the same database of the same database server. Thus, only one foreign server must be defined and specified for these functions. This foreign server is also referred to as the local server.
For more information about using the CREATE SERVER command, see the PostgreSQL core documentation at:
For more information about using the CREATE USER MAPPING command, see the PostgreSQL core documentation at:
The following psql commands show the foreign server and user mapping:
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 happen to be the same, identical database user, but that is not an absolute requirement.
For the remotecopyschema and remotecopyschema_nb functions, the source and target schemas are in different databases of either the same or different database servers. Thus, two foreign servers must be defined and specified for these functions.
The following psql commands show the foreign servers and user mappings:
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.
Note: Be sure the pg_hba.conf file of the database server running the source database srcdb has an appropriate entry permitting connection from the target server location (address 192.168.2.27 in the following example) connecting with the database user srcuser that was included in the user mapping for the foreign server src_server defining the source server and database.

9 EDB Clone Schema : 9.1 Setup Process

Table of Contents Previous Next