How do I get my rails app to use my postgresql db?

Okay, to figure out what's going on I created a new test app using the --database flag:

rails_projects$ rails new test_postgres --database postgresql

It turns out, all that does is set up your config/database.yml file like this:

development:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_development
  pool: 5
  username: test_postgres
  password:

test:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_test
  pool: 5
  username: test_postgres
  password:

production:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_production
  pool: 5
  username: test_postgres
  password:

And the empty sqlite files:

development.sqlite3
test.sqlite3 

were still present in the db directory.

Then I started up postgres, and I used pgAdmin3 to connect to the postgres database server. (If that is confusing, see: How do I start enterpiseDB PostgreSQL on Mac OSX 10.6.8?)

Then I created a model:

~/rails_projects$ cd test_postgres/
~/rails_projects/test_postgres$ rails g scaffold Post title:string author:string body:text

      invoke  active_record
      create    db/migrate/20130807061320_create_posts.rb
      create    app/models/post.rb
      invoke    test_unit
      create      test/models/post_test.rb
      create      test/fixtures/posts.yml
      invoke  resource_route
       route    resources :posts
      invoke  scaffold_controller
      create    app/controllers/posts_controller.rb
      invoke    erb
      create      app/views/posts
      create      app/views/posts/index.html.erb
      create      app/views/posts/edit.html.erb
      create      app/views/posts/show.html.erb
      create      app/views/posts/new.html.erb
      create      app/views/posts/_form.html.erb
      invoke    test_unit
      create      test/controllers/posts_controller_test.rb
      invoke    helper
      create      app/helpers/posts_helper.rb
      invoke      test_unit
      create        test/helpers/posts_helper_test.rb
      invoke    jbuilder
      create      app/views/posts/index.json.jbuilder
      create      app/views/posts/show.json.jbuilder
      invoke  assets
      invoke    coffee
      create      app/assets/javascripts/posts.js.coffee
      invoke    scss
      create      app/assets/stylesheets/posts.css.scss
      invoke  scss
      create    app/assets/stylesheets/scaffolds.css.scss

Then I tried to execute the migration:

~/rails_projects/test_postgres$ bundle exec rake db:migrate
rake aborted!
FATAL:  role "test_postgres" does not exist
...
...

That error occured because I don't have a database user(or role in postgres speak) named test_postgres--and it's likely no one else will either. When I set up postgres, I created the user 7stud, which is my Mac user name. To fix that rails error, I just changed the username to 7stud on the three lines in config/database.yml. Edit: in fact, I didn't even need to do that. Because I am the user 7stud on my Mac, that means I am executing my rails commands as 7stud, and coupled with the fact that my postgres db was setup with a user named 7stud, that allows me to completely eliminate the username line in config/database.yml:

development:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_development
  pool: 5

test:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_test
  pool: 5

production:
  adapter: postgresql
  encoding: unicode
  database: test_postgres_production
  pool: 5

Then I tried executing the migration again:

~/rails_projects/test_postgres$ bundle exec rake db:migrate
rake aborted!
FATAL:  database "test_postgres_development" does not exist
...
...

Notice that the database.yml file specifies three database names. So I created the development and test databases:

~$ cd /Library/PostgreSQL/9.2/
/Library/PostgreSQL/9.2$ sudo su postgres
Password: <normal sudo password>
bash-3.2$ createdb -O7stud -Eutf8 test_postgres_development
bash-3.2$ createdb -O7stud -Eutf8 test_postgres_test

-O owner
-E encoding

Then I tried executing the migration again:

~/rails_projects/test_postgres$ bundle exec rake db:migrate
==  CreatePosts: migrating ====================================================
-- create_table(:posts)
   -> 0.0441s
==  CreatePosts: migrated (0.0443s) ===========================================

Success (but elsewhere on the net examples show different output with 'Notice' lines).

Then I started a server:

~/rails_projects/test_postgres$ rails s

...and in my browser I entered the url:

http://localhost:3000/posts

...and I created a couple of Posts.

Then using pgAdmin3, I tried to find the posts in the test_postgres_development database. First, I refreshed the 'Databases(3)' line in pgAdmin3 by right clicking on the line and choosing 'Refresh'. Then I searched through the test_postgres_development directory for a long time, with no luck. Under Schemas, I could see a Tables directory that contained a posts directory, so the table got created, but I couldn't figure out how to see if there were any entries in the table. It turns out, you have to right click on the posts directory, then select 'View Data', and voila there were the posts I created.

Or you can view the entries using the command line:

~$ cd /Library/PostgreSQL/9.2/
/Library/PostgreSQL/9.2$ sudo su postgres
Password: <normal sudo password>
bash-3.2$ psql -U 7stud test_postgres_development
psql (9.2.4)
Type "help" for help.

test_postgres_development=> \dt

             List of relations
 Schema |       Name        | Type  | Owner 
--------+-------------------+-------+-------
 public | posts             | table | 7stud
 public | schema_migrations | table | 7stud
(2 rows)

test_postgres_development=> \d posts

                                     Table "public.posts"
   Column   |            Type             |                     Modifiers                      
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('posts_id_seq'::regclass)
 title      | character varying(255)      | 
 author     | character varying(255)      | 
 body       | text                        | 
 created_at | timestamp without time zone | 
 updated_at | timestamp without time zone | 
Indexes:
    "posts_pkey" PRIMARY KEY, btree (id)


test_postgres_development=> select id, title, author, created_at from posts;


 id |         title          |    author    |         created_at         
----+------------------------+--------------+----------------------------
  1 | Hi there.              | Tom          | 2013-08-07 06:24:57.806237
  2 | Ola!                   | Nancy        | 2013-08-07 06:25:23.989643
(2 rows)

test_postgres_development=>