rails 3 postgreSQL basic 'database does not exist'

Question

OK, I'm building my first rails 3.0 app and want to test the postgreSQL server as production on my development machine (which is running 10.6). When you create a new app and rake db:migrate it creates sqlite db's for all three environments. Cool. Now I want to learn how to move to production and use postgres. I've used homebrew to install postgres, installed the pg (env ARCHFLAGS="-arch x86_64" gem install pg) and postgres-pr gems.

I've run rake db:migrate in hope that like with sqlite3 it will auto build my production server since I've updated my database.yml (see below).

OK, in my app's folder, I restart the server using 'rails s --environment=production' and it bails saying it cannot find my production database.

So all the google searches for 'rails 3 postgres install' got me this far, but I appear to be missing something because rails is failing to create the new pg database.

postgres is running as determined by ps.

createdb -Omysuperusername -Eutf8 vitae_production
createdb -Omysuperusername -Eutf8 /Users/sam/apps/vitae/db/vitae_production

But this directory does not have this database so I'm missing something. What am I overlooking?

this is my database.yml snippet:

production:
  adapter: postgresql
  host: localhost
  database: db/vitae_production
  pool: 5
  timeout: 5000
  username: mysuperusername
  password:

Answer 1

There are a couple things going on here. First of all, you seem to be mixing the SQLite and PostgreSQL format for the database: setting in your database.yml. With SQLite, you specify the relative path to the SQLite database file with something like:

database: db/vitae_production.sqlite

but with PostgreSQL, you specify the database name with something like this:

development:
  database: vitae_development
  username: rails
  ...

Then, once database.yml is setup, you'd create the database user (if needed) from inside psql:

psql> create role rails login;

and then let Rails create the database:

$ rake db:create

Then you should be able to run your migrations to create your initial tables and away you go.

You probably want to read the create role documentation to make sure you get the right options. You probably want to be working in the development environment rather than production as well so I changed the names and YAML to reflect that, production is for deployment and I don't think you're deploying anything just yet.

Answer 2

Im'not familiar with rails but you could create your database as a postgres super user and then grant privileges, assuming that in your linux distribution the postgres super user is postgres:

su root
su postgres
createdb vitae_production

then in postgres grant privileges to another user

psql
CREATE USER rails WITH PASSWORD 'myPassword';
GRANT ALL PRIVILEGES ON DATABASE vitae_production TO rails;
ALTER DATABASE vitae_production OWNER TO rails;

then your config file should look like this:

production:
  adapter: postgresql
  host: localhost
  database: vitae_production
  pool: 5
  timeout: 5000
  username: rails
  password: myPassword