Skip to main content

PostgreSQL Integration for Nodus

1. Introduction to the PostgreSQL Integration​

What Is This Integration?​

The PostgreSQL integration connects your PostgreSQL database with Nodus, allowing you to extract, analyze, and transform data from PostgreSQL tables and views. This integration functions as a source connector, bringing your PostgreSQL data into the Nodus ecosystem for advanced analytics and business intelligence.

Prerequisites:​

  • An active PostgreSQL database server (version 9.4 or higher)
  • Database credentials with appropriate read permissions
  • Network access to your PostgreSQL server (proper security group settings, firewall rules, or SSH tunneling)
  • Optional: SSH tunnel access if your database is not directly accessible

Connection Overview:​

The integration uses PostgreSQL's SQL API to extract data and perform queries. Authentication is handled through standard database credentials, and data is extracted based on user-defined configurations that specify which schemas and tables to retrieve.

2. Platform Setup Documentation (Setup Form for PostgreSQL)​

Purpose & Scope​

This section covers how to set up the initial connection between Nodus and PostgreSQL by providing the necessary authentication credentials and server details.

Field-by-Field Breakdown:​

Integration Name​

  • Field Name & Label: Integration Name
  • Description & Purpose: A descriptive name to identify this PostgreSQL integration within your Nodus account.
  • Validation Rules & Format: Text string, required field.
  • Examples: "Production PostgreSQL", "Analytics Database"
  • Troubleshooting Tips: Use a descriptive name that clearly identifies the specific PostgreSQL database or purpose.

Host​

  • Field Name & Label: Host
  • Description & Purpose: The hostname or IP address of your PostgreSQL server.
  • Validation Rules & Format: Text string, required field.
  • Examples: "dexample.com", "postgresql.internal", "10.0.0.5"
  • Troubleshooting Tips: If you're using a cloud database service, use the endpoint provided by your cloud provider. For local development, you might use "localhost".

Port​

  • Field Name & Label: Port
  • Description & Purpose: The port number used to connect to your PostgreSQL server.
  • Validation Rules & Format: Numeric string, required field.
  • Examples: "5432" (the default PostgreSQL port)
  • Troubleshooting Tips: The default port for PostgreSQL is 5432, but this can be customized during installation.

Database​

  • Field Name & Label: Database
  • Description & Purpose: The name of the PostgreSQL database you want to connect to.
  • Validation Rules & Format: Text string, required field.
  • Examples: "analytics", "production", "customer_data"
  • Troubleshooting Tips: The database must already exist on your PostgreSQL server. Case-sensitive in most PostgreSQL installations.

Username​

  • Field Name & Label: Username
  • Description & Purpose: The username used to authenticate with your PostgreSQL server.
  • Validation Rules & Format: Text string, required field.
  • Examples: "postgres", "analytics_user", "readonly_user"
  • Troubleshooting Tips: For security best practices, consider creating a dedicated read-only user for Nodus connections.

Password​

  • Field Name & Label: Password
  • Description & Purpose: The password associated with the username.
  • Validation Rules & Format: Text string, required field, masked for security.
  • Troubleshooting Tips: Passwords are case-sensitive. Ensure the password is correctly entered.

SSH Authentication (Optional)​

These fields are required only if you need to connect to your PostgreSQL database through an SSH tunnel:

SSH Tunnel Jump Server Name​

  • Field Name & Label: SSH Tunnel Jump Server Name
  • Description & Purpose: The hostname or IP address of the SSH server that will be used as a tunnel to your PostgreSQL server.
  • Validation Rules & Format: Text string, optional field.
  • Examples: "jump.example.com", "22.33.44.55"
  • Troubleshooting Tips: This is the server that will act as an intermediary between Nodus and your PostgreSQL server.

SSH Tunnel Port​

  • Field Name & Label: SSH Tunnel Port
  • Description & Purpose: The port number used to connect to the SSH server.
  • Validation Rules & Format: Numeric string, optional field.
  • Examples: "22" (the default SSH port)
  • Troubleshooting Tips: The default port for SSH is 22, but this can be customized by your network administrator.

SSH Tunnel Username​

  • Field Name & Label: SSH Tunnel Username
  • Description & Purpose: The username used to authenticate with the SSH server.
  • Validation Rules & Format: Text string, optional field.
  • Examples: "ssh_user", "ec2-user", "ubuntu"
  • Troubleshooting Tips: This is the operating system user on the SSH server, not the PostgreSQL user.

SSH Tunnel Private Key​

  • Field Name & Label: SSH Tunnel Private Key
  • Description & Purpose: The private SSH key used to authenticate with the SSH server.
  • Validation Rules & Format: Text string (multiline), optional field.
  • Examples: RSA private key in PEM format
  • Troubleshooting Tips: The key should be in RSA PEM format, typically created with ssh-keygen -t rsa -m PEM -f myuser_rsa.

