datus-postgresql

PostgreSQL database adapter for Datus.

Timeouts

timeout_seconds must be positive and now bounds driver I/O as well as pool checkout where applicable. Long operations may need a larger value. Server execution deadlines and supported overrides are described in the shared timeout policy and real-database validation. A socket/RPC timeout is distinct from an overall operation deadline.

Amazon Aurora PostgreSQL

Aurora uses this adapter with the PostgreSQL dialect. Set aurora=True and provide sslrootcert; the adapter requires sslmode=verify-full. Password authentication needs no AWS SDK. Install pip install 'datus-postgresql[aws]' for IAM or Secrets Manager.

import os
from datus_postgresql import PostgreSQLConnector

connector = PostgreSQLConnector({
    "host": os.environ["AURORA_POSTGRESQL_ENDPOINT"],
    "username": os.environ["AURORA_DB_USER"],
    "database": os.environ["AURORA_DATABASE"],
    "aurora": True,
    "auth_mode": "iam",
    "aws": {"region": os.environ["AWS_REGION"]},
    "sslrootcert": os.environ["AURORA_CA_BUNDLE"],
})
try:
    result = connector.execute_query("SELECT 1", result_format="list")
    assert result.success, result.error
finally:
    connector.close()

TLS and dynamic credentials apply to the base engine and every cached database engine. See Aurora authentication and lifecycle for the full contract, IAM permissions/user setup, Role profiles, Secret rotation and pending cloud acceptance. From the workspace root, uv run --no-project python -m ci.run_tls_integration_tests postgresql runs verified TLS, multiple databases, shared contracts and wrong-CA/hostname rejection.

Overview

This adapter provides PostgreSQL connectivity for Datus, supporting full SQL operations, metadata discovery, schema management, and materialized views via the standard PostgreSQL protocol.

Installation

pip install datus-postgresql

This will automatically install the required dependencies:

• datus-agent
• datus-sqlalchemy (which includes SQLAlchemy and psycopg2)

Usage

The adapter is automatically registered with Datus when installed. Configure your database connection:

namespace:
  postgresql_prod:
    type: postgresql
    host: localhost
    port: 5432
    username: postgres
    password: your_password
    database: mydb
    schema: public

Or use programmatically:

from datus_postgresql import PostgreSQLConnector, PostgreSQLConfig

# Using config object
config = PostgreSQLConfig(
    host="localhost",
    port=5432,
    username="postgres",
    password="password",
    database="mydb",
    schema_name="public",
)

connector = PostgreSQLConnector(config)

# Or using dict
connector = PostgreSQLConnector({
    "host": "localhost",
    "port": 5432,
    "username": "postgres",
    "password": "password",
    "database": "mydb",
})

# Use context manager for automatic cleanup
with connector:
    # Test connection
    connector.test_connection()

    # Execute queries
    result = connector.execute({"sql_query": "SELECT * FROM users LIMIT 10"})
    print(result.sql_return)

    # Get tables
    tables = connector.get_tables(schema_name="public")
    print(f"Tables: {tables}")

    # Get table schema
    columns = connector.get_schema(schema_name="public", table_name="users")
    for col in columns:
        print(f"  {col['name']}: {col['type']}")

    # Get materialized views
    mvs = connector.get_materialized_views(schema_name="public")
    print(f"Materialized Views: {mvs}")

Configuration Options

Features

• Full SQL Support: Execute queries, DDL, DML operations
• Metadata Discovery: Automatic discovery of databases, schemas, tables, views, and materialized views
• DDL Generation: Reconstruct CREATE TABLE / VIEW / MATERIALIZED VIEW statements
• Schema Management: Switch between schemas seamlessly
• Sample Data: Extract sample rows for data profiling
• Connection Management: SQLAlchemy-based connection pooling with SSL support
• Multiple Result Formats: pandas, arrow, csv, list

Code Structure

datus-postgresql/
├── datus_postgresql/
│   ├── __init__.py          # Package exports
│   ├── config.py            # PostgreSQLConfig (Pydantic model)
│   └── connector.py         # PostgreSQLConnector implementation
├── tests/
│   ├── unit/                # Unit tests with mocks (no database needed)
│   └── integration/         # Integration tests (requires PostgreSQL)
│       ├── conftest.py      # Test fixtures and TPC-H data setup
│       ├── test_integration.py  # General integration tests
│       └── test_tpch.py     # TPC-H benchmark data tests
├── scripts/
│   └── init_tpch_data.py    # TPC-H data initialization script
├── docker-compose.yml       # PostgreSQL test container
├── pyproject.toml
└── README.md

Testing

Quick Start

# 1. Start PostgreSQL test container
docker-compose up -d

# 2. Run tests
pytest tests/unit/ -v              # Unit tests (no database needed)
pytest tests/integration/ -v       # Integration tests (requires PostgreSQL)
pytest tests/ -v                   # All tests

Test Types

• Unit tests: Configuration and connector logic with mocks (no database needed)
• Integration tests: Real database operations (SQL, metadata, schema)
• TPC-H tests: Analytical query tests using TPC-H benchmark data

TPC-H Integration Tests

The adapter includes TPC-H benchmark data tests for validating analytical query capabilities.

TPC-H Tables

Running TPC-H Tests

# Start PostgreSQL container
docker-compose up -d

# Run TPC-H integration tests
pytest tests/integration/test_tpch.py -v

# Initialize TPC-H data manually (for ad-hoc testing)
python scripts/init_tpch_data.py \
    --host localhost --port 5432 \
    --username test_user --password test_password \
    --database test --schema public

# Drop and recreate TPC-H tables
python scripts/init_tpch_data.py --drop

Environment Variables

Tests use these default values (matching docker-compose.yml):

Requirements

• Python >= 3.12
• PostgreSQL >= 12
• datus-agent >= 0.2.1
• datus-sqlalchemy >= 0.1.0

License

Apache License 2.0

Related Packages

• datus-sqlalchemy - SQLAlchemy base connector
• datus-mysql - MySQL adapter
• datus-redshift - Amazon Redshift adapter
• datus-snowflake - Snowflake adapter