Getting PostgreSQL to install on Windows

In the effort to migrate my existing Rails app from Slicehost to Heroku, I encountered some MySQL – PostgreSQL compatibility issues, I’ll write a separate post on Heroku gotchas later. In order to debug those issues, I need to install PostgreSQL on my local Windows development machine. Did you think it’ll be trivial? Of course not, nothing open source is ever trivial on Windows…. nothing, trust me on that.

First thing first, install PostgreSQL (V8.4.2-1 as of this article’s date).

And silly me, first thing I tried was gem install postgres, not even close. A little research led me to this article to try gem install ruby-postgres. That seemed to work.

Next I tried to run rake db:create, if memory serves me correctly, I ran into this error

Can’t activate rubyforge (= 0.4.4, runtime), already activated rubyforge-1.0.0 (Gem::Exception)

To fix it, I followed the advice in this article.

From here onwards, I kept trying rake db:migrate and got several other errors for libpq.dll, libeay32.dll, ssleay32.dll, and a whole slew of other missing DLLs. According to this article, it said to

You need to go back to the postgres installer file and
copy a couple of libraries to the ruby “bin” file (for example in
c:\ruby\bin\)

* comerr32.dll
* krb5_32.dll
* libeay32.dll
* libiconv-2.dll
* libintl-2.dll
* libpq.dll
* ssleay32.dll

Don’t overwrite any files if they already exist in the ruby directory !

I tried the advice without much success because I kept getting DLL errors like missing DLLs and like

ruby.exe – Ordinal Not Found
The ordinal 284 could not be located in the dynamic link library SSLEAY32.dll.
and
The ordinal 2821 could not be located in the dynamic link library LIBEAY32.dll.

Bottom line, don’t try the advice from the above article, it is outdated and there are new DLLs added to PostgreSQL since then, restore ruby\bin to what it was if you did try. Then I came across this article saying to add C:\Program Files\PostgreSQL\8.4\bin to your Windows path which as it turns out, installing PostgreSQL didn’t do, why I have no freaking idea.

Anyway, after adding the path, I’m still getting the ordinal could not be located in the DLL errors for LIBEAY32.DLL and SSLEAY32.DLL. It turns out that those 2 DLLs support OpenSSL and my machine is littered with multiple copies of it from cygwin, Google, SVN, Ruby, Postgres, VMware, etc. It’s a freaking mess. I took a cue from from this posting and copied those 2 files from C:\Program Files\PostgreSQL\8.4\bin to C:\ruby\bin. First, rename those 2 files in C:\ruby\bin to something else just in case you need them for some other reason before you copied them from PostgreSQL.

The final thing you need to do is to change your database.yml file to something like this

development:
adapter: postgresql
database: DATABASENAME
username: YOURUSERNAME
password: YOURPASSWORD
host: localhost
encoding: UTF8

And now run rake db:create and rake db:migrate and you should be good to go. I think I got a headache writing this post.

Getting Git to work on Heroku on Windows using Putty / Plink / Pageant

Ok, so I have heard from several Rails developers about how much they like Heroku . One reason is that the basic version is free though you are only allowed a max of 5MB for the database. I decided to give it a try in my attempt to migrate off slicehost which I’m currently using.

Boy, I had no idea what I was in for trying to get an app uploaded (not even running yet) to Heroku. I ran into more errors that I can shake a stick at especially since I’m using Putty / Plink / Pageant to handle ssh. First off, I’m new to Git, the word in the open source community is that all the cool kids are using it and in Heroku’s case, it’s not a choice but a necessity, it’s the only way they allow you to upload code to their server.

So off I go to install Git on Windows and the first thing I suggest you do is to read this excellent post on Git on Windows install because trust me, you will be confused with what choices to accept during installation. Here are my choices

1. Screen 1: Run Git from Windows Command Prompt
2. Screen 2: Use [Tortoise]Plink

Since I use Putty (not Tortoise), I set GIT_SSH to C:\Program Files\PuTTY\plink.exe, notice no quotes around it.

3. Screen 3: Use Unix style line endings

Next, I installed a couple of gems

• gem install rest-client –version 1.3.0

It has to be V1.3.0 (latest is V1.4.0) or else “gem install heroku” will barf on you.

• gem install heroku

Get your Rails app ready, either create a new one or use an existing one. If you are currently using SVN with your app, I suggest you export a fresh copy to a new folder and use that instead because I have no idea if SVN and Git will play nice together in the same app.

Here’s Heroku quickstart guide on deploying an app, it’s written more for Mac / Linux developers though the basic steps are the same.

Follow these steps to get your application added to your local Git repository

1. change folder to your app root folder
2. git init
3. git add .

You might see a a bunch of warning messages about CRLF being replaced by LF, no biggie.

4. git commit -m “first commit”

Next comes the tricky Putty / Plink / Pageant setup. First off, make sure you have the latest Putty version installed (V0.6), if not, you will get a nondescript error later on

Unable to read from standard input
fatal: The remote end hung up unexpectedly

Now, setup your SSH public/private keys using PuTTYgen, I used SSH-2 RSA. Once the key generation is done, click “Save private key” and save the private key to a folder of your choice, name it something like heroku_key.ppk. You can save the keys with or without a passphrase, it’s up to you, I didn’t. Next comes the public key but don’t click “Save public key”, instead open up notepad, copy and paste the public key string at the top of the PuTTYgen window and save it here

C:\documents and settings\(username)\.ssh\id_rsa.pub

Launch Pageant and add heroku_key.ppk to it. If you wish to auto launch Pageant at startup, you can add this link to Windows startup folder

“C:\Program Files\PuTTY\pageant.exe” “C:\Documents and Settings\(username)\(folder)\heroku_key.ppk” “C:\Documents and Settings\(username)\(folder)\some_other_key.ppk”

Notice that I added 2 keys to pageant upon launch, that’s because I have an existing key pair for slicehost, you only need to add the heroku_key.ppk.

To avoid getting this error when using plink,

The server’s host key is not cached in the registry. You have no guarantee that the server is the computer you think it is. The server’s rsa2 key fingerprint is: ssh-rsa 2048 8b:48:5e:67:0e:c9:16:47:32:f2:87:0c:1f:c8:60:ad
Connection abandoned.
fatal: The remote end hung up unexpectedly

Open PuTTY, in the hostname box, enter “heroku.com” and click Open. You’ll receive a prompt “The server’s host key is not cached in the registry”. Click Yes then close Putty (don’t bother trying to log in), Heroku’s host key will now be cached.

Now follow these steps to complete deploying your app to Heroku

• heroku create
• git push heroku master
• heroku rake db:migrate

And you are done and you can thank me later. What a PITA to get it working on Windows but I hope this will save someone the same pain I went through.