Step-by-Step Guide:​

  1. Prepare your PostgreSQL environment:

    • Ensure you have a PostgreSQL database that you want to extract data from
    • Create a dedicated user with appropriate read permissions
    • Configure network access to allow connections from Nodus

  2. Create a dedicated PostgreSQL user for Nodus (recommended):

    CREATE USER nodus_user WITH PASSWORD 'secure_password';
    GRANT USAGE ON SCHEMA public TO nodus_user;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO nodus_user;

  3. Enter your integration details in the Nodus setup form:

    • Provide a meaningful Integration Name
    • Enter your PostgreSQL Host and Port
    • Enter the Database name
    • Enter your Username and Password

  4. If using SSH tunneling, provide the SSH details:

    • SSH Tunnel Jump Server Name
    • SSH Tunnel Port
    • SSH Tunnel Username
    • SSH Tunnel Private Key

  5. Save the configuration and test the connection

3. Extraction/Query Configuration (Extraction Form for PostgreSQL)​

Purpose & Overview​

This section explains how to configure data extraction from PostgreSQL. The form allows you to select specific schemas and tables, define extraction strategies, and specify column filters for optimized data extraction.

Template & Field Documentation:​

Source Schema and Table Selection​

  • Field Name & Label: Source Schema / Source Table
  • Description & Purpose: Defines which schema and table to extract data from
  • Validation Rules & Format: Cascading dropdown selection, required field
  • Troubleshooting Tips: The schema list is populated with available schemas in your PostgreSQL database, and the table list is populated based on the selected schema.

Destination Configuration​

  • Field Name & Label: Final Schema / Final table name
  • Description & Purpose: Defines where extracted data will be stored in the Nodus data warehouse
  • Validation Rules & Format: Schema dropdown and table name input, required fields
  • Troubleshooting Tips: Choose an existing schema or create a new one, and provide a name for the destination table where your data will be loaded.

Write Strategy​

  • Field Name & Label: Write Strategy
  • Description & Purpose: Defines how data should be written to the destination
  • Validation Rules & Format: Radio button selection, required field
  • Available Options:
    • Upsert - Update existing records and insert new ones
    • Append - Add new records to the existing table
    • Replace - Drop and recreate the table with new data
  • Troubleshooting Tips: Choose the appropriate strategy based on your use case. Upsert requires a primary key.

Workflow & Examples:​

  1. Select the source schema and table from the cascading dropdowns
  2. Select a destination schema and enter a table name
  3. Choose a write strategy
  4. Execute extraction

Example Use Cases:​

User Data Analysis:

  • Source Schema: public
  • Source Table: users
  • Write Strategy: Append

Product Inventory Synchronization:

  • Source Schema: inventory
  • Source Table: products
  • Write Strategy: Upsert (based on product_id)

5. Troubleshooting & FAQs for PostgreSQL​

Common Issues & Error Messages​

Authentication Failures​

  • Error: "FATAL: password authentication failed for user"
  • Solution: Verify your username and password. Check if the user account exists in PostgreSQL and that the password is correct.

Connection Issues​

  • Error: "Could not connect to server: Connection refused"
  • Solution: Check your network settings. Ensure that your PostgreSQL server is running and that the host and port are correct. Verify that firewalls or security groups allow connections from Nodus to your PostgreSQL server.

Permission Problems​

  • Error: "Permission denied for schema" or "Permission denied for table"
  • Solution: Ensure that the user has the necessary permissions on the schema and tables you're trying to access. Grant SELECT privileges on the required objects.
-- Grant schema permissions
GRANT USAGE ON SCHEMA schema_name TO username;
-- Grant table permissions
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

SSH Tunnel Issues​

  • Error: "Failed to establish SSH connection" or "SSH authentication failed"
  • Solution: Verify your SSH credentials and connection details. Ensure that the SSH server is accessible and that the private key is in the correct format (RSA PEM).

Schema or Table Not Found​

  • Error: "Relation does not exist" or "Schema does not exist"
  • Solution: Verify that the schema and table names exist in your PostgreSQL database. PostgreSQL identifiers are case-sensitive unless created with double quotes.

Logging & Diagnostic Tools​

  • PostgreSQL server logs can provide detailed information about connection attempts and queries
  • Enable verbose logging in Nodus to capture API request/response details
  • Use the psql command-line tool to test connectivity and queries directly from a terminal

Contact & Support Information​

Best Practices for PostgreSQL Connections​

  • Create a dedicated read-only user for Nodus integrations
  • Use connection pooling for better performance with many concurrent connections
  • Consider using SSH tunneling if your database is not publicly accessible
  • Keep your PostgreSQL server updated with security patches
  • Regularly monitor and vacuum your tables to maintain performance