DOCSEARCHbyAlgolia
Submit Docs

DuckDB Postgres Extension

repository·main·Indexed 18 days ago

https://github.com/duckdb/duckdb-postgres

The DuckDB Postgres extension enables DuckDB to directly read from and write to Postgres database instances using standard SQL commands like ATTACH. It supports libpq connection strings, connection pooling for transaction-mode poolers like PgBouncer, and AWS RDS/Aurora IAM-based authentication. The extension provides the PostgresConnection class for managing connection lifecycles, executing queries, and handling data transfers via the COPY protocol.

Tokens
1.8K
Snippets
5
Records
8
Agent score
13%

What's inside duckdb-postgres

  1. Build and load the Postgres extension from source

    main

    To build the extension, ensure the DuckDB submodule is initialized, then use make. To run the built extension, use the bundled duckdb shell and LOAD the extension file manually.

    # Initialize submodules
git submodule init
git pull --recurse-submodules

# Build
make

# Run DuckDB (allowing unsigned extensions)
./build/release/duckdb -unsigned

# Inside DuckDB shell, load the extension
LOAD 'build/release/extension/postgres_scanner/postgres_scanner.duckdb_extension';

  2. Read data from Postgres using ATTACH

    main

    To access a Postgres database from DuckDB, use the ATTACH command with the TYPE postgres specification. The command accepts a libpq connection string consisting of key=value pairs. Once attached, Postgres tables can be queried directly from DuckDB as if they were native DuckDB tables, with data being read from Postgres at query time.

    ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE postgres);

  3. Use AWS RDS IAM Authentication

    main

    The extension supports AWS RDS/Aurora IAM-based authentication, allowing connections without static passwords by generating temporary tokens via the AWS SDK.

    Requirements

    • RDS instance with IAM database authentication enabled.
    • IAM user/role with rds-db:connect permission.
    • AWS credentials configured (via AWS_PROFILE, AWS_ACCESS_KEY_ID/AWS_SECRET_ACCESS_KEY, or IAM role).

    Usage

    Create a Postgres secret with AWS_RDS_IAM_AUTH_ENABLED set to TRUE, then ATTACH using that secret.

    Secret Parameters

    ParameterTypeRequiredDescription
    HOSTVARCHARYesRDS/Aurora instance hostname
    PORTVARCHARYesRDS/Aurora instance port (typically 5432)
    USERVARCHARYesIAM database username
    AWS_RDS_IAM_AUTH_ENABLEDBOOLEANYesEnable RDS IAM authentication
    AWS_RDS_IAM_TOKEN_EXPIRATION_SECONDSBIGINTNoToken expiration in seconds (default: 900)
    AWS_REGIONVARCHARYesAWS region

    Implementation Details

    • Token Caching: To minimize AWS SDK calls, the extension caches tokens for AWS_RDS_IAM_TOKEN_EXPIRATION_SECONDS - 60 seconds.
    • Credentials: Uses the default AWS SDK credential provider chain; these are not currently configurable.
    CREATE SECRET rds_secret (
    TYPE POSTGRES,
    HOST 'my-db-instance.xxxxxx.us-west-2.rds.amazonaws.com',
    PORT '5432',
    USER 'my_iam_user',
    DATABASE 'postgres',
    SSLMODE 'require',
    AWS_RDS_IAM_AUTH_ENABLED TRUE,
    AWS_REGION 'us-west-2'
);

ATTACH '' AS rds_db (TYPE POSTGRES, SECRET rds_secret);

  4. Configure connection pooling for transaction-mode poolers

    main

    If you are connecting through a transaction-mode pooler like PgBouncer (pool_mode=transaction), the extension might exceed the pooler's capacity under load. To prevent hanging, you can cap the connections and set the acquisition mode to wait.

    Important: SET statements for pooling must be executed before the ATTACH command. To modify pooling for an already attached database, use the postgres_configure_pool(catalog_name=...) function.

    SET pg_pool_acquire_mode = 'wait';
SET pg_pool_max_connections = 16; -- Recommended: ~80-90% of your pooler's pool size

ATTACH 'host=pgbouncer-host port=6432 dbname=mydb' AS postgres_db (TYPE postgres);

  5. Configure Postgres connection parameters

    main

    The ATTACH command uses standard libpq connection string parameters. Common parameters include:

    NameDescriptionDefault
    hostName of host to connect tolocalhost
    hostaddrHost IP addresslocalhost
    portPort Number5432
    userPostgres User Name[OS user name]
    passwordPostgres Password
    dbnameDatabase Name[user]
    passfileName of file passwords are stored in~/.pgpass

    Example connection string: host=localhost port=5432 dbname=mydb connect_timeout=10

  6. Get Postgres version and index information

    main

    The PostgresConnection class provides utility methods to inspect the connected PostgreSQL instance.

    • GetPostgresVersion(ClientContext &context): Returns a PostgresVersion object representing the version of the connected PostgreSQL server.
    • GetIndexInfo(const string &table_name): Returns a vector<IndexInfo> containing metadata about the indexes associated with the specified table.

  8. Manage Postgres connections with PostgresConnection

    main

    The PostgresConnection class is the primary interface for interacting with a PostgreSQL database. It manages the lifecycle of a connection and provides methods for executing queries, handling data copies, and inspecting the server state.

    Key Operations

    • Opening a connection: Use PostgresConnection::Open(dsn, attach_path) to establish a new connection using a Data Source Name (DSN).
    • Executing queries:
      • Execute(...): Runs a query without returning a result set.
      • Query(...): Runs a query and returns a PostgresResult.
      • TryQuery(...): Similar to Query, but allows providing an optional error message pointer.
      • ExecuteQueries(...): Submits a batch of queries for execution.
    • Connection Management:
      • IsOpen(): Checks if the connection is active.
      • Close(): Closes the connection.
      • PingServer(health_check_query): Verifies connectivity by running a specific query.
      • Reset(health_check_query): Resets the connection state.
    • Data Transfer (COPY protocol):
      • BeginCopyTo(...): Initiates a COPY TO operation to stream data from Postgres to DuckDB.
      • CopyData(...): Sends data chunks using binary or text writers.
      • BeginCopyFrom(...): Initiates a COPY FROM operation to stream data from DuckDB to Postgres.
    // Example of opening a connection and executing a query
auto conn = PostgresConnection::Open("dbname=test user=postgres", "/path/to/attach");
conn.Execute(nullptr, "CREATE TABLE test (id INTEGER)");
auto result = conn.Query(nullptr, "SELECT * FROM test");