Getting Started With PostgreSQL

Looking for a database that won’t cost you an arm and a leg? I
am. Join me as I take a look at what PostgreSQL has to offer.

What is It

PostreSQL is an object-relational database management system.
Nothing really more to say here except that, as you read on, it is a free
database system that is taking stabs at proprietary database systems like
Oracle, SQLServer, and DB2. It’s ability to find and fill niches from as low as
the hacker to the need of a start-up company and to the corporate data centers
is proof of it’s viability in the open-source community and robustness of a
true database engine.

Is it Really Free

PostgreSQL is released under the BSD license which states
that you can use, copy, modify, and distribute the software and its’
documentation for any purpose, without fee, and without a written agreement’.
Let me state that again, there is NO cost, NO fees, No licensing, and that
means NO PROBLEMS. Wow! Just the blanket statement I need to forge ahead and
not be concerned that I am doing something illegal. Of course, with this
statement you also have to accept that there are no warranties for any damages
what so ever that may happen when using the product. But then again what do you
expect from a true open-source license?

You can start the process by going to the PostgreSQL website
at http://www.postgreSQL.org. What
this really relates to for you and me is that we can play, develop and go to
market with no cost to database software. Yes, if I need more options or more
robustness I can then port to another database. But hold on, we haven’t gotten
there yet.

What is the Platform

When looking for a database to "use for awhile" or
in the case of free software I always need to ask myself "Do I have a
platform to run it." PostgreSQL has a variety of Unix platforms and also
runs on Windows with the help of Cygwin framework ( http://www.cygwin.com/ ). This is great for
me since I will typically spend a bit of time playing with a database on my
Win2K laptop while at work, or on the road while keeping my true installation
for work at home on my Linux Servers.

How Do I Get the Software

For the windows environment it is quite easy and if you have
installed Cygwin in the past, you might already have a copy of PostgreSQL,
since it downloads with Cygwin. To install PostgreSQL, simply follow the
procedures in http://www.postgresql.org/docs/faqs/text/FAQ_MSWIN.
For getting the software for Linux, you really need to go to one of the many
download mirror sites. Start by reading a little on how the distribution is
organized on the site by opening the file ftp://ftp10.us.postgresql.org/pub/postgresql/README.dist-split.
Then proceed to download the five tar balls at ftp://ftp10.us.postgresql.org/pub/postgresql/latest.

Ok, Does it have any Features

Of course, the database we choose has to have some features
or else it won’t offer us the ability to store and retrieve the information. Look
at Listing 1 that I cut off the PostreSQL web site. You can also take a
look at the press release (http://advocacy.postgresql.org/news/2002112801/
) on the latest and greatest PostreSQL version (7.3.2). Surely, you can see
that this database has enough to offer the casual user and is ready for prime
time. Stay with me for future articles on how things really work. For now I am
satisfied.

Listing 1:

Some of PostgreSQL features

  • Fully ACID compliant

  • ANSI SQL compliant

  • Referential Integrity

  • Replication (non-commercial and commercial solutions) allowing
    the duplication of the master database to multiple slave machines

  • Native interfaces for ODBC, JDBC, C, C++, PHP, Perl, TCL, ECPG,
    Python, and Ruby

  • Rules

  • Views

  • Triggers

  • Unicode

  • Sequences

  • Inheritance

  • Outer Joins

  • Sub-selects

  • An open API

  • Stored Procedures

  • Native SSL support

  • Procedural languages

  • Hot stand-by (commercial solutions)

  • Better than row-level locking

  • Functional and Partial indexes

  • Native Kerberos authentication

  • Support for UNION, UNION ALL and EXCEPT queries

  • Loadable extensions offering SHA1, MD5, XML, and other
    functionality

  • Tools for generating portable SQL to share with other
    SQL-compliant systems

  • Extensible data type system providing for custom, user-defined datatypes
    and rapid development of new datatypes

  • Cross-database compatibility functions for easing the transition
    from other, less SQL-compliant RDBMS

What is the Support System

One of the things that impressed me the most when browsing
the PostreSQL web site was the depth of community. By this I mean the amount of
people that are sold on this open-source database and are contributing to its
success. There is an actual set of manuals, published books, mailing lists to
join at (http://archives.postgresql.org/
), independent projects at (http://gborg.postgresql.org/
), real technical documentation (http://techdocs.postgresql.org/
), and consultancies that will help you out if need be. You can also get general
"PR" information at http://advocacy.postgresql.org/
to learn about who PostreSQL is, the
advantages to using open-source software, read a few case studies, and get
involved in its success. Without the community of developers and contributors
of this database, I surely wouldn’t be interested in using it. After all, you don’t
want to hit a brick wall and not have a support system behind you.

Where to Go Now

After going through a deep dive into the PostreSQL web site,
looking at the documentation available, seeing the support system behind it,
and personally looking forward to an open-source database solution that is cost
effective, capable, ready for prime time, and free, free, free, lets go
download and install. I’ll keep you posted.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles