November, 2000 - NT Internet Goodies

by Andy Bruce

Last issue, we covered Windows 2000, secure Telnet and FTP, and some remote control tools. In this issue, we continue our tour of free database servers for Windows NT by looking at the highly regarded PostgreSQL package. This turns out to be somewhat of a challenge, since I could find no NT ports of either the database server or the database client software. So my goal became twofold: first, to locate and build the latest versions of the software; and second, to provide a handy NT binary that you can use on your own machines. While I don't have the full binary needed to run the database (I need to do more testing to verify my build), I provide a full set of build instructions and required patch files for your benefit.

PostgreSQL (pronounced Post-Gres-Q-L)

A Quick History

PostgreSQL (free software, http://www.postgresql.org/) originated as Ingres in 1977, a database management system written at UC Berkeley. In 1986, the name became Postgres and the server was rewritten to use an object-relational model. In 1995, the name changed to Postgres95, and only after 1996 did the software become the PostgreSQL we have today. PostgreSQL is free software for all purposes (business and commercial) as long as the software copyright notice remains in place.

PostgreSQL is research and Internet software in the truest sense. Graduate students, undergraduates, and staff programmers at UC Berkeley wrote the original DBMS under the direction of Professor Michael Stonebraker. Today, a worldwide virtual team of developers provides ongoing enhancements, and it's certainly possible for any interested and talented developer to become a part of this system (download the distribution for details).

Currently, PostgreSQL compares favorably to many commercial database systems as well as to the highly regarded MySQL database server. You can get the latest copy of the distribution at ftp://ftp.postgresql.org/.

Preparing Your Machine

Building PostgreSQL on NT isn't for the faint of heart, so I'm providing a working NT port of this database server for your benefit. But as often happens when I'm researching these articles, I found so many interesting tools and tidbits of information that it's well worth your while to "follow along" with me. You'll end up with additional free NT utilities, and you'll gain a greater understanding of how the utilities work.

PostgreSQL provides a nice document for building on NT after you download the distribution. Since you can get the document only if you download the distribution, I've put a copy of it on the Web page for this issue of Enterprise Solutions. The file name is Readme.nt I used it to find out the following information on setting up the machine so that I could build the package.

Cygwin

You must install a supported version of the Cygwin package (freeware, http://sourceware.cygnus.com/cygwin/download.html). I covered this set of UNIX utilities in March of 1999, and it's a fine package with lots of useful UNIX tools. The latest version is B20.1 ("bash version 20.1"). You can download it from any of the listed FTP sites, and you must have it installed to run the database server.

The setup process changed from the last time I wrote about Cygwin. Formerly, you downloaded a single executable (either full.exe or user.exe, depending on your tool requirements). Now, you can use the dynamic setup.exe program provided on any of the FTP sites. As an example, if you download the latest copy of Cygwin from the Cygnus main site, you find the setup program at ftp://sourceware.cygnus.com/pub/cygwin/latest/setup.exe. This setup program isn't really that great; it simply downloads all the compressed images from the FTP site and runs an extraction utility to copy the files to your local disk. You must still do some manual work, including setting any environment variables such as the system PATH.

You'll notice that the FTP site contains many directories. Each directory contains a "package"--a set of utilities or core programs. For example, the bash directory contains a single compressed file containing the bash shell. The automated setup process simply copies all the packages down.

I chose the automated setup, which prompted me for the download type (from Internet, install from local directory). I chose "Download from Internet" and came back the next morning. Unfortunately, I found that most of the packages did not get downloaded! So I manually downloaded all the packages it missed, and reran the setup program. This time, I selected "Install from local directory" and the program ran like a charm.

Once I installed Cygwin, I needed to set up a batch file to start it. The setup program kindly provided me with a starting batch file in my installation directory (C:\Cygnus). You can use this batch file to begin a "bash" shell.

I wanted to keep my installation as simple as possible, so I created everything (almost!) on drive C:. One of the nice things about Cygwin is that it supports links (at least on NTFS drives), so I could install things on a separate drive but set a link to it from my "/" (root) drive.

Andy Piper's Utilities

The FAQ for building PostgreSQL for NT recommended that I download this free set of extended UNIX utilities from http://www.xemacs.freeserve.co.uk/. I went to the site, and the first thing I discovered was a nifty Cygwin verification tool located at http://www.xemacs.freeserve.co.uk/check_cygwin_setup.sh. After installing Cygwin, download this file and then execute from a Cygwin bash shell (not a DOS command window). You can run it by using the following:

<download_dir>/check_cygwin_setup.sh

On my box, I downloaded the file to d:\download, so my command was:

//d/download/check_cygwin_setup.sh

This did all sorts of verification for me, and built a sample "hosts" file that the Cygwin binaries could use for Internet access. However, this file conflicts with the existing "hosts" file setup in my C:\WINNT\system32\drivers\etc directory. I solved the problem by creating a UNIX-style link under the bash shell, as in:

cd /etc
rm ­fr hosts
ln ­fs //C/WINNT/system32/drivers/etc/hosts hosts

This created a file in my Cygwin installation at /etc/hosts that points to the official "hosts" file for my NT installation. It also demonstrates that Cygwin implements the UNIX ln command on NT--a nice addition!

Once I ran the verification script, I installed the actual set of utilities (ftp://ftp.xemacs.org/pub/xemacs/aux/cygwin-b20-local.tar.bz2)following Andy Piper's instructions. Since the file is a "bz2" file, I needed to run the bunzip2 utility (provided in the Cygwin installation).

A Custom "ipc-daemon" for NT

PostgreSQL clients on NT use the "IPC" package to communicate with the PostgreSQL database server. Unfortunately, Cygwin doesn't come with a copy of this utility, so some Very Nice People have provided a port of it. The README file for PostgreSQL documents how to build and set up this package, but it still required quite a bit of effort.

You get the original package from ftp.postgresql.org/pub/NT_Support_Files/cygwin32_ipc-1.03.tar.gz. Because the version posted on the FTP site isn't completely ready for prime-time (as the PostgreSQL README.NT file details), I've included a "patched" version of the distributable on the Web page for this issue that I used when building the PostgreSQL database. This file is called cygwin32_ipc-1.03.zip.

Within the package exists another README file that details how to install the binaries (which I also provide). You must download this package and follow the README instructions to get this file installed properly on your computer! And you must have this package installed properly for the database server to work!

Installing PostgreSQL

After downloading the PostgreSQL distribution from the FTP site, I installed it on my D: drive (in D:\postgresql). In fact, this brings up another point. For space reasons, I wanted all the source and binaries for the database server to be on drive D:. However, my Cygwin installation is on drive C:. I was concerned about this, since the Cygwin readme clearly states that it expects to be installed at /usr/local/pgsql, and the /usr Cygwin mount is on my C: drive. (Perhaps the database server simply wouldn't work without tons of tinkering!) Then I remembered that Cygwin supports UNIX-style links, and my problem was solved. After installing the PostgreSQL distribution, I saw it was actually located at D:\postgresql\postgresql-7.0.2, since the downloaded source code has postgresql-7.0.2 embedded in its paths. I simply did the following to get what I wanted using a bash shell:

mkdir //d/postgresql/postgresql-7.0.2/pgsql
ln ­fs //d/postgresql/postgresql-7.0.2/pgsql /usr/local/pgsql

This worked perfectly, since the verification script I'd run as part of installing Andy Piper's Utilities creates /usr/local for me if necessary.

On most UNIX installations, third-party programs (such as Oracle) generally go under the /opt directory (e.g., /opt/oracle8). Since conceptually PostgreSQL fits in the same category, and since I wanted an easy way to get to the PostgreSQL directory from my Cygwin "/" (root) directory, I did the following from a bash shell:

mkdir /opt
ln ­fs //d/postgresql/postgresql-7.0.2 /opt/postgresql

Now, within a bash shell, all I needed to do to get to the PostgreSQL directory is type cd /opt/postgresql, which is certainly easier than using the full directory path.

The NT-specific documentation exists under D:\postgresql\postgresql-7.0.2\doc\README.NT. (I've used this file above to detail how to configure a machine to build the database server.) Following the README.NT file, I needed to modify the file D:\postgresql\postgresql-7.0.2\src\template\cygwin32 to update the YFLAGS parameter. The default file that comes with the download says:

YFLAGS:-d ­L /sw/cygwin-b20/share

The "-L" is a "library path" entry, so all I did was do a search for any directory named "share" in my Cygwin installation by doing the following from a bash shell:

cd /
find . ­name "share" ­print

This found the share library at /usr/local/share, so I updated the line to read:

YFLAGS:-d ­L /usr/local/share

Configuring the Build

In order to build the database server, I needed to run a pregenerated script file called configure that exists in the src directory. This file configures the build to work based on the operating system and the available compiler. The file actually gets generated by a completely cool program included in the Cygwin distribution called autoconf, which allows you to automate all sorts of tests to make your C source portable. (In a later issue, I'll go over this tool to show you how it can help you build portable code--it's awesome!)

I was hoping the configure process would work as-is, since I'd taken extraordinary pains to make sure I understood where to put files, and that I'd downloaded the correct archives. As is so often the case, however, it didn't work quite correctly. At the very end of the configuration process (which performs around 150 separate tests and actions), a file called dummy.s couldn't be found. I poked around in the configure script enough to see that the file was required for something called a "taz"--which the configure file indicated wasn't needed for Cygwin running on NT. I fixed the problem by creating an empty file named dummy.s in the D:\postgresql\postgresql-7.0.2\src\backend\port\tas directory. Then, the configure worked correctly.

Running Make and Fixing Compiler Errors

The makefile included with the distribution requires the GNU make utility, which comes with the Cygwin distribution. It must be GNU make Version 3.76 or higher; you can find your version by using the following command:

make ­version

Once I verified make on my machine, I simply typed the command make using a bash shell in my /opt/postgresql/src directory (remember that I created a link using this path in the instructions above). Although I had hoped the make would succeed the first time through, it failed after compiling hundreds of source files.

/usr/include/cygwin/cygwin_dll.h

The build failed when trying to build the NT-specific DLL (NT shared library; called a dynamic-link library) entry point in the file src/utils/dllinit.c. I looked at the file, and traced the compile error to the /usr/include/cygwin/cygwin_dll.h header file. This header file has a few errors in it that the super-picky GNU compiler (provided with Cygwin) catches, but that other compilers couldn't care less about. I changed the file, and I've put the corrected text on the Web page for this issue; it's called include_patch.zip. Note that when you download these patches, you can apply them directly to your /usr/include directory (the ZIP file contains the relative path).

/usr/include/sys/errno.h

The next build failure occurred when I received an "undefined reference to _sys_nerr" in a couple of source files. At this point, I was feeling low, since I couldn't understand why the build had these errors--after all, NT was listed as a supported platform! However, since the database server is used by thousands of NT developers around the globe, I didn't despair. Rather, I tracked the problem down to /usr/include/sys/errno.h (that's right--a system include file!). On line 23, it was declaring an external reference to a variable named _sys_nerr, and (obviously) that variable wasn't being defined anywhere. I commented out the line in question, and added a new line so that lines 23 and 24 of the file now read:

/*extern int _sys_nerr __declspec(dllimport);*/
#define _sys_nerr 0

The second line (#define _sys_nerr 0) effectively removes support for the variable. I further investigated usage of the _sys_nerr variable in the two affected source files (src/backend/utils/error/elog.c and src/backend/utils/error/exc.c), and saw that the variable wasn't required since the files already use the standard errno variable to check for system errors. All I had to do in the source modules was comment out the line that declared the _sys_nerr variable to be external. I've placed this file in the same archive file as above.

src/utils/dllinit.c

This module declares an external variable called __imp_reent_data, which doesn't get used in the source module! I solved this problem by commenting out lines referencing this data, and I've included this source file in the ZIP file postgresql_patch.zip, which you can download from the Web page for this issue of Enterprise Solutions. As a convenience for you, you can simply unzip this archive into the top-level directory containing your PostgreSQL installation.

A Good Stopping Point...

By this point, I had the following: An executing IPC daemon (required for communication with the database server), and the database server itself (listening for incoming transactions). I'm going ahead and releasing the contents of my PostgreSQL binary directory and the required ipc-daemon executable, since all the programs start and (probably) everything is fine. Where I stopped is in verifying that the binaries work perfectly. The PostgreSQL package comes with excellent built-in testing programs, but I need to do some more work on the client portion of these programs before it can run on my NT box. I'll finish up over the next two months and provide the final version then. For now, you can use the binaries I created by downloading binaries.zip from the Web page for this issue of Enterprise Solutions. Note that this ZIP file contains two directories: one for the IPC ­daemon, and one for the PostgreSQL binaries themselves. Be sure you unzip these binaries into the right directories on your computer; I've put the full directory paths that I used for my own installation!

Please note that before using the binaries, you'll need to read the PostgreSQL setup information provided on the main Web site. Or, you can wait for the next issue and I'll provide all the details for you.

Blast Your Computer

As you might imagine, I had some frustrating moments during this session with PostgreSQL (which required nights, weekends, and more to finish!). I can attribute what little sanity I have left to a handy little program that allows you to blast your computer screen. GUN.EXE (freeware, http://www.galilei.com/libs/win95.htm) allows you to shoot holes in different windows in your screen, with a satisfying POW that appeals to my inner child (or inner vandal--I'm not sure which). Use it to enliven your own day! Better yet, write a small program to run this thing remotely without your co-worker suspecting you! The way I see it, you're having an off day, why not ruin someone else's productivity as well?

Coming up Next Time

Next time, I'll finish up the database work by providing NT binaries of all the required client software, as well as documentation on configuring the database server for your own machine. While that will probably take all the spare time I can throw at it, I'll try to provide a detailed look at the free C++ compiler provided by Cygnus: GNU C++. I used it to build the PostgreSQL package, and the compiler itself provides a cutting-edge (and free) way to develop your own software. If I have time, I'll build a nice little free utility with it to display directory usage in a couple of different types of charts. This will allow you to see at a glance how large particular directories are compared to the disk and other directories at the same level--a good way to find out what needs to be pruned! Plus, it'll give you a chance to see how the free C++ compiler works in actual practice, so that you can make your own decision about whether it's worthwhile to use it in your shop or not. Until then, surf safely!