What's the strategy for migrating from SQLite to PostgreSQL?

[DataLabTechTV]

Hey guys,

I started my DuckLake using SQLite, but now I'm about to run some experiments where it makes sense, even for a single user scenario, to move to PostgreSQL. Is there (or will there be) a clear way to do this migration on the DuckLake side, or should I just use standard tools like pgloader for this?

Has anyone tried this, or have any advice?

Thanks!

[guillesd]

I will upvote this since it seems like something nice to have but for now we don't have an example on migrating metadata catalogs

[lostmygithubaccount]

FWIW I waas not migrating between metadata DBs, but was using Postgres and had a script to:

• run pgdump to backup all data to a backup.sql file
• run some encryption on that file
• upload it to cloud storage

then the reverse script to:

• download from cloud storage
• de-encrypt via password(s)
• pgsql command to load the data

This was working great for restoring DuckLake's metadata DB for me. I assume this would work going between SQLite and Postgres, though may require some chagnes. I would personally just recommend using standard tooling

[DataLabTechTV]

It's the potential changes between PostgreSQL and SQLite that concern me, but I'll test this out in a few weeks and report back. It's likely that pgloader handles it correctly. Thanks for the feedback!

[lostmygithubaccount]

I'm also curious how this works if you do report back here. I was going back and forth between sqlite vs postgres for my personal use (and just wiping the data every time), having the option to easily switch would be cool