Zac Bowling’s Mind

This is one of those things that is under-documented. I’m not going into specifics of how to use Postgres, so this is really for anyone that knows a little about it or is trying to follow some generic platform inspecific instructions on setting up something with it and is stumped by the setup in Debian.

My hope is to help people searching Google for answers like I did (which currently sucks) and which I had to go read the code to figure out. So here is a few tips on using Postgres on Debian using the distribution packages. (I hope this information is indexed well by Google for all so you like it, then digg it or share it or what ever social networking service type link it so this posts index will go up).

In Debian, you are allowed to install multiple versions of Postgres side by side thanks to some magic package called “postgresql-common” (link is for `sid’ as of November 13, 2008 so ask these guys on the Debian postgres packaging project if things have changed much by the time you read this blog entry).

They do this by installing Postgres into multiple directories. As you can see from listing the configuration folder in /etc/.


polystimulus:~/# ls /etc/postgresql/
7.4 8.0 8.1 8.3


One directory for each install basically. They go on step below that even and create this concept called “clusters”. By default each install has a built in “cluster” called main. You can create your own, possibly for each DB, if you want.

Clusters are made to make upgrading from each Postgres version up, easier. Since different packages require different versions of Postgres, and also because end users use Postgres and may not want to be forced to update their code if the distribution wants to upgrade, this works out great.

The problem is that most things assume that normally only have one postgres install, and that is always at one specific port, port 5432, and that at the shell, first “psql” executable in your path is the Postgres client you want to use. Unfortunately with multiple versions running around that assumption doesn’t quite work out right.

In this case we have to do a bit of configuring, tinkering, and setting up our environments.

For the command line tools for postgres, each of them is wrapped by a shell script. This shell script detects the version and cluster you want to use and directs you to the appropriate command line client. It can easily configured using the PGCLUSTER enviroment variable.

I’m not sure which Postgres install gets the port for 5432 by default (on mine it was the 8.1 install for some reason). You can change the port each postgres instance is running under by editing /etc/postgresql/<version>/<cluster>/postgresql.conf

Be sure to read up on clusters, because they are will be a time saver in the future. Read the man file on pg_createcluster, pg_lscluster, pg_ctlcluster, and pg_upgradecluster especially.

As frustrating as having to edit every script I get now to give it a port or new Unix socket address, I still love these tools and I wish I had these tools a few years ago.

What is supprising is that mysql doesn’t have something like this in Debian.

I post this because LeadSwami.com is powered by Postgres and have had my head deep in PostGIS the last few days, geocoding all the addresses in our 19 million records. So much data!

Since I need to geocode for my project on LeadSwami.com, I decided to make the geocode api public. Check it out:

http://leadswami.com/api/

Enter in an US address, and it will return the latitude and longitude of the address. If you enter a fuzzy/badly formed address, it will return a number of listings with ranked results. It’s really pretty nifty.

Free for personal or comerical use as long as you keep it under 5000 requests per day. Only restriction is that if you use it in on the web for anything queried live, give a link back to LeadSwami.com. If you need more access, please contact info@leadswami.com.

Oh you evil Bogons! A bogon is a ip range that has no legitimate use, so if you get a packet from one from an address in that range, its probably safe to drop it.

I’ve been tracking down a problem with my server where people were unable to hit my server from different places. Tracking the issue down I figured out its DNS. Its funny because I haven’t tinkered with bind in nearly a year and it had been work and still worked from.

Well today I figured it out. My server came preloaded with a list of these bogon networks. The problem is that the bogon list changes all the time as the verious registries start allocating more IPs in our world of lessing number of IPs in the IPv4 space.

I was blocking the IPs allocated by AT&T/Waypoint access points (used at starbucks). It took me a few hours but figured out. Evil bogons!

A little over 5 weeks ago, I went back to working for Telligent Systems again. I originally worked for them back when they had only 10 people a little over 3 years ago, and now they are in the hundreds. They are growing like crazy and having a lot of success.

My stay there was was sort lived though. I came on to work on a little research product but shortly into it, the project was canceled so I was no longer needed. While I’m upset because it was a little unexpected only a few weeks into it, I have no ill feelings towards Telligent though. I came in on a contract basis and that’s what happens when you play the contract game. It’s businesses and everyone has to stay competitive. They said that the company wants to focus more on their core products and do need me anymore and that’s pretty much it.

What is upsetting is that I under the impression that I would of been there a bit longer then a month though. I had turned down other offers to work with Telligent because I believed that I would me secure there in the current economy which didn’t quite turn out to be the case.

In the mean time, I’m working on my little side project, LeadSwami.com. It’s still alpha but we have over 19 million searchable businesses. Getting geocoding done and a resonable search soon.

This is only a test.

— Post From My iPhone