PostgreSQL Integration

The PostgreSQL integration in Lamatic automates data synchronization from PostgreSQL databases. It enables scheduled fetching of rows from specified tables and supports vectorization and indexing for Retrieval-Augmented Generation (RAG) workflows.

Features

✅ Key Functionalities

• Data Synchronization: Automatically syncs data from PostgreSQL tables and materialized views
• Scheduled Processing: Supports automated sync schedules with cron expressions
• RAG Integration: Enables vectorization and indexing for AI-powered data retrieval
• Flexible Sync Modes: Supports both incremental and full-refresh synchronization

✅ Benefits

• Automates database synchronization processes
• Enables RAG workflows with PostgreSQL data
• Provides granular control over table selection and processing
• Supports both incremental and full-refresh synchronization modes

Available Functionality

Event Triggers

✅ Scheduled data syncing from PostgreSQL tables
✅ Support for tables and materialized views
✅ Schema-specific monitoring and filtering
✅ Incremental and full-refresh sync modes

Actions

✅ Parse and extract data from PostgreSQL rows
✅ Vectorize content for RAG workflows
✅ Filter tables using schema and table names
✅ Schedule automated sync operations

Prerequisites

Before setting up the PostgreSQL integration, ensure you have:

• PostgreSQL database credentials (host, port, database, username, password)
• Access to target database, schemas, and tables
• Understanding of cron expressions for scheduling
• Appropriate database permissions for read operations

Setup

Step 1: Set Up PostgreSQL Credentials

Follow the steps below to properly configure a PostgreSQL connector with the required read-only access.

Create a dedicated read-only Postgres user

It's recommended to use a dedicated read-only user for secure data replication. You may also use an existing user with similar permissions.

Run the following SQL command to create a new user:

CREATE USER <user_name> PASSWORD 'your_password_here';

Then, grant the user read-only access to the desired schemas and tables. Execute the following commands for each schema you intend to replicate data from:

GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <user_name>;

Connection Requirements

1. Database Access Make sure you can connect to the PostgreSQL database.

2. User Permissions Confirm that your database user has sufficient read access to the required schemas and tables.

3. Connection Information Gather the following details for the database connection:

   • Host address
   • Port number (default: 5432)
   • Database name
   • Username and password

Connecting via SSL

Lamatic supports various SSL modes when connecting to PostgreSQL databases.

• disable – Disables encryption entirely, this mode should be used if you are using supabase
• allow – Enables encryption only if the source requires it
• prefer – Uses unencrypted communication unless the source supports encryption
• require – Always encrypts the connection; fails if encryption isn't supported by the source
• verify-ca – Like require, but also verifies the server's SSL certificate
• verify-full – Requires encryption and also verifies the server’s hostname against the certificate

Step 2: Configure PostgreSQL Node

1. Add PostgreSQL Node: Drag the PostgreSQL node to your flow
2. Enter Credentials: Provide your database connection details
3. Configure Schema: Enter the database schema to process
4. Set Table/View: Specify the table or materialized view name

Step 3: Test and Deploy

1. Test Connection: Verify the node can connect to your PostgreSQL database
2. Configure Sync Settings: Set up sync mode, schedule, and table filters
3. Deploy Flow: Activate the flow to start syncing data

Configuration Reference

PostgreSQL Node Parameters

Sync Configuration Options

Sync Modes

# Incremental Sync (recommended)
sync_mode: "incremental"  # Only sync new/modified data
 
# Full Refresh
sync_mode: "full-refresh"  # Re-index all data

Schedule Examples

# Daily at midnight
sync_schedule: "0 0 * * *"
 
# Every 6 hours
sync_schedule: "0 */6 * * *"
 
# Weekdays only at 9 AM
sync_schedule: "0 9 * * 1-5"
 
# Every 30 minutes
sync_schedule: "*/30 * * * *"

Database Configuration

Schema and Table Selection

# Single schema and table
schema: "public"
table: "users"
 
# Multiple schemas (comma-separated)
schemas: "public,analytics,reports"
 
# Multiple tables (comma-separated)
tables: "users,orders,products"

Usage Examples

Basic PostgreSQL Sync

# Basic configuration for syncing user data
credentials: "PostgreSQL Connection"
schema: "public"
table: "users"
sync_mode: "incremental"
sync_schedule: "0 0 * * *"  # Daily at midnight

Advanced Configuration

# Advanced setup with multiple schemas and scheduling
credentials: "PostgreSQL Connection"
schema: "analytics"
table: "user_metrics"
sync_mode: "incremental"
sync_schedule: "0 2 * * *"  # Daily at 2 AM

Materialized View Sync

# Sync data from a materialized view
credentials: "PostgreSQL Connection"
schema: "reports"
table: "daily_summary_mv"  # Materialized view
sync_mode: "full-refresh"  # Full refresh for materialized views
sync_schedule: "0 6 * * *"  # Daily at 6 AM

Troubleshooting

Common Issues

Debugging Steps

1. Verify Credentials: Test your PostgreSQL connection details
2. Check Database Access: Ensure you can connect to the database using a client
3. Validate Schema/Table: Confirm the schema and table exist and are accessible
4. Test Permissions: Verify your user has read permissions on the target objects
5. Check Sync Logs: Review Lamatic Flow logs for detailed error information
6. If the connection fails, whitelist the following IPs: https://www.cloudflare.com/ips/ (opens in a new tab)

Best Practices

• Use incremental sync mode for better performance with large tables
• Schedule syncs during off-peak hours to minimize database impact
• Use materialized views for complex queries that need regular updates
• Implement appropriate database indexes for better sync performance
• Regularly monitor sync logs for any issues
• Consider using read replicas for sync operations to avoid impacting production

Example Use Cases

Data Intelligence Workflows

• User Analytics: Sync user behavior data for AI-powered insights
• Business Metrics: Process sales, inventory, and performance data
• Customer Data: Index customer profiles and interactions for personalized experiences
• Operational Data: Automate access to logs, events, and system metrics

RAG Applications

• Semantic Search: Enable natural language search across database records
• Question Answering: Build AI assistants that can answer questions about business data
• Data Summarization: Automatically summarize large datasets and reports
• Content Discovery: Help users find relevant information across database tables

Related Resources

• PostgreSQL Documentation (opens in a new tab)
• Cron Expression Guide (opens in a new tab)