ducklake catalog backup fails on 'Missing Extension Error'

[macraesdirtysocks]

What happens?

I decided to open an issue and include a reprex. Related to #523. This is my personal.

I am following the ducklake docs to create a backup.

Following the postgres -> duckdb instructions verbatim returns Missing Extension Error.

ATTACH 'postgres:connection_string' AS db (READ_ONLY);
ATTACH 'duckdb:backup.duckdb' AS backup;
COPY FROM DATABASE db TO backup;

Missing Extension Error:
Cannot bind index 'ducklake_snapshot_changes', unknown index type ''. You need to load the extension that provides this index type before table 'ducklake_snapshot_changes' can be modified.

To Reproduce

I have attached a shell file for convenience.

# If using shell file
./run_reprex.sh 

Else

docker run -d \
  --name ducklake-postgres \
  -e POSTGRES_USER=ducklake \
  -e POSTGRES_PASSWORD=ducklake_password \
  -e POSTGRES_DB=ducklake_catalog \
  -p 5432:5432 \
  postgres:15-alpine


duckdb
load postgres;
load ducklake;

ATTACH 'ducklake:postgres:dbname=ducklake_catalog host=localhost user=ducklake password=ducklake_password port=5432' AS my_ducklake
        (DATA_PATH '/tmp/datafiles/');
ATTACH 'postgres:dbname=ducklake_catalog host=localhost user=ducklake password=ducklake_password port=5432' AS db (read_only);
ATTACH 'duckdb:backup.duckdb' AS backup;

COPY FROM DATABASE db TO backup;

[run_reprex.sh](https://github.com/user-attachments/files/22990352/run_reprex.sh)

Missing Extension Error:
Cannot bind index 'ducklake_delete_file', unknown index type ''. You need to load the extension that provides this index type before table 'ducklake_delete_file' can be modified.

OS:

iOS, Apple M2, Sonoma 14.0

DuckDB Version:

1.4.1

DuckLake Version:

0.3

DuckDB Client:

CLI

Hardware:

No response

Full Name:

Ryan

Affiliation:

City of New York

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• [x] Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• [x] Yes, I have

[guillesd]

hi @macraesdirtysocks! I think this is a mistake on my side, since the SQL dump backup managed by DuckDB should be between postgres databases rather than a postgres db and a DuckDB db. The example doesn't work because the copy command is trying to copy postgres indexes over to DuckDB, which just doesn't fly.

I think this is more of a documentation issue so I may address this in the docs.

[macraesdirtysocks]

Hi @guillesd

Thanks for the response.

The use case is my catalog is Postgres and locked away on-prem.

I was looking for a way to create a local catalog so I can dev offsite.

Im trying to replicate PG to DuckDB using sling cli but duckdb won't read the replication as a catalog. I'm still looking into that.

[yurivanmidden]

I'm also interested in running this use case. The documentation suggests it should be possible but I'm running into the same issue as @macraesdirtysocks.

If it's because of the incompatibility in indexes, I would kindly suggest to update the documentation that this particular case is unsupported :).

[macraesdirtysocks]

I have found two ways to do this as a workaround.

"Freezing" your ducklake

https://ducklake.select/2025/10/24/frozen-ducklake/

Using sling to replicate from PG to duckDB.

https://docs.slingdata.io/sling-cli/getting-started

[guillesd]

I added a warning to the page. I think the problem generally with the COPY command is that it doesn't handle edge cases pretty well. That is why we also have this migration script for DuckDB to DuckLake https://ducklake.select/docs/stable/duckdb/migrations/duckdb_to_ducklake

Have you tried https://duckdb.org/docs/stable/sql/statements/export with duckdb-postgres? I haven't had a chance to try it if u guys have a sec to check whether this does a better job, I could update the docs. Basically this exports SQL commands and parquet files, so could potentially safe guard against the CREATE INDEX problem. I'm just unsure how well it works with Postgres.