2

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!

Sasha
  • 6,224
  • 10
  • 55
  • 102
  • Just an FYI, while it's best practice to switch so that your environments match, you don't have to switch to deploy to heroku. You can conditionally load things in your gemfile using groups. – Jason Levens Sep 17 '12 at 19:26
  • Did you install the 'pg' gem? – CubaLibre Sep 17 '12 at 19:33
  • Yes. I did. I think I might have used taps incorrectly, though. The doc suggests using a pull line that looks like this, and I don't know what's meant by httpuser and httppassword: taps pull postgres://dbuser:dbpassword@localhost/dbname http://httpuser:httppassword@example.com:5000 – Sasha Sep 17 '12 at 19:56

5 Answers5

8

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.

Community
  • 1
  • 1
jefflunt
  • 33,527
  • 7
  • 88
  • 126
  • Yeah. I've got the pg gem installed. Also, I'm new to Rails and don't know what you mean by dumping SQLite and starting fresh. How do I do that? rollback the models and then generate them again? – Sasha Sep 17 '12 at 19:48
  • I mean, stop using SQLite and just start using Postgres. I apologize - "dump" is also a database term meaning essentially to export the contents of a database to a file, but that's not what I meant. I meant "dump" it as in stop using it. – jefflunt Sep 17 '12 at 19:50
  • If you can avoid moving your development data from SQLite to Postgres (just start over - it will probably save you time in the long run), then you don't need the `taps` gem at all. – jefflunt Sep 17 '12 at 19:52
  • Sorry for stupid question number two. How does one "stop" using one database and start using another? Just alter the database.yml file? Should that get rid of my old data but otherwise be seamless? I'm sorry to be so clueless -- just entered the Rails(/code) world a few weeks ago, and most of this is Greek to me. – Sasha Sep 17 '12 at 19:53
  • 1
    Thanks so much for the detailed answer, by the way. Really appreciate it. – Sasha Sep 17 '12 at 19:55
3

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

Jeremy Evans
  • 11,959
  • 27
  • 26
  • this is the right answer, even there is a very very long one there, but this is the correct – Aleks Apr 24 '13 at 22:46
1

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.

pdoherty926
  • 9,895
  • 4
  • 37
  • 68
  • So you mean push to heroku with sqlite3 dev and postgresql production and then pull from that database (once it's working) using taps? – Sasha Sep 17 '12 at 19:54
1

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.

Mark Locklear
  • 5,044
  • 1
  • 51
  • 81
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]

  1. remove gem 'sqlite3' from your gemfile
  2. bundle install
  3. start server - rails server
  4. test by visiting app at localhost:3000

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

Resources:

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]
Dan Williams
  • 3,769
  • 1
  • 18
  • 26