How to backup & restore SQLite3 database with Ruby on Rails 8 & Kamal

Backing up your database is an important thing when you have a website online especially when you want to migrate your app to a new server for things like reducing costs and if you found a better service to use. Whatever the reason is, once you want to migrate your app you are going to also want to move your database as well so that you don't lose your users and apps data. This guide will tell you how to do so using Ruby on Rails and Kamal First of all we will start in the terminal on your computer. We are going to SSH into are app

ssh -i ~/ssh-key user@server-ip

Next we are going to get the docker container id that are app is using and store it as a shell variable so we can use it for the following commands. ( Replace your-app-web with the name of your service found in config/deploy.yml)

CONTAINER_ID=$(docker ps --filter name=your-app-web --format '{{.ID}}')

Next we are going to create a backup of our database inside of the docker container which is where the sqlite3 database is running

docker exec $CONTAINER_ID sqlite3 /rails/storage/production.sqlite3 ".backup /tmp/backup.sqlite3"

Then we copy the backup file from the container onto the server outside of the docker container

docker cp $CONTAINER_ID:/tmp/backup.sqlite3 /root/backup.sqlite3

Now you can exit out of the SSH and go back to your local terminal. We will now copy the file from the server to our local machine so we can store it until we move it to the new server

scp -i ~/ssh-key root@your-server:/root/backup.sqlite3 ./backup.sqlite3

Congrats! You now have a backup of your apps database on your machine. Next I will show you how to migrate the database to the new server. You are going to want to acquire your new server and run the kamal setup command so that your app is running. You should have an empty database on the new app with no data yet. To migrate the database to the new server we will do a similar process from the backup but in reverse order. First of all you will copy the local backup file to the new server

scp -i ~/ssh-key ./backup.sqlite3 root@your-server:/root/backup.sqlite3

Then we will SSH into the new server

ssh -i ~/ssh-key user@server-ip

Then we want to get the container id again for the docker container of our new app

CONTAINER_ID=$(docker ps --filter name=your-app-web --format '{{.ID}}')

Then we can copy the backup file which we have on the server into the docker container and into the place that rails is using for the production sqlite3 file

docker cp /root/backup.sqlite3 $CONTAINER_ID:/rails/storage/production.sqlite3

Finally you want to reboot your app so that it will use the new database file. Exit out of the SSH and run this command on your local terminal ( inside of your app folder so that the kamal command works )

kamal app boot