9.1 Setup Process
9 EDB Clone Schema : 9.1 Setup Process
9.1 Setup ProcessSeveral extensions along with the PL/Perl language must be installed on any database to be used as the source or target database by an EDB Clone Schema function.In addition, some configuration parameters in the postgresql.conf file of the database servers may benefit from some modification.These steps must be performed on any database to be used as the source or target database by an EDB Clone Schema function.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.The previously listed extensions can be installed by the following commands if they do not already exist: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.Run StackBuilder Plus, select and download the EDB Language Pack installer, and proceed with the installation.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.configINSTALL_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 following sections describe certain configuration parameters that may need to be altered in the postgresql.conf file.Tuning of configuration parameters is for the source database server referenced in a cloning function.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.184.108.40.206 Status LoggingStatus 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: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 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.These steps must be performed on any database to be used as the source or target database by an EDB Clone Schema function.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 each foreign server, a user mapping must be created. When a selected database superuser invokes a cloning function, that database superuser who invokes the function must have been mapped to a database user name and password that has access to the foreign server that is specified as a parameter in the cloning function.For general information about foreign data, foreign servers, and user mappings, see the PostgreSQL core documentation at: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.This server is 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.This foreign server and user mapping must be created within the database of the local server in which the cloning is to occur.The database user for whom the user mapping is defined must be a superuser and the user connected to the local server when invoking an EDB Clone Schema function.The following example creates the foreign server for the database containing the schema to be cloned, and to receive the cloned schema as well.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 specific usage of these foreign server and user mapping examples, see the example given in Section 9.2.1.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 foreign server defining the originating database server and its database containing the source schema to be cloned 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 be cloned 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.All of these foreign servers and user mappings must be created within 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.The following example creates the foreign server for the local, target database that is to receive the cloned schema.The following example creates the foreign server for the remote, source database that is to be the source for the cloned schema.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