PostgreSQL - Creating a New Tablespace and Database

Using pcAdmin III it is straightforward to setup a new Tablespace and Database for use with your PostgeSQL installation. The only 'gotcha' is that you need to specify an absolute path to the Tablespace and the user 'postgres' must be the owner of that directory.



On Windows you can set the owner using the properties of the directory, on the Mac however you will need to so something similar to this:

sudo chown -v postgres data

Make sure you have changed into the parent directory of 'data' before you execute the above command.

www.artenscience.co.uk
Quality Commercial Software and Custom Software Development

PostgreSQL - First Things First

After installing PostgreSQL the installation is available to access only from the local machine. If however you have installed it on a remote server or VM you will initially be unable to connect. This is because (sensibly) PostgreSQL comes with a highly restrictive table of allowed connections. Therefore the first thing to do is give yourself access to your PostgreSQL database from over the local network.

These are the steps to follow to set this up:

From the machine which contains the PostgreSQL installation, launch the pGAdmin III application. If you don't have this you should run Application Stack Builder included with the PG installation and download it.

Once pGAdmin III is launched it will look something like this:



From the File menu select the option 'Open pg_hba.conf...' This will open a dialog asking you the location of this file. Navigate to your data directory and select the pg_hba.conf file.

You will then see this:



Double Click the third line down in the list and populate the window as follows:



Press OK to close the input window, close the list window, answering Yes to the prompt to Save changes and close pgAdmin III.

You now need to Restart the PostgreSQL server process using the shortcut installed by the PG installer.

Your PG database will now be accessible from your Local Area Network (assuming you use the IP Range 192.168.0.X, if you use a different IP Range then substitute it when editing the pg_hba.conf file.)

www.artenscience.co.uk
Quality Commercial Software and Custom Software Development

The Most Appropriate Open Source Database ?

An upcoming development of mine ideally needs to store data in a powerful, multi user relational database system. Not a problem - I'm a big fan of Oracle and use it daily. The fly in the ointment this time is that due to the nature of the software the database system needs to be inexpensive, ideally free.

There are several 'free' database systems to choose from. the two most well known and highly rated are MySQL and PostgreSQL. Of the two MySQL seems to be great for 'quick and dirty' data storage while from the research I have done PostgreSQL seems to be far more advanced and with features nearer to what I would expect from Oracle.

I have experience of using MySQL, both locally on my Mac and also as part of the Plesk installation on my dedicated web server. It seems straight forward enough and would probably suit my needs, but the more I read the more drawn towards PostgreSQL I am becoming.

A big benefit of both of these database systems is the ability to install the server software on my Macbook Pro, a big plus when traveling, as Oracle doesn't install on a Mac and requires a seperate VM on which to run.

A very fast download and equally fast installation on my Mac left me very surprised. PostgreSQL really feels very professional and even comes with some nice admin tools. I tried the same exercise on one of my virtual Windows 2003 servers with the same result.

I have yet to do any extensive testing with PostgreSQL but I have already ruled out MySQL. Far from being free, it cannot be used with my commercial application without I first purchase a license. Apparently MySQL is released under the very restrictive GPL license and this effectively means that if I use MySQL as my data store then my commercial application falls under the same licensing - in other words I have to make it Open Source. Which I am not prepared to do.

PostgreSQL on the other hand is released under the far simpler BSD license. Which you can read below: (Click to enlarge the image)



This is a much better fit for my purpose. I'm not sure why MySQL appears to be so much more popular given the restrictions of the licensing ? Maybe it is just easier to use ? Maybe people do not realise how restrictive the licensing is ?

Anyway, it's full steam ahead with evaluating PostgreSQL then. The elephant takes the lead :-)

Note: Another big plus point in favour of PostgreSQL from my point of view is that native drivers are available for my current development platform of choice, REALbasic. No middleware needed ... If PostgreSQL turns out to be as good as I suspect it is, it may well replace Oracle in some of my future higher value commercial applications.



www.artenscience.co.uk
Quality Commercial Software and Custom Software Development