{"id":12900,"date":"2019-06-05T15:53:57","date_gmt":"2019-06-05T19:53:57","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=12900"},"modified":"2024-09-06T16:19:17","modified_gmt":"2024-09-06T20:19:17","slug":"connect-to-postgresql-in-workbench","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/iri\/iri-workbench\/connect-to-postgresql-in-workbench\/","title":{"rendered":"Connecting PostgreSQL to IRI Workbench"},"content":{"rendered":"

Connecting to\u00a0<\/span>PostgreSQL<\/a><\/span>\u00a0(and c<\/span>ompatible databases like Amazon RDS, CockroachDB and EnterpriseDB) for data movement and manipulation in <\/span>IRI Workbench<\/a><\/span>-supported products like Voracity, CoSort, FieldShield, NextForm and RowGen follows the same paradigm as other relational databases.\u00a0<\/span>This means driver downloading and installation, configuration (using and testing with your credentials), registration, and validation.<\/span><\/p>\n

Workbench users interact with PostgreSQL tables and exchange metadata in IRI job wizards through the PostgreSQL\u00a0<\/span>JDBC\u00a0<\/span>driver. They move data to and from Postgres and the\u00a0<\/span>SortCL<\/a><\/span>\u00a0data manipulation engine common to the aforementioned IRI products via\u00a0<\/span>ODBC<\/span>. Note that if you are masking data in PostgreSQL with IRI DarkShield, only the JDBC connection documented below is required.<\/span><\/span><\/p>\n

As with<\/span>\u00a0the other databases, if you run Workbench on Windows, you will run the ODBC 32-bit data source administrator and specify the PostgreSQL ODBC driver in it. If you are connecting to a database inside a firewal<\/span>l, you should also use a tool like OpenVPN for remote access. Of course, you can use direct access if you are inside your Local Area Network.<\/span><\/p>\n

This document provides step-by-step instructions to: 1) obtain the PostgreSQL JDBC driver and PostgreSQL ODBC driver (since you will need both), 2) configure those drivers and confirm they are working, 3) register and bridge connections, and 4) test things out.\u00a0<\/span><\/p>\n

Step 1: Obtaining JDBC and ODBC drivers for PostgreSQL<\/span><\/h3>\n

In this section, it does not matter what order you go regarding the drivers. What matters is that as long as you have the drivers installed, you should be able to use them for configuration for connecting to PostgreSQL. This section covers installation of the JDBC and ODBC drivers for PostgreSQL.<\/span><\/p>\n

First, download<\/span> the ODBC driver from: https:\/\/www.postgresql.org\/ftp\/odbc\/releases\u00a0<\/a><\/p>\n

\"\"<\/a><\/p>\n

I used\u00a0psqlodbc_10_03_0000-x86.zip<\/span>. \u00a0After downloading, extract it with WinRAR (\u201cExtract Here\u201d):<\/span><\/p>\n

\"\"<\/a><\/p>\n

After you extract the zip file, open \u201cpsqlodbc_x86.msi\u201d by right-clicking and clicking on \u201cInstall\u201d:<\/p>\n

\"\"<\/a><\/p>\n

Follow the installation instructions.<\/p>\n

Download the PostgreSQL JDBC driver (as of this writing, the latest version was postgresql-42.2.5.jar) at: https:\/\/jdbc.postgresql.org\/<\/a><\/p>\n

\"\"<\/a><\/p>\n

Step 2: Configuring JDBC and ODBC Drivers for PostgreSQL<\/span><\/h3>\n

Configuring JDBC and ODBC drivers for PostgreSQL is no different than configuring them for other databases. At this point, you should already have downloaded and installed the drivers per the instructions in step 1.<\/span><\/p>\n

Configure the\u00a0<\/span>ODBC Driver:<\/span><\/h5>\n

Run the 32-bit version of ODBC Data Source Administrator:<\/span><\/p>\n

\"\"<\/a><\/p>\n

Click on \u201cSystem DSN.\u201d<\/span><\/p>\n

\"\"<\/a><\/p>\n

Click \u00a0\u201cAdd\u201d:<\/span><\/p>\n

\"\"<\/a><\/p>\n

The ANSI driver is used for standard Latin text. The Unicode driver is used for a Unicode character set. We recommended the ANSI driver because using working with UTF is more complex.<\/span><\/p>\n

