jump to navigation

Postgres in Cygwin for Rails 3 November 14, 2011

Posted by ficial in Blogroll, cygwin, ruby on rails, techy.
Tags: , , ,
trackback

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:

gem 'pg'

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.

Advertisements

Comments»

1. csd_666 - September 30, 2012

Awesome post! Thanks man! Saved a lot of time for me.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: