What is Postgres?

Postgres is an advanced open-source relational database management system known for its robustness, extensibility, and standards compliance. It supports complex queries, data integrity, and concurrency control, making it ideal for high-demand applications.

Why Connect Postgres to Monte Carlo?

Integrating Monte Carlo with your Postgres database allows you to monitor your data and tables. Monte Carlo can detect anomalous volume changes out of the box. With the ability to run SQL queries on your tables you can create advanced monitors to analyze the data in your tables. For a full list of supported monitor types see Monitor Support.

After connecting Postgres here is what you can expect to see out of the box:

Volume

Our volume monitors are active out of the box. By collecting metadata on your tables each hour, Monte Carlo's machine learning algorithm can set automatic thresholds to alert when a table has gone too long without a change in size or when an abnormal amount of rows are added or deleted.

Monitor & Lineage Support

Below are the monitors & lineage support for the Postgres Integration today. Please reach out to your Monte Carlo representative if you have requirements for additional monitors.

Connecting to Postgres

Now that we know the value connecting Postgres to Monte Carlo can bring, here are the necessary steps to set up the integration.

📘

Prerequisites

To create a service account, you will need admin credentials to your Postgres database.

This guide explains how to create a read-only service account for Monte Carlo in your Postgres database.

To review all steps necessary to integrate a database with Monte Carlo, please see here.

Create a read-only service account

Please run the SQL snippet below on your Postgres database to create a read-only service account for Monte Carlo

CREATE ROLE montecarlo WITH LOGIN PASSWORD '<PASSWORD>';

-- Grant access to all current tables. Repeat for all relevant schemas (e.g. public, etc.)
-- Select permissions are necessary to create custom SQL monitors.
-- To get a list of all schemas available:
---- SELECT schema_name FROM information_schema.schemata;

GRANT USAGE ON SCHEMA <schema> TO montecarlo;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO montecarlo;

-- Grant access to all future tables. Repeat for all relevant users who create tables in each schema.
-- Select permissions are necessary to create custom SQL monitors.
-- To get a list of all users available:
---- SELECT usename FROM pg_catalog.pg_user;

ALTER DEFAULT PRIVILEGES FOR ROLE <creating_user> IN SCHEMA <schema> GRANT SELECT ON TABLES TO montecarlo;

Provide service account credentials to Monte Carlo

In the settings page of the Monte Carlo UI, when using Monte Carlo's onboarding wizard, please provide the details created about to validate and complete the integration:

• Integration Name: Friendly name for your new connection, which will be referenced and shown throughout the Monte Carlo UI. For example, postgres_prod.
• User and Password: Please provide the credentials you chose while creating the service account.
• Host: The endpoint of your instance.
• Port: The port used to networking into your instance.
• Database: Please provide the name of the database.

Alternatively, we also support onboarding through our CLI:

1. Please follow this guide to install and configure the CLI.
2. Please use the command montecarlo integrations add-postgres to connect. For reference, see help here.

montecarlo integrations add-postgres \
	--host example.cluster-123456789.us-east-1.rds.amazonaws.com \
	--user montecarlo \
	--password -1 \
	--database sample \
	--name artemis-dev

FAQs

What is the minimum supported version?

Monte Carlo's metadata collection supports Postgres 10 and newer. Versions lower than 10 but 9.4+ could also work, although not as thoroughly tested.

For metric monitors, some of the validity metric types like SSN (%), USA phone number (%), USA ZIP code (%) and Email (%) use a Postgres function that is supported in Postgres 15 and newer. If you are using a version that predates 15, your metric monitor execution will fail if you use these metric types. You will still be able to create a metric monitor using any of the other metric types.

Are cloud variants of Postgres like AWS Aurora, GCP Cloud SQL, and Azure Database supported?

Yes, AWS Aurora, GCP Cloud SQL, and Azure Database for Postgres are all supported.

Are other forks or variants supported?

Variants like Greenplum, Supabase or others may work -- contact us.

What hosting solutions are supported?
This integration supports any instance that is reachable from one of our hosting options. Please see the networking docs for more detail.

How do I list all schemas (for setup)?

Run the following SQL snippet:

SELECT schema_name FROM information_schema.schemata;

How do I list all users (for setup)?

Run the following SQL snippet:

SELECT usename FROM pg_catalog.pg_user;