PostgreSQL

Connectivity Summary

An out of the box connector is available for PostgreSQL databases. It provides support for crawling database objects, profiling of sample data and lineage building.

The drivers used by the connector are given below:

Driver / API: JDBC driver

Version: 42.2.14 (latest version is 42.2.23)

Details: https://mvnrepository.com/artifact/org.postgresql/postgresql/

Technical Specifications

The connector capabilities are shown below:

Crawling

Supported Objects Supported Data Types
Tables, Table Columns, Views, Stored Procedures, Functions, Triggers, Roles, Users, Permissions, Triggers, Usage Statistics

bigint, bigserial, bit, boolean, character, date, double, int, interval, money, numeric, path, real, smallint, text, time, timestamp, uuid

Profiling

Please see Profiling Data for more details on profiling.

Feature

Support

Remarks

Table Profiling

Row count, Columns count, View sample data

 

View Profiling

Row count, Columns count, View sample data

View is treated as a table for profiling purposes

Column Profiling

Min, Max, Null count, distinct, top 50 values

 

Full Profiling 

Supported

 

Sample Profiling

Supported

 

Lineage Building

 
Lineage Entities Details

Table lineage

Supported

Column lineage

Supported

Lineage Sources

Stored procedures, functions, triggers, views, SQL queries (from Query Sheet), query logs

Querying

Operation Details

Select

Supported

Insert

Not supported, by default.

Update

Not supported, by default.

Delete

Not supported, by default.

Joins within database

Supported

Joins outside database

Not supported

Aggregations

Supported

Group By

Supported

Order By

Supported

 
By default the service account provided for the connector will be used for any query operations. If the service account has write privileges, then Insert / Update / Delete queries can be executed.

Pre-requisites

To use the connector, the following need to be available:

  • Connection details as specified in the following section should be available.
  • An admin / service account with read privileges.
  • JDBC driver is provided by default. In case it needs to be changed, add PostgreSQL drivers into the OvalEdge Jar path to communicate with the PostgreSQL database. 

Check the Configuration section for further details on how to add the drivers to the jar path.

Connection Details

The following connection settings should be added for connecting to a PostgreSQL database:

  • Database Type: POSTGRESQL
  • Connection Name: Select a Connection name for the PostgreSQL database. The name that you specify is a reference name to easily identify your PostgreSQL database connection in OvalEdge. Example: PostgreSQL Connection DB1
  • Hostname / IP Address: Database instance URL (on-premises/cloud-based)
    Example: ovaledge-postgres.csklygkwz3dx.us-east-1.rds.amazonaws.com
  • Port number: 5432
  • Sid / Database: Name of the database to connect.
  • Username: User account login credential
  • Password: Password
  • Driver Name: JDBC driver name for PostgreSQL . It will be auto-populated.
    Example: org.postgresql.Driver
  • Connection String: PostgreSQL connection string. Set the Connection string toggle button to automatic, to get the details automatically from the credentials provided. Alternatively, you can manually enter the string.
    Format: jdbc:postgresql://{server}:5432/{sid}
    Example: jdbc:postgresql://ovaledge-postgres.csklygkwz3dx.us-east-1.rds.amazonaws.com:5432/ovaledgedb

Once connectivity is established, additional configurations for Crawling and Profiling can be specified.

Property

Details

Profile Settings

Order

Priority of the rule

Start time and End time

Used when crawling / profiling are to be scheduled

No. of threads

No. of threads used to perform profiling

Profile Type

Disabled/ Auto/ Sample/ Query

Row count Constraint

No. of rows to be fetched

Sample profile size

Sample profile row limit

Sample data count

Sample amount of data to profile

Query Timeout

Time to wait for response

Crawler options

Procedures, Functions, Triggers & Views Source Code/ Tables, Views & Columns/ Relationship

Profile options

Tables and Columns/ Views and Columns

Crawler Rules

Include and Exclude  Regex for table/ column/ procedures

Profile Rules

Include and Exclude  Regex for table/ column

Points to note

Port number may vary, 5432 is the default port number.

FAQs

  1. What should I know when upgrading my driver?
    The Microsoft JDBC Driver 7.4 supports the JDBC 4.2, and 4.3 (partially) specifications and includes JAR class libraries in the installation package as follows:

    JAR JDBC Specification JDK Version
    https://repo1.maven.org/maven2/org/postgresql/postgresql/42.2.14/postgresql-42.2.14.jar JDBC 42.2.x JDK 8.0
  2. How much does the driver cost?
    The Microsoft JDBC Driver for PostgreSQL is available at no additional charge.