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
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:
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
fly proxy 5432 -a poobah-db
Now you can run arbitrary
psql commands against fly.io. We'll run our
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?