Change dev and test database to postgres?

[Bill Rosgen]

Commit 729c01 changes the database configuration file to switch to a postgres database -- this has caused the developlment and testing versions of the site to fail for everyone who isn't running a postgres. I don't know if this affects anyone other than me and Aram, but I thought that I should point it out.

I agree that it's probably best if the production database matches the development and testing databases, so now is as good a time as any for a switch (sqlite and postgres do not always agree, which has caught me in the past). This will add a working postgresql installation to the required pieces of development software.

Provided no one has any strenuous objections, I'll finish the switch to postgres and commit the new configuration files later today (i.e. several hours from now).

Bill

[aram harrow]

Fine with me. Youv'e done the change already, right? I'm working on
getting it running properly now.

[Bill Rosgen]

Yes, the configuration should now be switched over.

You may want to run 'bundle install' to make sure that you have the pg gem. Otherwise you need a postgres installation that responds to the role/user 'scirate'. To set it up I followed the instructions at http://railscasts.com/episodes/342-migrating-to-postgresql . From your commits earlier, Aram, it looks like you've already done most of this, so it will hopefully more of less just work (you may need to create the scirate role in the db -- the only real change I've made is to switch the database username to 'scirate').

For completeness (and to help anyone else who is about to do the same), I went through the following steps to set up postgres. Clearly you can change the paths to whatever makes sense on your development machine.

1) I needed to first initialize a postgres database with:

initdb /usr/local/var/postgres

2) Then I needed to start the server with:

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

3) To add the role to the database I needed to do the following (the second two lines are from the postgres console).

psql -d postgres
postgres=# create role scirate login createdb;
postgres=# \q

4) Finally, you probably want to run "rake db:create:all" and/or "rake db:migrate".

5) To grab a snapshot of the current production database from heroku, you *should* be able to do "heroku db:pull". I find this is less than reliable with my internet connection in Singapore, so I instead downloaded a backup using heroku pgbackups and imported it by hand.


Bill