Rails app change postgres db to sqlite3 using litestack

Questions / Help Build debugging
, ,
1

As per the title I am attempting to change my Ruby on Rails v7.2.2 app from using a postgres db to a sqlite3 db. I am using litestack to fascilitate that and have used the Ruby Dispatch - SQLite & Rails in Production article by Brad Gessler as my starting point. Steps I have taken are:

  1. Install litestack - deemed successful as it works in Development mode.

​ Litestack modifies config/database.yml with adapter: litedb and database: <%= Litesupport.root("production").join("data.sqlite3") %> It also adds development and production folders to the db folder with data.sqlite3 being the sqlite files in each.

  1. Create a persistent Volume on fly:
fly volumes create sqlite_volume --region syd -n 1 --size 1 --app my-app
fly volumes list
ID                  	STATE  	NAME         	SIZE	REGION	ZONE	ENCRYPTED	ATTACHED VM	
vol_vg32ogzyxjdxyyyy	created	sqlite_volume	1GB 	syd   	80c5	true
  1. Add mounts to fly.toml
[[mounts]]
  source = "sqlite_volume"
  destination = "/data"
  1. Add LITESTACK_DATA_PATH to Dockerfile
	LITESTACK_DATA_PATH="/data"
  1. Deploy to fly.io
fly deploy
✓ Configuration is valid
.
.
Error: Process group 'app' needs volumes with name 'sqlite_volume' to fulfill mounts defined in fly.toml; Run `fly volume create sqlite_volume -r REGION -n COUNT` for the following regions and counts: syd=2

I don’t understand the error message - I already created the volume and added it as source in fly.toml. What else do I need to do?

I thought I was well prepared for this but I must have missed something? Can anyone please help?

3

Hi… It’s asking for two volumes here, although it’s not clear why it didn’t create the second one automatically…

What does fly m list currently show? (You may need to pare down your current set before proceeding.)

