Using

After you have configured your EDB Postgres Distribution, as stated in the Configuring section, you will be able to then use Precisely CDC's Replication functionality. For the examples in this guide, the replication functionality is demonstrated using EDB Postgres Advanced Server.

• Configure Postgres Distribution as a Source Instance in Connect CDC Director
  • Setup and configuration of EDB Postgres Advanced Server as a source instance for Connect CDC Director.
• Configure Postgres Distribution as a Target Instance in Connect CDC Director
  • Setup and configuration of EDB Postgres Advanced Server as a target instance to Connect CDC Director.
• Create Distribution on Connect CDC Director
  • Define how data will be replicated from source to target in a Model.
• Save the Model
  • Save, validate and commit the Model created for Replication.
• Execute Copy Replication using Connect CDC MonCon
  • Execute the Copy Replication to copy all the data from the Source Tables to the Target Tables.
• Create Replication Distribution using Connect CDC Director
  • Replicate all the changes made on the Source Tables to the Target Tables.
• Execute Replication using Connect CDC MonCon
  • Replication distribution will replicate all the changes done on the Source Tables to the Target Tables.

With Precisely Connect CDC you also have the ability to “Transform” your data as you replicate it from a source to target using Data Transformation. See the section on “Data Transformation” on how to enable this feature.

• Data Transformation
  • Modify the source data you are distributing or even create or derive the data you distribute to the target system.

Configure Postgres Distribution as a Source Instance in Connect CDC Director

The following steps demonstrate the setup and configuration of EDB Postgres Advanced Server as a source instance for Connect CDC Director.

1. Open Connect CDC Director.

2. Click New option.

3. Right click Hosts, Servers, Tables and select New Host.

4. Enter information for the Host, Host is the machine where Connect CDC Director is running.

5. Right Click on the newly added Host and select Test Connection, a success message will be displayed.

6. Right Click on the newly added Host and select New Server.

7. Enter Connection Information for EDB Postgres Advanced Server as Source Instance (Server name is where the database name is given).

8. Right Click on the newly added Server and select Install Source Metabase.

9. Provide EDB Postgres Advanced Server Source Instance User and Password on User ID and Password Screen.

10. Provide password of rpuser (refer to the user name and password given on New Server Screen) when prompted on the User ID and Password screen.

11. Click Ok on Install Source Metabase screen.

12. A success message will be displayed once the Source Metabase is created on the EDB Postgres Advanced Server Source Instance.

13. Open pgAdmin and connect to your EDB Postgres Advanced Server Source instance, navigate to Schemas and under the Schemas rpuser Schema has been created.

14. Navigate back to Connect CDC Director and right Click on the newly added Server and select Test Connection. A success message will be displayed.

15. Right Click on the newly added Server and select Prepare User Database, this will add the replication user to the public database.

16. On Database/Schema name Screen provide the schema name for the schema you wish to replicate. In our example we are using schema public.

17. On the User ID and Password Screen, provide EDB Postgres Advanced Server Source Instance User and Password.

18. Click Ok on Prepare User Database Screen.

19. A success message will be displayed once the Prepare User Database operation is successful.

20. Right Click on the newly added Server and select Refresh Available Tables.

21. Click on the Refresh list and then select schema public and click ok.

22. To display the available tables click newly added Server —> Tables —> Available Tables —> Public (Schema name), a list of available tables will be displayed.

Configure Postgres Distribution as a Target Instance in Connect CDC Director

The following steps demonstrate the setup and configuration of EDB Postgres Advanced Server as a target instance to Connect CDC Director.

1. Right click DBMS Servers and New Server.

2. Provide Connection Information for the EDB Postgres Advanced Server Target Instance.

3. Right Click on the newly added Server and select Install Target Only Metabase.

4. Provide EDB Postgres Advanced Server Target Instance User and Password on User ID and Password Screen.

5. Provide password of rpuser (refer to the user name and password given on New Server Screen) when prompted on the User ID and Password screen.

