Open Source .NET Development [Electronic resources] نسخه متنی

PostgreSQL

PostgreSQL is a multi-user relational database. The community support for PostgreSQL is very strong; in fact, PostgreSQL recently distinguished itself by powering the Internet .org namespace.[2] Getting into PostgreSQL is beyond the scope of this chapter, but for a good book, see:

[2] See http://cw.idg.com.au/idg2.nsf/All/2ADD84E6EBCEADE9CA256CB30075FA01!OpenDocument.

PostgreSQL: Introduction and Concepts

by Bruce Momjian

Addison-Wesley

PostgreSQL does run on both Linux and Microsoft Windows but does not have a native port for the Windows platform (like the MySQL database does). In this chapter, our example will be designed to run on both platforms.

Installation

Since most mainline Linux distributions install PostgreSQL by default, there is not much to installing the database except maybe for applying the latest upgrade. Thus, this section of the chapter will mainly focus on installing PostgreSQL on Windows. PostgreSQL requires many features of the Unix platform that do not exist on the Windows OS.[3] Therefore, PostgreSQL requires the use of a "Unix-Like" environment running within Windows.

[3] A native Windows version of PostgreSQL (not requiring cygwin) is due out this summer. See Interview with the PostgreSQL Team, by Eugenia Loli-Queru - Posted on 2003-04-21 17:26:22 (http://www.osnews.com/story.php?news_id=3341&page=1).

Cygwin

Cygwin (http://www.cygwin.com) is an Open Source project to bring many of the Unix-based operating system features and tools to the Windows platform. Figure 10-1 shows the installation utility for Cygwin.

In Figure 10-1, the far left of the figure is the Skip column. The way this installation tool works is similar to a normal tree control. At the very top level, you can set this column to Skip, Default, or Install. Each child inherits the setting of the parent. For instance, if you set the top level of the tree to the Install option, the latest version of every child of the tree will be installed. Also, you can go to each child node and change the setting.

TIP

When you click on a child node, the tree node behaves like a multi-state button. This takes a little getting used to; however, it allows the get feature of installing a specific version of the product. For instance, the options change like this with each click: Skip, Install latest version (watch the version column), Install next to latest version, then back to Skip again.

After installing Cygwin, you can run it by using the desktop icon (if you installed it), or you can use the Cygwin icon in the Start Menu. This results in a command window opening with a Unix shell running. For the examples in this book, the Cygwin install was configured at the very top level of the tree as Default. Scroll down to the databases section of the installation tree (shown in Figure 10-1) and click on the PostgreSQL child node of the tree to install the latest version.

Running PostgreSQL using Cygwin requires that ipc-daemon or ipc-daemon2 be running to service the IPC calls from the database. Under Unix, these are daemons that run in the background. In Cygwin, they can be configured to run as a Windows service, but that requires a massive amount of work. So the command Windows in the background of Figure 10-2 shows IPC-Daemon2.

To run the PostgreSQL database engine, just type postmaster D [path to the database], but first the database must be created by using the initdb utility. Both are shown in Figure 10-2.

After running ipc-daemon2, run initdb. For more information, see the PostgreSQL online documentation or the man pages for PostgreSQL and initdb. The steps under Linux for creating and running the PostgreSQL database are identical.

Administration Tools

Like most other Unix-based programs, PostgreSQL has a number of command-line tools. However, to speed up development, there are a number of other Open Source projects that help with the administration and development of PostgreSQL databases.

Conversion Utilities

PostgreSQL provides conversion tools to convert from other database engines. Some of the supported conversions are:

MS Access
http://gborg.postgresql.org/project/access2pgconverter/projdisplay.php

MySQL
http://gborg.postgresql.org/project/mysql2psql/projdisplay.php

These projects can be helpful in converting existing database projects over to PostgreSQL.

Graphical Tools

PgAdmin III (http://pgadmin.postgresql.org), shown in Figure 10-3, is a very intuitive and time-saving tool. This tool allows you to easily administer and graphically design a database. You can create databases, tables, and stored procedures and perform many other operations. This product will be very familiar to anyone who has used Microsoft SQL Server Enterprise Manager and Query Analyzer.

TIP

A Web-based application (PHPPgAdmin) similar to PgAdmin III is also available at http://phppgadmin.sourceforge.net.

To get PgAdmin working and to configure PostgreSQL to accept TCP connections, you must edit the PostgreSQL.conf file, as shown in Figure 10-4.