How I fixed my PostgreSQL re-install problems

As mentioned in an earlier post, I had some trouble re-installing PostgreSQL. For some obscure reason re-installing PostgreSQL (postgres) locally wasn’t possible anymore. Over time something (or someone, probably me) changed the system in a way that would always get me errors in the last steps of the PostgreSQL Windows installer. The part were the installer tries to set up the basic database structures.

The first time I met the problem I didn’t bother fixing it, the central database worked just fine for me. As time passed tracing back your steps becomes harder. As long as there was no pressure and time to spend on the problem, I stopped after a few fruitless attempts at finding the culprit on each try. This time was different. Determined to squash this inconvenience permanently, I decided not to stop searching. After a while some mailing posts seemed to lead to a solution to the problem.

It turned out the environment variable COMSPEC had a value that confused the install script of the PostgreSQL installer. The value was not a path to a shell executable like command.com or cmd.exe. The installer script being written in vbscript would run differently when a system or user variable with that name existed and the value was not a valid path to a shell executable. This seemed is probably an interpreter feature and not a bug in the script. When I dropped the variable, the install finishes successfully. I didn’t look for further, but one of the posts said that restoring the variable to its original value wouldn’t pose any problems.

Benefits of running your own test database

Setting up your own work environment proper and suiting your needs is an important and often undervalued part of software development. One of the things is to consider where and how to test the code. In big teams you have to conform to the standards or come up with a common understanding. I happen to have more freedom in this aspect, which made me change from using the “everything works on my pc” idea to the “keep everything centralized” one and then to the current one, a mix of both.

It has been only recently that I got it the way I wanted, more about that in another post. In the current configuration there is a production database, central test database that conforms with the latest changes in the version control system and a local test instance which has any changes which have not been committed yet. The main driver being that among others the continuous integration (CI) uses the central test database for testing and I want to continue development without breaking tests run by the CI server.

The advantages of my new configuration are:

  • the CI server can run its test without interference by developers
  • developers can their test without outside interference
  • speed, database access is less of a bottleneck

The disadvantages are:

  1. developers need their own test database to prevent interference
    1. the developer must update his test database schema with the latest changes from the central test database schema
    2. the developer must update the central test database’s schema, if necessary
  2. unrealistic performance expectations

Disadvantage 1 isn’t that hard to fix. For disadvantages 2.1 and 2.2 the solution is to restrict the number of developers on code that interacts with the database through a persistence subproject. And the last disadvantage might exist on several levels. The test database’s server might have different performance from the production server, which will certain differ between clients.

In conclusion I think I made my world a little better through these improvements. Another night with happy dreams ahead of me.