6. Click Ok on Install Target Only Metabase screen.

7. A success message will be displayed once the Target Only Metabase is created.

8. Open pgAdmin and connect to EDB Postgres Advanced Server Target Instance, and under Schemas rpuser Schema is created.

9. Right Click on the newly added Server and select Prepare User Database, this will add the replication user to EDB Postgres Advanced Server Target Instance.

10. On Database/Schema Name provide schema name public.

11. On User ID and Password Screen, provide EDB Postgres Advanced Server Target Instance user and password.

12. Click Ok on the Prepare User Database screen.

13. A success message will be displayed once the Prepare User Database is successful.

Create Distribution on Connect CDC Director

The following steps demonstrate the creation of Distribution which will define how data will be replicated from source to target. In our case from EDB Postgres Advanced Server source to EDB Postgres Advanced Server target.

1. Open Connect CDC Director.

2. Right click on Distributions and select New Distribution.

3. Enter Distribution information e.g distribution name, description etc and click ok.

4. Go to Source DBMS Server —> Tables —> Available Tables —> Public (Schema name).

5. Select the table(s) to replicate using the checking box in front of them and right click and select Select for Distribution —> All Checked Tables.

6. On the Distributed Tables Screen, select the Target Server under Select one or more target servers and select Create tables on target option and select the distribution under Select a Distribution option and click ok.

7. On Connect CDC Director Screen, select Yes.

8. On the Target Tables Screen, enter the Target Schema against each table and Click Next.

9. On Target Server Login Details Screen, enter EDB Postgres Advanced Server Target Instance user and password and click test connect. Once successful, click Add and then click Create.

10. Once done, click Finish.

11. To confirm the tables are created on Target Server, click the Target DBMS Server —> Tables —> Receiving Tables, the list of the tables will be displayed which are available on Source DBMS Server.

12. Open pgAdmin and connect to EDB Postgres Advanced Server Target Instance, and under Schema public, the tables will be created.

13. Mappings of tables setup for replication from EDB Postgres Advanced Server Source Instance to EDB Postgres Advanced Server Target Instance will be created under Distributions —> Mappings.

14. Open Distributions —> Newly Created Distribution —> Requests —> New Request —> Copy. Copy will copy/replicate all the data in tables from EDB Postgres Advanced Server Source Instance to EDB Postgres Advanced Server Target Instance.

15. Provide required information and click Ok on Copy Request Properties Screen.

Save the Model

The Model created in the above steps needs to be saved, validated and committed before the Replication can be performed.

1. To save the Model, click File and select Save option and provide the name for the Model.

2. Right Click Enterprise Data Movement Model and select Validate to validate the Model.

3. No error will be displayed if the Model is valid.

4. Right Click Enterprise Data Movement Model and select Commit —> Full.

5. Click Ok on Commit Model Screen. This will create the Model files.

Execute Copy Replication using Connect CDC MonCon

Connect CDC MonCon is a GUI application, separate from the Connect CDC Director, that you use for monitoring and control functions. We will use Connect CDC MonCon to execute the Copy Replication. Copy Replication will copy all the data from the Source Tables to the Target Tables.

1. Open Connect CDC MonCon.

2. Click Model —> New Model from Menu.

3. Provide Hostname/IP and select the saved Model from the drop down list.

4. In the Request Section, select the Distribution Model that was created for copying data created in the above steps and right click and select start.

5. Click on Process to see the progress.

6. Open pgAdmin once the copy operation is completed successfully and connect the EDB Postgres Advanced Server Target Instance and check the tables and the data is copied to the tables.

Create Replication Distribution using Connect CDC Director

Once the Copy replication operation is successful then a Replication option will be executed which will replicate all the changes made on the Source Tables to the Target Tables.

1. Open Connect CDC Director.

2. Create the Replication Request from Distributions —> Newly Created Distribution —> Requests —> New Request —> Replication.

