# Heroku Postgres
> Load data from Heroku Postgres into MotherDuck with DuckDB's PostgreSQL extension, using a read-only credential or a follower database.
Heroku Postgres is managed PostgreSQL offered as a Heroku add-on. Because it's Postgres on the wire, DuckDB's PostgreSQL extension attaches it like any other Postgres database, and everything on [Loading data from Postgres](/key-tasks/loading-data-into-motherduck/loading-data-from-postgres) applies.

## Attach a Heroku database

Get the connection URL from the Heroku CLI, or from the **Credentials** tab of the database in the Heroku Dashboard:

```bash
heroku pg:credentials:url DATABASE -a <app_name>
```

The URL uses the `postgres://` scheme, which the extension accepts as-is. Heroku requires TLS on every connection, so add `sslmode=require` to the string:

```sql
INSTALL postgres;
LOAD postgres;
ATTACH 'md:';

ATTACH 'postgres://<user>:<password>@<host>:5432/<database>?sslmode=require'
    AS heroku_db (TYPE postgres, READ_ONLY);

CREATE DATABASE IF NOT EXISTS analytics;

CREATE TABLE analytics.main.orders AS
SELECT * FROM heroku_db.public.orders;
```

Attaching read-only is a good default: it makes accidental writes back to your application database impossible.

To avoid putting credentials in the `ATTACH` string, store them in a secret first:

```sql
CREATE SECRET heroku_secret (
    TYPE POSTGRES,
    HOST '<host>',
    PORT 5432,
    DATABASE '<database>',
    USER '<user>',
    PASSWORD '<password>'
);

ATTACH '' AS heroku_db (TYPE postgres, SECRET heroku_secret, READ_ONLY);
```

## Use a read-only credential and a follower

The default credential owns the database. On Standard, Premium, Private, and Shield plans, you can give MotherDuck its own credential with `SELECT` access only, so the load can't change application data:

```bash
heroku pg:credentials:create DATABASE --name motherduck_reader -a <app_name>
```

A new credential can only connect. Grant it read access from `heroku pg:psql`:

```sql
GRANT USAGE ON SCHEMA public TO motherduck_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO motherduck_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO motherduck_reader;
```

The same plans support [follower databases](https://devcenter.heroku.com/articles/heroku-postgres-follower-databases), which are read-only replicas. Point the load at a follower so a large scan doesn't compete with application traffic on the leader. A follower has its own config var, such as `HEROKU_POSTGRESQL_IVORY_URL`, so switching is a connection-string change.

Essential plans have only the owner credential and no followers.

## Load on a schedule with a Flight

For a recurring load, run the same extract from a [Flight](/concepts/flights) so it executes on MotherDuck compute on a cron rather than on a machine you maintain. The [Postgres ingest Flight recipe](/cookbook/flight-postgres-ingest) is a ready-made starting point: point it at your Heroku host and store the credentials as a Flight secret.

The Flight is the same extract wrapped in an entrypoint. Attaching with an empty connection string keeps the password in the environment rather than in a SQL statement:

```python
import duckdb

def main():
    con = duckdb.connect("md:")
    con.execute("INSTALL postgres; LOAD postgres;")
    con.execute("ATTACH '' AS heroku_db (TYPE postgres, READ_ONLY)")
    con.execute(
        "CREATE OR REPLACE TABLE analytics.main.orders AS "
        "SELECT * FROM heroku_db.public.orders"
    )

if __name__ == "__main__":
    main()
```

Store the connection as a [Flight secret](/sql-reference/motherduck-sql-reference/create-secret#flight-secrets) named after the libpq environment variables, so the extension picks them up with no mapping:

```sql
CREATE SECRET heroku_creds IN MOTHERDUCK (
    TYPE FLIGHTS,
    PARAMS MAP {
        'PGHOST': '<host>',
        'PGPORT': '5432',
        'PGDATABASE': '<database>',
        'PGUSER': '<user>',
        'PGPASSWORD': '<password>',
        'PGSSLMODE': 'require'
    }
);
```

Create the Flight with [`MD_CREATE_FLIGHT`](/sql-reference/motherduck-sql-reference/flights/md-create-flight), passing that secret in `flight_secret_names` and a `schedule_cron` for the cadence you want.

For append-only tables, extract incrementally with a watermark instead of reloading the table. See [Data loading patterns](/key-tasks/loading-data-into-motherduck/loading-patterns).

## Things to know

- **Credentials can change.** A failover or credential rotation updates `DATABASE_URL` on your Heroku apps, but not the copy in your Flight secret. When a scheduled load starts failing authentication, fetch the current URL and update the secret with `CREATE OR REPLACE SECRET`.
- **Private and Shield databases aren't reachable from outside their space.** Connecting from MotherDuck needs one of the external access options in [Heroku Postgres and Private Spaces](https://devcenter.heroku.com/articles/heroku-postgres-and-private-spaces).
- **`pg_duckdb` is not available on Heroku.** The [pg_duckdb](/concepts/pgduckdb) route, where Postgres itself connects out to MotherDuck, needs the extension installed server-side, and it isn't on Heroku's [supported extensions](https://devcenter.heroku.com/articles/heroku-postgres-extensions-postgis-full-text-search) list. Use the DuckDB-side `ATTACH` above instead.
- **Row-by-row transfer has a ceiling.** The `postgres` extension is well-suited to one-time loads and backfills. For continuous replication of a busy table, use a change-data-capture tool such as [Estuary](/integrations/ingestion/estuary) or [Streamkap](/integrations/ingestion/streamkap).

## Related content

- [PostgreSQL](/integrations/databases/postgres)
- [Loading data from Postgres](/key-tasks/loading-data-into-motherduck/loading-data-from-postgres)
- [Ingest Postgres tables into MotherDuck from a Flight](/cookbook/flight-postgres-ingest)
- [Connecting to Heroku Postgres](https://devcenter.heroku.com/articles/connecting-heroku-postgres)


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fintegrations%2Fdatabases%2Fheroku%2F&page_title=Heroku%20Postgres&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.