Integrations

Postgres to Petals Data Source Integration

Updated: 06 Mar 2025

Overview

This guide walks you through connecting your PostgreSQL database to Petals, enabling data extraction for analysis within the Petals-managed data warehouse.

Important Notes

  • This integration extracts data from your PostgreSQL database and moves it into Petals’ managed Postgres environment for analysis.

  • If you prefer to connect your own Postgres database directly , you can configure this within the Administration Panel instead.

Step 1: Add a New PostgreSQL Integration in Petals

  1. Navigate to Data Sources inside the Petals application.

  2. New Integration in the top-right corner

  3. Select PostgreSQL Database from the available integration options.

Step 2: Provide Your PostgreSQL Database Information

Once you've selected PostgreSQL Database, you'll need to input the following credentials:

  • Host: The server address of your database (see below for instructions on retrieving your host).

  • Port: Default PostgreSQL port is 5432, unless configured otherwise.

  • Database Name: The name of the database you want to connect.

  • Schemas: The schemas Petals should access.

  • Username: A dedicated read-only user for Petals.

  • Password: Corresponding password for the above user.

Step 3: Creating a Read-Only User for Petals

To ensure secure access, it’s recommended to create a dedicated read-only PostgreSQL user for Petals.

Creating a Read-Only User

Run the following SQL commands in your PostgreSQL database:

  1. Create a new read-only user

    • CREATE USER petals_readonly WITH PASSWORD 'your_secure_password';

  2. Grant the user read-only access to the database

    • GRANT CONNECT ON DATABASE your_database TO petals_readonly;

    • GRANT CREATE, TEMPORARY ON DATABASE your_database TO petals_readonly;

  3. Grant access to specific schemas

    • GRANT USAGE ON SCHEMA your_schema TO petals_readonly;

  4. Grant SELECT privileges on all existing tables

    • GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA your_schema TO petals_readonly;

  5. Ensure the user has access to future tables

    • ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema

    • GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO petals_readonly;

Alternatively, if you already have an existing user with read permissions, you can use that.

Step 4: Retrieving Your PostgreSQL Hostname

Your PostgreSQL hostname is the address used to connect to your database. You can find it using one of the following methods:

For Cloud-Hosted Databases (e.g., AWS RDS, Google Cloud SQL, Azure)

  1. Log in to your cloud provider's database console.

  2. Locate your PostgreSQL instance

  3. Find the host address (e.g., your-database-instance.region.cloudprovider.com).

For Self-Hosted PostgreSQL

  1. Run the following command inside your server’s terminal:

    shCopyEdithostname -I

    This will return the IP address of your server.

  2. If the database is running on a different machine, check your database’s

    PostgreSQL configuration file (postgresql.conf) to confirm the bind address.

Finalizing the Integration

  • After inputting all required fields, test the connection to ensure Petals can successfully extract data.

  • Once verified, click Save Integration to complete the setup.

Need Help?

If you encounter any issues, refer to your database administrator or contact Petals Support for troubleshooting assistance.