Import SQL into Fly.io Postgres

This is one way to migrate data into fly.io Postgres database.

Create a cluster

Fly.io gives you a handy cli for running commands on its platform. The pg or postgres subcommand will give you access to postgres database actions. If not done already, first, create the Postgres instance:

fly pg create

This will ask you a few questions and create the cluster.

Connect the database to your application

If not done already, you'll also want to make sure that your database is "connected" to your application. Let's say you named app poobah and your database poobah-db. To connect them, you would:

cd poobah
fly attach poobah-db

The only thing I've observed this doing is setting a DATABASE_URL secret in your poobah app environment. But presumably there may be network access being routed as well so that the app can communicate with the cluster.

Create the database

You've created the Postgres cluster, but now you need to create the database inside of it. We might call it poobah_app. To run a create database command in Postgres, we need to connect to a psql console. Run this:

fly pg connect -a poobah-db

This will give you a psql prompt, where you can run:

create database poobah_app;

Migrate your application

Now you can create your database tables however you desire. A common method is to have some tooling in your application that handles syncing database migrations to your application state. For a Django app, this is indeed the case. For fly.io, we setup a Dockerfile that allows database migration to happen as a part of the deploy. The Dockerfile might include:

RUN python manage.py migrate

Then to get that to run on our app instance, we'd cd into our app dir and deploy:

fly deploy

But if that's not a part of your deploy process, we could ssh into our app instance and run the migrations manually from there:

fly ssh console
cd app # or whatever your Dockerfile WORKDIR is
python manage.py migrate

Get data to copy

Your data can come from anywhere, so this likely doesn't apply to you, but for example: Let's say that it's in another, pre-existing Postgres instance. You can get it out as SQL with:

pg_dump --data-only --column-inserts > inserts.sql

This will not include anything but table data as inserts with the column names explicit in the insert statements.

Run SQL Against Fly.io Database

Now you have some data that you want to import or seed into your Postgres database on Fly.io. We have the file locally. We have psql locally. We're going to create a network tunnel from our localhost to postgres.

First, stop local Postgres instances, if any, so that port 5432 is clear:

systemctl stop postgresql.service

Then proxy network traffic from localhost:5432 to poobah-db:

fly proxy 5432 -a poobah-db

Now you can run arbitrary psql commands against fly.io. We'll run our inserts.sql file:

psql -U postgres -h localhost -p 5432 poobah_app -f inserts.sql

With any luck over this multi-step process, you'll be able to see your data now in your Fly.io Postgres database.

How do you do this? How can we make it more straightforward?