Switching Sqlite3 to PostgreSQL for existing app

Question

I'm trying to switch development (and test/production) databases to PostgreSQL so I can deploy my rails app to Heroku.

Followed their directions and a railscast and looked around StackOverflow and Google for the right way to rewrite my database.yml file and do everything else, but I've been running into a lot of problems, so I was hoping someone could help me figure out what else I need to do. I'll just explain what I've tried below.

One major question is what to do with my database.yml file. A lot of sites disagree, or simply don't have any entries for pool or username or encoding (or, for that matter, the whole Production environment). Here's what I ended up with after some looking around and combining. Should this work?:

development:
  adapter: postgresql
  encoding: unicode
  database: <appname>_development
  pool: 5
  username: <username>
  password: <password>

test:
  adapter: postgresql
  database: <appname>_test
  username: <username>
  password: <password>
  host: localhost

What is Pool? Do I need encoding? Why do so many examples have no production section in the yml file?

Per the Railscast suggestion, I homebrew installed PostgreSQL, then init'd the db, then executed this line.

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Following another StackOverflow answer, I ensured my Postgres was in the /usr/local/bin/postgres directory, and I added this line to my .bash_profile file.

export PATH="/Applications/Postgres.app/Contents/MacOS/bin:$PATH"

Also on the Railscast's suggestion, I installed the taps gem, and executed the following lines:

taps server sqlite://db.development.sqlite3 <username> <password>

then opened a new tab and executed:

taps pull postgresql://<username>@localhost/<appname>_development http://localhost//5000

But when I enter that, I get the following error:

Failed to connect to database:
  Sequel::AdapterNotFound -> LoadError: cannot load such file -- sequel/adapters/postgresql

Sure I'm doing a bunch wrong. (For instance, should I be pulling from the sqlite3 database instead? How do I know its url?). But I have no idea how to start troubleshooting, so I figured I'd ask for help.

Thanks!

Answer 1

10 minutes to move from SQLite to Postgres for your RoR app

This is assuming you have a development database in sqlite and you want to move the structure and data to postgres.

You will need to install and configure Postgres locally first with a user that has your username, here are the postgres install docs

---

Software needed: postgresql, pgloader

---

Steps

1. install pgloader / postgres, and make sure postgresql is running on your system
2. backup sqlite - copy development.sql to development_old.sql
3. add gem 'pg' to main section of your Gemfile
4. bundle install
5. update config/database.yml (see sample below)
6. rake db:setup
7. cd [application root]
8. load postgres db with data from sqlite

pgloader ./db/development.sqlite3 postgresql:///[name of postgres dev db]

9. remove gem 'sqlite3' from your gemfile
10. bundle install
11. start server - rails server
12. test by visiting app at localhost:3000

---

In case you run into issues or edge cases, here are some resources to help.

Resources:

• https://pgloader.io
• postgres install docs
• Want to move to heroku too?

database_sample.yml

default: &default
  adapter: postgresql
  encoding: unicode
  host: localhost
  port: 5432
  # For details on connection pooling, see Rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: [name of app]_dev

test:
  <<: *default
  database: [name of app]_test

staging:
  <<: *default
  database: [name of app]

production:
  <<: *default
  database: [name of app]

Answer 2

Taps uses Sequel, and Sequel uses postgres://... instead of postgresql://... for connection strings. Try: taps pull postgres://<username>@localhost/<appname>_development http://localhost//5000.

Answer 3

Heroku, and the "production" section of your "database.yml" file

Heroku will ignore whatever your database.yml file says for production. Heroku will inject its own configuration for production automatically for the Bamboo and Cedar stacks. To say it another way - leave production empty - it's not required when deploying to the Bamboo or Cedar stacks.

If, on the other hand, you're deploying to, say, Amazon Web Services, but using Heroku's hosted Postgres solution as your backend database (which, itself, runs on top of AWS), you would specify a production section inside of database.yml.

The "taps" gem

This appears to be a gem that allows you to migrate data from existing database (SQLite, for you) to a new database (PostgreSQL, which I hope you'll be using for local development from here on out). I didn't read too much into it, but I'm guessing that taps uses ActiveRecord (or another ORM solution) to read records in from any database it supports, and then uses ActiveRecord to write data back out to a destination database. By using ActiveRecord as a sort of inter-database translator you avoid the need to write database-specific queries to move the data.

That being said, if your local database is just development data, and assuming it can be recreated (it should be - it's just development data), then you might be able to forego using taps altogether. Simply don't migrate your development data from SQLite to Postgres - just stop using SQLite and start using Postgres with an empty database with either all your migrations having been run, or a db:schema:load on it.

Creating great development data that is easy to re-create on a whim is possible via Populator (for your future needs - I wouldn't worry too much about it right now).

"Failed to connect to database:" error

Did you install the pg gem? That's the gem you need in Rails to talk to PostgreSQL servers. My interpretation of that error suggests that you're just missing the pg gem, hence, "Sequel::AdapterNotFound"

The pool

The pool is the number of concurrent connections you want to make available to your database, from your application. With SQLite you can specify this to allow a certain number of concurrent reads. SQLite (contrary to what is implied by its documentation) does not support concurrent writes.

The pool is useful in production whenever your app is being served by more than a single web instance. The pool specifies the maximum number of concurrent connections to your database, and typically your web server will use one connection, per active request, per thread, or thereabouts. I'm fudging the technical details here, but the point is that the more concurrency (the more simultaneous requests) that you need to support, the more connections you might need to have available.

As previously stated, this is ignored on Heroku anyway, so it's pointless to specify it, and in local development you probably will only be making one request at a time, and so setting this to 1 probably looks the same as setting it to 5.

So, what happens when all the connections in the pool are currently being used? Any subsequent requests are queued up until a connection is made available, or until the connection times out, whichever comes first.

There is more information on the connection pool here.

Answer 4

This is generally what I use for postgres:

production:
  adapter: postgresql
  encoding: utf8
  database: app_name
  pool: 5
  host: localhost
  username: username
  password: password

Also, keep in mind yaml can be cantankerous, so be sure to avoid tabs and use only spaces in your formatting.

Answer 5

I did this recently and my database.yml only uses:

development:
  adapter: postgresql
  username: my_user
  database: my_project_development

If you have data in a Postgres DB on Heroku, you could do a heroku db:pull to populate your Postgres development database. Otherwise, you could probably db:push your Sqlite data (from your machine, using your old database.yml configuration) to Heroku, update your database.yml and then do a db:pull.