Gonna give a quick how to for connecting to and getting your sqlite database
from your production app on fly.io. It can be a bit confusing but once you get
your ssh connection set up and openssh installed on your fly.io server, it's
much easier to replicate. In this tutorial we will be connecting to your fly.io
instance via wireguard and ssh, then insalling openssh on your instance so we
can use scp from your local computer to copy the sqlite db file for viewing
locally.
Start by downloading Wireguard here.
You'll also need flyctl which I'm assuming you already have, but if you don't it
can be downloaded here.
You'll need to connect wireguard to fly.io by creating a wireguard config file
with the fly CLI. Use flyctl wireguard create to create a file called
wireguard.conf. Then open wireguard and import that file and activate that ssh
tunnel.
Next you're going to connect to your fly instance via ssh. Type
fly ssh issue --agent then fly ssh console to ssh into your instance.
Once you're in you'll need to install openssh on the instance to be able to
securely copy files from your instance to your local computer. Do this with the
command: apt-get install openssh-client.
At this stage you'll want to create a backup of your database to copy to your
local machine so nothing goes wrong trying to copy the file while a transaction
is happening. You can do this in one of 2 ways:
sqlite3 data/sqlite.db '.backup data/backup.db'
OR
sqlite3 data/sqlite.db "VACUUM INTO 'data/backup.db'"
You may have to change the path to your database, mine was in the data
directory.
Exit ssh with exit;
Now you can copy your sqlite db file to your local computer with
scp root@[app-name].internal:/data/backup.db fly_backup.db.
Don't forget to fill in the name of your app on fly.io and change the path to
wherever you backed up your sqlite db on your server. Mine is at
/data/backup.db.
Top comments (0)