How to Redshift open source in PostgreSQL

How do I query Amazon Redshift from PostgreSQL with open-source tools?

Use an open-source foreign data wrapper (FDW) to query Amazon Redshift directly from PostgreSQL.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use an open-source Redshift FDW?

Querying Redshift from PostgreSQL eliminates ETL lag, lets developers join Redshift data with local tables, and keeps analytics in one place.

Which open-source FDW works with Redshift?

Most teams adopt the postgres_fdw or community odbc_fdw.Both are free, maintained, and installable via CREATE EXTENSION.

How do I install the FDW?

Install the OS package (e.g., apt-get install postgresql-15-odbc-fdw) or compile from GitHub, then run CREATE EXTENSION in your database.

What roles and permissions are required?

A superuser or a role with CREATE privilege on the current database can create the server and user mapping. The Redshift user needs read access on target schemas.

When should I import versus manually create foreign tables?

Use IMPORT FOREIGN SCHEMA for dozens of tables.For a hand-picked subset, manually write CREATE FOREIGN TABLE definitions to control column names and data types.

Best practice: keep connections pooled

Set FDW keep_connections to on to avoid reconnect overhead and throttle limits on Redshift.

Best practice: push down predicates

Add filters in the SELECT statement so the FDW sends them to Redshift, reducing data movement.

Why How to Redshift open source in PostgreSQL is important

How to Redshift open source in PostgreSQL Example Usage


-- Join local Products with Redshift Orders to compute stock-out risk
SELECT p.id,
       p.name,
       p.stock,
       COALESCE(SUM(o.total_amount),0) AS lifetime_revenue
FROM   Products p
LEFT JOIN redshift_ext.Orders o
       ON o.product_id = p.id
GROUP  BY p.id, p.name, p.stock
ORDER  BY lifetime_revenue DESC;

How to Redshift open source in PostgreSQL Syntax


-- 1. Enable extension
CREATE EXTENSION IF NOT EXISTS odbc_fdw;

-- 2. Create foreign server pointing at Redshift
CREATE SERVER redshift_srv
  FOREIGN DATA WRAPPER odbc_fdw
  OPTIONS (
    driver 'Amazon Redshift (x64)',
    database 'ecom_dw',
    host 'redshift-cluster.abc123.us-east-1.redshift.amazonaws.com',
    port '5439'
  );

-- 3. Map a Postgres role to a Redshift user
CREATE USER MAPPING FOR app_user
  SERVER redshift_srv
  OPTIONS (
    username 'rs_readonly',
    password '********'
  );

-- 4. Import Redshift tables into a local schema
IMPORT FOREIGN SCHEMA public
  FROM SERVER redshift_srv
  INTO redshift_ext
  OPTIONS (import_default 'true');

Common Mistakes

Forgetting to install the matching ODBC driver. Without the Amazon Redshift ODBC driver, <code>odbc_fdw</code> fails at connection time. Install the driver and restart Postgres.
Selecting <code>*</code> from wide Redshift tables. This pulls full datasets across the network. Always project only needed columns and add WHERE filters to leverage predicate pushdown.