How do I move a Rails app from SQLite to PostgreSQL?

Here’s the typical path to move a Rails app from SQLite to PostgreSQL.

1) Add the PostgreSQL gem

In your Gemfile, replace or add:

gem "pg"

Then run:

bundle install

If you currently have sqlite3 in the Gemfile, you can remove it if you no longer need SQLite.

---

2) Install PostgreSQL locally

Make sure PostgreSQL is installed and running.

Examples:

• macOS (Homebrew):

  brew install postgresql
  brew services start postgresql
  

• Ubuntu/Debian:

  sudo apt update
  sudo apt install postgresql postgresql-contrib
  sudo service postgresql start
  

• Docker: Run a Postgres container and point Rails to it.

---

3) Update config/database.yml

Change your database config from SQLite to PostgreSQL.

Example:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: your_db_user
  password: your_db_password
  host: localhost

development:
  <<: *default
  database: your_app_development

test:
  <<: *default
  database: your_app_test

production:
  <<: *default
  database: your_app_production
  username: your_app_production_user
  password: <%= ENV["YOUR_APP_DATABASE_PASSWORD"] %>

If your PostgreSQL setup uses peer auth on local machine, you may not need username/password in development.

---

4) Create the PostgreSQL databases

Run:

rails db:create

This creates the development and test databases.

If needed, you can also create them manually with:

createdb your_app_development
createdb your_app_test

---

5) Move your schema/data

If you’re just switching and your app is still early, the simplest approach is often:

rails db:migrate

If you already have data in SQLite and want to preserve it, you have a few options:

Option A: Dump and load data manually

• Export from SQLite
• Import into PostgreSQL

This can be tedious and error-prone depending on data types.

Option B: Use a migration/data transfer tool

Common approaches include:

• pgloader
• custom scripts
• exporting CSVs and importing into Postgres

pgloader is often the easiest for a straight SQLite → PostgreSQL migration.

Example:

pgloader sqlite:///path/to/development.sqlite3 postgresql://user:password@localhost/your_app_development

---

6) Check for SQLite/Postgres incompatibilities

Some common issues when switching:

• Boolean fields: usually fine, but check defaults.
• sqlite-specific SQL in raw queries may break.
• Case-insensitive searches: Postgres differs from SQLite.
• Text vs string limits: Postgres is stricter in some cases.
• Reserved keywords: column/table names may conflict.
• Date/time behavior and type casting can differ.

---

7) Regenerate schema if needed

Rails usually uses schema.rb, but for Postgres-specific features you may prefer:

config.active_record.schema_format = :sql

in config/application.rb or environment config.

Use structure.sql if you rely on Postgres-specific features like:

• custom indexes
• partial indexes
• views
• functions
• advanced constraints

Then dump your schema with:

rails db:structure:dump

---

8) Test thoroughly

Run your test suite:

rails test
# or
bundle exec rspec

Also check:

• migrations
• seed data
• background jobs
• search/filter queries
• production boot

---

9) Update production config

If deploying to something like Heroku, Render, Fly, or AWS, make sure:

• DATABASE_URL is set
• PostgreSQL is provisioned
• credentials are configured correctly

Rails will often use DATABASE_URL automatically if present.

---

10) Commit and deploy

Once everything works:

git add .
git commit -m "Switch database from SQLite to PostgreSQL"

Then deploy.

---

Quick checklist

• Add pg gem
• Install PostgreSQL
• Update database.yml
• Create databases
• Migrate or import data
• Fix SQL/type incompatibilities
• Run tests
• Update production config

If you want, I can also give you:

1. a step-by-step migration for an existing app with data, or
2. a sample database.yml for development/test/production.