Highlight \u201cPostgreSQL30\u201d (located under\u00a0<\/span>\u201cSystem DSN\u201d) and click \u201cConfigure.\u201d<\/span><\/p>\n

\"\"<\/a><\/p>\n

After your credentials are entered, test the connection by clicking on \u201cTest\u201d (make sure your VPN is running, if applicable). \u00a0<\/span><\/p>\n

\"\"<\/a><\/p>\n

After the connection is successful, click \u201cSave.\u201d<\/p>\n

Configure the\u00a0<\/span>JDBC Driver:<\/span><\/h5>\n

Open IRI Workbench.<\/p>\n

\"\"<\/a><\/p>\n

Click on \u201cNew Connection Profile\u201d\u00a0<\/span>in\u00a0Data Source Explorer and select \u201cPostgreSQL.\u201d Then click \u201cNext.\u201d<\/span><\/p>\n

\"\"<\/a><\/p>\n

Click \u201cEdit Driver Definition\u201d\u00a0<\/span>(th<\/span>e blue-lined triangle next to the drop-down menu for the drivers):<\/p>\n

Click \u201cAdd JAR\/ZIP.\u201d This is where you have to locate the .jar file that you have downloaded (you should have postgresql-42.2.5.jar):<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

Click on \u201cProperties.\u201d In this tab, enter your credentials for connecting to PostgreSQL. <\/span><\/p>\n

\"\"<\/a><\/p>\n

Enter the appropriate credentials. \u00a0<\/span>Check the \u201csave password\u201d box so that you will not have to enter the credentials every time. After you enter the credentials, click \u201cOK.\u201d<\/span><\/p>\n

Test the connection to ensure that you can successfully connect to PostgreSQL (make sure\u00a0<\/span>your\u00a0VPN is running, if applicable):<\/span><\/p>\n

\"\"<\/a><\/p>\n

 <\/p>\n

Once the connection is successful, click \u201cFinish.\u201d <\/span><\/p>\n

Once you finish the process, you can rename the database in the Data Source Explorer if you wish.<\/span><\/p>\n

Step 3: Registering and Bridging Connections<\/h3>\n

Open the Data Connection Registry from the IRI Workbench (Window > Preferences > IRI).<\/span><\/p>\n

\"\"<\/a><\/p>\n

Click on \u201cPostgreSQL30\u201d and click \u201cEdit.\u201d<\/span><\/p>\n

\"\"<\/a><\/p>\n

Add a schema filter if you wish to add one. See <\/span>this article<\/span><\/a> if you have hundreds or thousands of tables. Because the ODBC driver does not save the credentials, you will need to enter them in the \u201cAuthentication\u201d section. Select a connection profile for the JDBC connection. After you enter the information, click OK. <\/span><\/p>\n

\"\"<\/a><\/p>\n

Click \u201cApply\u201d to save the changes.<\/p>\n

\"\"<\/a><\/p>\n

When you register the connection with PostgreSQL, you are mapping out the permissions between the executables that migrate and control data through ODBC, and the IRI Workbench wizards that use the \u00a0JDBC connection to parse and use the table metadata. This is what enables IRI Workbench to make and visually interact with your data architecture and column-level specifications. <\/span><\/p>\n

Step 4: Testing Things Out<\/h3>\n

At this point, you should have everything properly configured. Both can be verified by opening the database and table you want in Data Source Explorer (which uses JDBC). For example, right-click on a table located in your schema, and choose \u201cgenerate DDF\u201d from the IRI menu:<\/span><\/p>\n

\"\"<\/a><\/p>\n

This will open the Data Definition Format (DDF) file wizard, which builds SortCL-compatible \/FIELD layouts you can use in multiple application job scripts like FieldShield .fcl masking jobs.<\/span><\/p>\n

\"\"<\/a><\/p>\n

Once you enter specifications like these and click OK, you should see the field layouts matching the columns in the table you highlighted:<\/span><\/p>\n

\"\"<\/a><\/p>\n

And after you click \u201cFinish,\u201d you should have reusable metadata file in your project explorer like this:<\/span><\/p>\n

\"\"<\/a><\/p>\n

If you need additional help connecting your PostgreSQL data sources in IRI Workbench, contact your IRI representative or email your details to <\/span>support@iri.com<\/span><\/a>.<\/span><\/p>\n

Follow along with our YouTube video!<\/strong><\/p>\n