Postgres in Cygwin for Rails 3 November 14, 2011Posted by ficial in Blogroll, cygwin, ruby on rails, techy.
Tags: cygwin, heroku, postgres, rails 3
I’m using Rails 3 on cygwin (on Windows 7). I used the cygwin setup utility to install all the postgres related packages I could find (all the Postgres items in the Databases group). For rails I used default configurations / system setting as much as possible. This means, among other things, that I’ve been doing all my development using sqlite. That was actually working just fine… until I started doing deploys to heroku. At that point I ran into the infamous ‘PGError: ERROR: operator does not exist: character varying = integer’ problem. Somewhat reluctantly, I decided it was finally time to get Postgres running in my dev environment.
My Gemfile has:
and there weren’t any particular issues I ran into there (just the usual bundle install). The tricky parts were the database.yml settings, and getting postgres itself running. For the former I did a bit of googling and found a great set of examples at https://gist.github.com/961978. Adapting the postgres example was pretty easy. My database.yml looks like:
development: adapter: postgresql encoding: unicode database: proj_development pool: 5 username: projuser password: 12345dev # you may want something more secure here :P timeout: 5000 # Warning: The database defined as "test" will be erased and # re-generated from your development database when you run "rake". # Do not set this db to the same as development or production. test: adapter: postgresql encoding: unicode database: proj_test pool: 5 username: projuser password: 12345test # you may want something more secure here :P timeout: 5000 production: adapter: postgresql encoding: unicode database: proj pool: 5 username: projuser password: 12345prod # you may want something more secure here :P timeout: 5000
Getting postgres running took a quite a bit more research and experimenting. The difficulty I ran into was that much of the documentation out there is for systems other than cygwin, and cygwin has a number of idiosyncracies with respect to where various files live and that kind of thing. Two pages I found to be useful were http://beige.ucs.indiana.edu/I590/node149.html and http://www.postgresql.org/docs/8.4/static/installation-platform-notes.html. In addition to those I made heavy use of man and whereis to figure out where various commands resided and what exactly they did. The eventual sequence of successful (and relevant commands) was:
$ /usr/sbin/cygserver & cygserver: Initialization complete. Waiting for requests. $ cd $ mkdir pgdata $ cd pgdata $ /usr/sbin/initdb . The files belonging to this database system will be owned by user "Chris". This user must also own the server process. The database cluster will be initialized with locale C.UTF-8. The default database encoding has accordingly been set to UTF8. fixing permissions on existing directory . ... ok creating subdirectories ... ok selecting default max_connections ... 40 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in ./base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: /usr/sbin/postgres -D . or /usr/sbin/pg_ctl -D . -l logfile start $ createdb proj_development CREATE DATABASE $ /usr/sbin/createuser -P projuser Enter password for new role: Enter it again: Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n CREATE ROLE
Once all that was done I was able to run rake db:migrate (from my project folder, of course) to set up my tables, and then rails s to get my dev server going. Shockingly, those two steps went without a hitch, and I was able to use my application on my dev machine without any problem. Yay!
….except that heroku is still unhappy and gives the same ‘PGError: ERROR: operator does not exist: character varying = integer’ as I got originally. Dammit. :(
So, after re-examining all my migration files once more again from the start, I noticed this time that I’d some how used t.string instead of t.integer for a foreign key on an id field. Oops. I fixed that line, committed it, pushed to heroku, ran the db migration all the way down then back up, and POOF! IT WORKS!
Still, at least I learned something, and hopefully now you have as well.