Skip to main content

Replicating PostgreSQL tables to MotherDuck

This page will serve to show basic patterns for using Python to connect to PostgreSQL using the postgres_scanner, connect to MotherDuck, and then write the data from PostgreSQL into MotherDuck. For more complex replication scenarios, please take a look at our ingestion partners.

If you are looking for the pg_duckdb extension, head on over to the pg_duckdb explainer page.

To skip the documentation and look at the entire script, expand the element below:

SQL script
-- install pg extension in DuckDB
INSTALL postgres;
LOAD postgres;

-- attach pg as pg_db
ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS pg_db (TYPE POSTGRES, READ_ONLY);

-- connect to MotherDuck
ATTACH 'md:my_db';

-- insert data into MotherDuck
CREATE OR REPLACE TABLE my_db.main.postgres_table AS
SELECT * FROM pg_db.public.some_table

Loading the PostgreSQL Exentsion & Authenticating​

info

MotherDuck does not yet support the PostgreSQL and MySQL extensions, so you need to perform the following steps on your own computer or cloud computing resource. We are working on supporting the PostgreSQL extension on the server side so that this can happen within the MotherDuck app in the future with improved performance.

The first step to connect to Postgres is to install & load the postgres extension using the DuckDB CLI:

INSTALL postgres;
LOAD postgres;

Once this is completed, you can connect to postgres by attaching it to your duckdb session:

ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS pg_db (TYPE POSTGRES, READ_ONLY);

More detailed information can be found on the DuckDB documentation.

Connecting to MotherDuck & inserting the table​

Once you are connected to your postgres database, you need to connect to MotherDuck. To learn more about authentication, go here.

ATTACH 'md:my_db';

Once you have authenticated, you can execute CTAS in SQL to replicate data from postgres into MotherDuck.

CREATE OR REPLACE TABLE my_db.main.postgres_table AS
SELECT * FROM pg_db.public.some_table

Congratulations! You have now replicated data from Postgres into MotherDuck.

Handling More Complex Workflows​

Production use cases tend to be much more complex and include things like incremental builds & state management. In those scenarios, please take a look at our ingestion partners, which includes many options including some that offer native python. An overview of the MotherDuck Ecosystem is shown below.

Diagram