Published: 2025-01-06
When I originally created this site, I started on sqlite but I quickly realized I wanted postgres so that I could learn a thing or two about databases. Here's the process I went about to start migrating the database adapter to Postgres.
Note: I did not actually migrate data from sqlite to postgres. This site had exactly 1 post prior to me doing this and so this is more how to switch it up to postgres and get that working.
I started with following a few guides to give myself an idea of what Postgres and Rails looks like. First thing I did was created a new test site so I had configs to compare to.
Note: I did not actually migrate data from sqlite to postgres. This site had exactly 1 post prior to me doing this and so this is more how to switch it up to postgres and get that working.
I started with following a few guides to give myself an idea of what Postgres and Rails looks like. First thing I did was created a new test site so I had configs to compare to.
$ rails new pg_site --database=postgresql
From here I realized looking at the database.yml file that production recommends FOUR different databases. And this does make sense given that Rails is meant to scale, so the database will be one area where you want your queue, cache and websockets to not impact your main database. More info on the Rails rationale for multiple databases here.
Still, this is a personal website, and I don't need four databases and won't it be fun to do a migration back to four databases in the future? And then I can read this guide and unscrew up everything I screwed up in the first place?
Recommended config/database.yml
development:
<<: *default
database: pg_site_development
production:
primary: &primary_production
<<: *default
database: pg_site_production
username: pg_site
password: <%= ENV["PG_SITE_DATABASE_PASSWORD"] %>
cache:
<<: *primary_production
database: pg_site_production_cache
migrations_paths: db/cache_migrate
queue:
<<: *primary_production
database: pg_site_production_queue
migrations_paths: db/queue_migrate
cable:
<<: *primary_production
database: pg_site_production_cable
migrations_paths: db/cable_migrate
My config/database.yml
development: <<: *default url: <%= ENV["DATABASE_DEV_URL"] %> production: <<: *default url: <%= ENV["DATABASE_PROD_URL"] %>
My rationale for using Postgres URLs is because its a simpler environment variable than having a username, password and database as separate secrets to maintain. So that's that, deploy and it works right? Wrong! There's more you need to fix!
Recommended config/cable.yml
production:
adapter: solid_cable
connects_to:
database:
writing: cable
polling_interval: 0.1.seconds
message_retention: 1.day
New config/cable.yml
production: adapter: async
Recommended config/cache.yml
production: database: cache <<: *default
New config/cache.yml
production: <<: *default
In config/environments/production.yml, there's too many things happening to show you all the config so I'll tell you this. Just comment out these lines and make sure your cache_store is set to memory
config.cache_store = :memory_store
#config.active_job.queue_adapter = :solid_queue
#config.solid_queue.connects_to = { database: { writing: :queue } }
But then there was one more issue. I kept getting errors for solid queue that certain tables didn't exist. And that's because solid queue still needed to see those tables in the main database. The recommendation I found was to copy the db/queue_schema.rb into its own migration. This is definitely when things started feeling really hacky to me, but it's not stupid if it works right? Right?
And then the site worked on Postgres...for now. We'll see over the years how things go if I think I will migrate back to multiple databases.