Postgres

Owned by Chichi
Last updated: Nov 05, 2023
3 min read

Open in new tab

This page will guide you through the setup of Postgres in K using the direct connect method.

Integration details

Scope

Included

Comments

Scope

Included

Comments

Metadata

YES

 

Lineage

YES

 

Usage

No

 

Sensitive Data Scanner

No

 

Step 1) Establish Postgres Access

You will need to create a user <kada user> for the K Platform.

Generally all users should have access to the pg_catalog tables on Database creation for Postgres.

In the event the user doesn’t have access, explicit grants will need to be done per new Database in Postgres to the <kada user>.

GRANT USAGE ON SCHEMA pg_catalog TO <kada user>; GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO <kada user>;

The user used for the extraction must also be able to connect to the the databases needed for extraction.

PG Tables

The user must have access to these pg_catalog tables per applicable database in Postgres

  • pg_class

  • pg_namespace

  • pg_proc

  • pg_database

  • pg_language

  • pg_type

  • pg_collation

  • pg_depend

  • pg_sequence

  • pg_constraint

  • pg_authid

  • pg_auth_members

Databases

  • The user must also be able to connect to all databases that you want onboarded.

Note that visibility of entries in the pg_catalog tables will depend on if the user has SELECT access to the table, so make sure SELECT is granted to the <kada user> for all tables within the database. You may need to re-apply this grant if schemas are dropped, you may also wish to apply a default grant on the schema so future tables can be visible.

GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <kada user>

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> public GRANT SELECT ON TABLES TO <kada user>

Step 2) Connecting K to Postgres

  • Select Platform Settings in the side bar

  • In the pop-out side panel, under Integrations click on Sources

  • Click Add Source and select Postgres

  • Select Direct Connect and add your Postgres details and click Next

  • Fill in the Source Settings and click Next

    • Name: The name you wish to give your Postgres Instance in K

    • Host: Add your Postgres host (found in your Postgres URL)

      • Omit the https:// from the URL

 

  • Add the Connection details and click Save & Next when connection is successful

    • Host: Use the same details you previously added in the Host setting

    • Username: Add the Postgres user name you created in Step 1

    • Password: Add the Postgres user password you created in Step 1

  • Test your connection and click Save

  • Select the Databases you wish to load into K and click Finish Setup

    • All databases will be listed. If you have a lot of databases this may take a few seconds to load

  • Return to the Sources page and locate the new Postgres source that you loaded

  • Click on the clock icon to select Edit Schedule and set your preferred schedule for the Snowflake load

Note that scheduling a source can take up to 15 minutes to propagate the change.

Step 3) Manually run an ad hoc load to test Postgres setup

  • Next to your new Source, click on the Run manual load icon

  • Confirm how your want the source to be loaded

  • After the source load is triggered, a pop up bar will appear taking you to the Monitor tab in the Batch Manager page. This is the usual page you visit to view the progress of source loads

     

A manual source load will also require a manual run of

  • DAILY

  • GATHER_METRICS_AND_STATS

To load all metrics and indexes with the manually loaded metadata. These can be found in the Batch Manager page