3. Enter the name and press Ok.

4. Save the changes in the Model.

5. Right Click Enterprise Data Movement Model and select Validate to validate the Model.

6. Right Click Enterprise Data Movement Model and select Commit —> Full.

7. Click Ok on Commit Model Screen. This will create the Model files.

Execute Replication using Connect CDC MonCon

Replication distribution will replicate all the changes done on the Source Tables to the Target Tables.

1. Open Connect CDC MonCon.

2. As the Model is updated in the Connect CDC Director to add the Replication Distribution, click on the Model Update, this will add the Replication Distribution to the Connect CDC MonCon Interface.

3. The replication request will be displayed once the Model is updated successfully.

4. In the Request Section, select the Distribution Model created for Replication and right click and select Start.

5. On the Source Instance, update the data to be replicated in the Source Tables.

6. On the Target Instance, check data in the Target Tables, data will be updated there.

Data Transformation

Precisely Connect CDC has options that let you modify the source data you are distributing or even create or derive the data you distribute. You can use predefined functions to accomplish these data transformations or you can construct your own operations or functions.

For example, to express source currency values in terms of a different currency, you specify the transformation algorithm to use for that column mapping. For some common transformations, the algorithm is supplied as a predefined Connect CDC function.

The algorithms you specify are known in the Connect CDC Director as expressions. The Connect CDC Expression Handler parses them and then creates runtime code that calculates the result for the expressions.

In addition to or instead of calls to predefined functions, expressions may have an arbitrary number of constants, column references, and calculations on those constants and columns and functions. Their results must be compatible in type and length with the target column.

Steps to demonstrate data transformations

The following steps demonstrate data transformations.

1. Open Connect CDC Director.

2. Go to Source DBMS Server —> Tables —> Sending Tables.

3. Select the table(s) to perform data transformation using the checking box in front of them and right click and select Properties —> Mappings.

4. On the Mapping tab, you define the column mappings for each of your target tables with respect to the source tables. The Mapping tab displays the corresponding columns in the Target Column and Source Data columns in each row. Connect CDC Director automatically mapped target columns to source columns that have the same name.

When the Connect CDC Director maps a source table to a same-named target table, it also associates individual source table columns with same-named target table columns. You then select which of these column pairings you want to include in your data distribution, which additional column pairings you want to arrange, and what type of data transformation, if any, you want to assign to each column pairing.

Mappings Tab has the following options

- Receiving server list : The name of the target server is displayed.
- Receiving table list : The name of the target table is displayed. If it does not (for example, because its name does not match your source table’s), select the target table from the Receiving table list. The list includes all tables in the list of Available tables for the target server. Any table name with [M]preceding it is already mapped to a source table.

The Mapping tab grid displays each target table column and its datatype as well as its default corresponding source column, if any. Copy column under Method in the grid means the source column value is to be distributed without any special handling or transformation.

- Target Column : Contains all the columns in the target table.
- Datatype : Datatype of the column for both Source and Target tables as per the mapping defined between source and target table.
- Source Data : Contains all the columns in the Source table.
- DKey : This column identifies the Distribution Key. By default, DKey is marked for the Primary key. You can deselect the default and add as many check marks as necessary.
- Method : It contains a list of data transformation functions.

5. In our example, click on Method drop down list for the target column loc and select lower case, this data transformation will convert the data in loc column of target table to lower case after the replication is done.

6. Perform the replication from Connect CDC MonCon.

7. On the Target Instance, check data in the Target Table dept, data will be transformed for column loc and it will be stored as lower case.

• On this page
• Configure Postgres Distribution as a Source Instance in Connect CDC Director
• Configure Postgres Distribution as a Target Instance in Connect CDC Director
• Create Distribution on Connect CDC Director
• Save the Model
• Execute Copy Replication using Connect CDC MonCon
• Create Replication Distribution using Connect CDC Director
• Execute Replication using Connect CDC MonCon
• Data Transformation