5 
fly m list
3 machines have been retrieved from app holmes-genealogy.
View them in the UI here (​https://fly.io/apps/holmes-genealogy/machines/)

holmes-genealogy
ID              NAME                    STATE   CHECKS  REGION  ROLE    IMAGE                                                   IP ADDRESS                        VOLUME  CREATED                 LAST UPDATED            PROCESS GROUP   SIZE                 
148e5264c264e8  black-mountain-8276     stopped         syd             holmes-genealogy:deployment-01JCQ36GQXAKTK68S3A3BA90BF  fdaa:9:141c:a7b:2db:3f9a:ece0:2           2024-07-02T15:11:09Z    2024-11-15T05:06:03Z    app             shared-cpu-1x:1024MB
5683d513a52048  damp-wood-9420          started         syd             holmes-genealogy:deployment-01JCQ36GQXAKTK68S3A3BA90BF  fdaa:9:141c:a7b:1b4:6b5d:1eeb:2           2024-05-09T22:43:23Z    2024-11-15T05:06:10Z    app             shared-cpu-1x:1024MB
4d89172da00e78  bold-resonance-1060     stopped         syd             holmes-genealogy:deployment-01JCQ36GQXAKTK68S3A3BA90BF  fdaa:9:141c:a7b:1ae:59ad:c361:2           2024-07-10T20:47:44Z    2024-11-15T05:11:50Z    app             shared-cpu-1x:1024MB

I am doing this on an existing app so didn’t use fly launch to create the volumes. I created the volume myself. I did try a second volume with same name but the error message came back as ‘syd=1’ Should I have run fly launch again prior to the fly deploy?

6

No, fly deploy was correct.

Overall, you won’t be able to use SQLite with three machines, since they’d all have independent volumes (and hence wouldn’t see each others’ writes, etc.).

Could you perhaps say a little more about this application—in particular what the data-durability constraints are like? (E.g., can you afford to lose the past 1–2 days of updates.)

1 Like
7

This is just a hobby/family app mainly to learn and have a bit of family interaction. Yes! I can afford to lose the last few days of updates. I also probably don’t need 3 machines.

8

Neat… In that case, I think it’s ok to reduce down to just a single machine and a single volume.

(Be sure to check the dashboard every 2–3 days, though, because when the underlying physical host fails (which eventually it will), there will be a limited amount of time to restore from the automatic snapshot…)

9

Thanks, I actually thought I only had one machine when I deployed 7 months ago. Now I have 3 more machines added 10 days ago. Should I just do a fly scale count 1 to reduce to 1 machine?

10

Yes! that is exactly what I had to do. I haven’t been able to test the solution yet as fly deploy keeps timing out and throwing errors due I believe to a fly.io incident - ‘Degraded API Performance’ As soon as fly deploy succeeds I will mark message 6 as the solution.

1 Like
11

Why not use turso? They have a generous free tier for your use case

12

Unfortunately I still need help.

Running fly deploy gave a release command error:

Error: release command failed - aborting deployment. error running release_command machine: error creating a release_command machine: failed to launch VM: request returned non-2xx status: 504: upstream request timeout (Request ID: 01JDKE88S8TGEYWXMYZJX6EAAB-syd) (Trace ID: 3f01c3a7536a15442713a1a7e2989eb7)

I removed release_command = "./bin/rails db:prepare"from fly.toml [deploy] section and fly deploy ran. Was this the right thing to do?

Here is the last part of the fly deploy console logs:

--> Building image done
image: registry.fly.io/holmes-genealogy:deployment-01JDN5D9XNGHXC6PP36A7NBZ68
image size: 275 MB

Watch your deployment at https://fly.io/apps/holmes-genealogy/monitoring

-------
Updating existing machines in 'holmes-genealogy' with rolling strategy

WARNING The app is not listening on the expected address and will not be reachable by fly-proxy.
You can fix this by configuring your app to listen on the following addresses:
  - 0.0.0.0:3000
Found these processes inside the machine with open listening sockets:
  PROCESS        | ADDRESSES
-----------------*---------------------------------------
  /.fly/hallpass | [fdaa:9:141c:a7b:2d9:bcb0:cd83:2]:22


-------
 ✔ Cleared lease for 78432d4a465618
-------
Checking DNS configuration for holmes-genealogy.fly.dev

Visit your newly deployed app at https://etc

I had copied my development db (data.sqlite3) from db/development/data.sqlite3 to db/production/sqlite3 hoping that fly deploy with ‘litestack’ would then put it in the persistent volume (copilot suggested this). I don’t know if this has happened or not but I suspect that it hasn’t. I think fly is still using my postgres db.

How do I check that my app is now using sqlite3?

How do I make this change from postgres to sqlite3 work?

13

Yes.

I don’t know enough about litestack to answer this question.

Is the DATABASE_URL secret still set? Try running fly ssh console and then “echo $DATABASE_URL”. If that says postgres, you are still using postgres.

If set, unset it with:

fly secrets unset DATABASE_URL

With that secret unset, you CAN’T access your postgres database, so if things are still working you are using sqlite3.

14 
fly ssh console
Connecting to fdaa:9:141c:a7b:2d9:bcb0:cd83:2... complete
root@78432d4a465618:/rails# echo $DATABASE_URL
postgres://holmes_genealogy:urguBGKwKeukMlZ@holmes-genealogy-db.flycast:5432/holmes_genealogy?sslmode=disable

and

fly secrets unset DATABASE_URL
Updating existing machines in 'holmes-genealogy' with rolling strategy

-------
 ✔ Machine 78432d4a465618 [app] update succeeded
-------
Checking DNS configuration for holmes-genealogy.fly.dev

Now echo $DATABASE_URLreturns nothing.

The landing page for my app loads, however, I am unable to log in. How can I interact with the sqlite3 file, maybe run db:seeds. Any other ideas?

15

A Rails 7.2.2 app would typically have a bin/docker-entrypoint containing the following:

# If running the rails server then create or migrate existing database
if [ "${1}" == "./bin/rails" ] && [ "${2}" == "server" ]; then
  ./bin/rails db:prepare
fi

This would load your seeds.

You can interact with your database by running fly ssh console and then running commands on that machine.

I don’t know enough about litestack to tell you what you should be looking to do, but you will want your underlying database on the volume (i.e., /data)

16

Thank you. I have that bin/docker-entrypoint. Maybe that was my problem as I wanted to just adopt my development db as is but I had a small amount of seed data exposed which I didn’t want loaded.
I was able to find the sqlite3 file at /data/production/data.sqlite3.
Am I able to deploy again after removing the rogue seed data and have everything reset to the original intended starting point. In other words will a new fly deploy overwrite or supersede the existing sqlite3 file.
If I can do that then I think everything is going to be ok.
No. It seems that the table structures did not come across to the volume. That is why I had the landing page and nothing else. What I need to do is direct copy my development database to the /data/production folder in the volume. Can you help me do that?

17

SFTP is easiest for that, in my opinion:

https://community.fly.io/t/backup-and-restore-sqlite-db-to-server/21232/2

(You may need an extra step to tweak permissions at the end, in this case; I don’t use Rails myself…)

18

Thank you, I will give it a go.

19

Yes the backup and restore process with SFTP worked but now I have a problem with my machine that starts and stops almost immediately. I am going to leave it until tomorrow.

20

Just want to thank Mayailurus and Rubys for your help. I was going around in circles until I realised that I had reduced my machines to 1 but still had:

auto_stop_machines=true
auto_start_machines=true

in my fly.toml file.
I then had to sort out permissions because when I used SFTP to upload my db it changed the permissions. There is more than 1 solution here but I clicked the latest
Took me a long time but I learnt a lot.

1 Like