If you’ve already built a Rails 8 app using the default SQLite setup and now want to switch to PostgreSQL, here’s a clean step-by-step guide to make the transition smooth:
1.🔧 Setup PostgreSQL in macOS
🔷 Step 1: Install PostgreSQL via Homebrew
Run the following:
brew install postgresql
This created a default database cluster for me, check the output. So you can skip the Step 3.
==> Summary
🍺 /opt/homebrew/Cellar/postgresql@14/14.17_1: 3,330 files, 45.9MB
==> Running `brew cleanup postgresql@14`...
==> postgresql@14
This formula has created a default database cluster with:
initdb --locale=C -E UTF-8 /opt/homebrew/var/postgresql@14
To start postgresql@14 now and restart at login:
brew services start postgresql@14
Or, if you don't want/need a background service you can just run:
/opt/homebrew/opt/postgresql@14/bin/postgres -D /opt/homebrew/var/postgresql@14
After installation, check the version:
psql --version
> psql (PostgreSQL) 14.17 (Homebrew)
🔷 Step 2: Start PostgreSQL Service
To start PostgreSQL now and have it start automatically at login:
brew services start postgresql
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)
If you just want to run it in the background without autostart:
# pg_ctl — initialize, start, stop, or control a PostgreSQL server
pg_ctl -D /opt/homebrew/var/postgresql@14 start
https://www.postgresql.org/docs/current/app-pg-ctl.html
You can find the installed version using:
brew list | grep postgres
🔷 Step 3: Initialize the Database (if needed)
Sometimes Homebrew does this automatically. If not:
initdb /opt/homebrew/var/postgresql@<version>
Or a more general version:
initdb /usr/local/var/postgres
Key functions of initdb: Creates a new database cluster, Initializes the database cluster’s default locale and character set encoding, Runs a vacuum command.
In essence, initdb prepares the environment for a PostgreSQL database to be used and provides a foundation for creating and managing databases within that cluster
🔷 Step 4: Create a User and Database
PostgreSQL uses a role-based access control. Create a user with superuser privileges:
# createuser creates a new Postgres user
createuser -s postgres
createuser is a shell script wrapper around the SQL command CREATE USER via the Postgres interactive terminal psql. Thus, there is nothing special about creating users via this or other methods
Then switch to psql:
psql postgres
You can also create a database:
createdb <db_name>
🔷 Step 5: Connect and Use psql
psql -d <db_name>
Inside the psql shell, try:
\l -- list databases
\dt -- list tables
\q -- quit
🔷 Step 6: Use a GUI (Optional)
For a friendly UI, install one of the following:
2. Update Gemfile
Replace SQLite gem with PostgreSQL:
# Remove or comment this:
# gem "sqlite3", "~> 1.4"
# Add this:
gem "pg", "~> 1.4"
Then run:
bundle install
3. Update config/database.yml
Replace the entire contents of config/database.yml with the following:
default: &default
adapter: postgresql
encoding: unicode
username: postgres
password:
host: localhost
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
development:
<<: *default
database: your_app_name_development
test:
<<: *default
database: your_app_name_test
production:
primary: &primary_production
<<: *default
database: your_app_name_production
username: your_production_username
password: <%= ENV['YOUR_APP_DATABASE_PASSWORD'] %>
cache:
<<: *primary_production
database: your_app_name_production_cache
migrations_paths: db/cache_migrate
queue:
<<: *primary_production
database: your_app_name_production_queue
migrations_paths: db/queue_migrate
cable:
<<: *primary_production
database: your_app_name_production_cable
migrations_paths: db/cable_migrate
Replace
your_app_namewith your actual Rails app name.
4. Drop SQLite Database (Optional)
rm storage/development.sqlite3
rm storage/test.sqlite3
5. Create and Setup PostgreSQL Database
rails db:create
rails db:migrate
If you had seed data:
rails db:seed
6. Test It Works
Boot up your server:
bin/dev
Then go to http://localhost:3000 and confirm everything works.
7. Check psql manually (Optional)
psql -d your_app_name_development
Then run:
\dt -- view tables
\q -- quit
8. Update .gitignore
Note: If not already added /storage/*
Make sure SQLite DBs are not accidentally committed:
/storage/*.sqlite3
/storage/*.sqlite3-journal
After moving into PostgreSQL
I was getting an issue with postgres column, where I have the following data in the migration:
# migration
t.decimal :rating, precision: 1, scale: 1
# log
ActiveRecord::RangeError (PG::NumericValueOutOfRange: ERROR: numeric field overflow
12:44:36 web.1 | DETAIL: A field with precision 1, scale 1 must round to an absolute value less than 1.
12:44:36 web.1 | )
Value passed is: 4.3. I was not getting this issue in SqLite DB.
What does precision: 1, scale: 1 mean?
precision: Total number of digits (both left and right of the decimal).scale: Number of digits after the decimal point
If you want to store ratings like 4.3, 4.5, etc., a good setup is:
t.decimal :rating, precision: 2, scale: 1
# revert and migrate for products table
✗ rails db:migrate:down VERSION=2025031XXXXX -t
✗ rails db:migrate:up VERSION=2025031XXXXXX -t
Then go to http://localhost:3000 and confirm everything works.
to be continued.. 🚀
The Rails Drop 
2 thoughts on “Setup 🛠 Rails 8 App – Part 11: Convert 🔄 Rails App from SQLite to PostgreSQL”