Migrating from Postgres to SQLite (with LiteFS)

1

I’m trying to migrate from a Postgres cluster (on Fly) to SQLite with LiteFS and having a bit of trouble with the data migration. I’m a db noob, so I tried writing a simple script that would run on startup to query/upsert all data from Postgres to SQLite, but that’s not working out at the moment due to probably too much data to handle I think (the script stops without explanation when trying to copy 425k records).

So now I’m investigating the “proper” way to do this following these instructions: Convert PostgreSQL to SQLite - Manuel van Rijn

Trouble is when I try to run:

fly ssh console -a kcd-postgres -C "pg_dump --data-only --inserts withered_frost_3196" > ~/Desktop/dump.sql

I’m able to connect just fine, but I get the error:

pg_dump: error: connection to database "withered_frost_3196" failed: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

I’m stuck. Any idea of how to do this properly? I only need this to run once so hacks are acceptable :sweat_smile:

I should add that the server is definitely running as my app still works :slight_smile:

2

Hi @kentcdodds

Try adding the database url to your pg_dump command:

pg_dump -d postgres://postgres:$OPERATOR_PASSWORD@$FLY_APP_NAME.internal:5432 --data-only --inserts withered_frost_3196

You’ll most likely need to run that interactively ( fly ssh console -a kcd-postgres ).

3

Still had the same errors. I’m sure I’m doing something wrong. But I was able to get my script to run by removing it from the startup and instead SSH-ing into the VM after startup to manually run the script. :man_shrugging: It worked.

Thanks for the help anyway @dusty!

1 Like