Use PostgreSQL and PHP on Windows

I guess every SitePoint visitor has seen the words “Database Driven Website Using PHP & MySQL” a million times. Something you don’t see every day is the combination “PHP & PostgreSQL” — especially not with the post-script, “…on Windows”.

Some people think it is incredibly difficult to run PostgreSQL on Windows, because there’s no easy-to-use Win32 installer (at the time of writing, 7.3.4. is the latest version). Users have to install some sort of unix emulator first, and run the database system on this “platform”.

The PostgreSQL Technical Documentation Site says, “The main PostgreSQL project plan is to add support for a native Windows version with our 7.4 release. This will probably be released in the middle of 2003…” I haven’t seen this release yet… maybe late 2003? However, what this means is that, in the future PostgreSQL will be an even more appealing choice than MySQL!

Build a database driven Website with PostgreSQL? Why would you do such a thing? Isn’t the rule that if you’re using PHP, your database is MySQL — period!?

Well, in my case, it was until I was assigned the task to convert an ASP/MSSQL solution to PHP/PostgreSQL. The reasons for the change?

1. we (the assigner) didn’t want to use Microsoft products any longer (due to the associated licensing cost)
2. we wanted database features that weren’t present in MySQL (foreign keys etc)

To me, it also seemed to be a good idea to have both the old ASP stuff and the new PHP install on the same (Windows) development machine when we performed this “translation”. Another good reason to try the Windows, PostgreSQL, and PHP combination is that it’s always fun to find the answer to that age-old question, “How difficult can it be?”

I must admit up-front that I’m a Microsoft-indoctrinated sort of developer. Primarily I use MSSQL, less often MySQL, and almost never PostgreSQL. It was the same with operating systems; 90% of the time I used Windows, 10% Linux. Despite those facts, my move to PostgreSQL ended happily. And if it was doable for me, then it is for you, too.

For more reading on alternatives to MySQL, see Wayne Luke’s article Moving Beyond MySQL – High End Database Solutions.

Note that this tutorial assumes you have read chapter one in Kevin Yank’s excellent Build Your Own Database Driven Website Using PHP and MySQL, or some other tutorial that explains how to install Apache and PHP on Windows. More importantly, I assume that after learning this process, you have successfully installed a Web server with PHP “plug-in”.

Let’s begin!

Cygwin Installation

The first task is to download and install Cygwin, which is a linux-like environment for Windows. At the time of writing, the latest version of Cygwin is 1.5.5. It’s not a bad idea to rename setup.exe to cygwin_setup.exe — this way, the file will be easier to locate when you want to add/remove/update Cygwin components later.
Naturally, you should be logged in as a user belonging to the group Administrators when you launch the setup program.

The Cygwin Net Release Setup Program will ask you the following eight questions:

1. Disable Virus Scanner?
   As some antivirus programs may cause problems during the Cygwin installation, you have the option to disable the virus scanner temporarily. In most cases, the default option Leave Virus Scanner Alone works. (If no antivirus program is detected, then — sooner or later you’ll encounter another type of problem…)
2. Choose Installation Type
   Select Install from Internet.
3. Choose Installation Directory
   You can use the default options here too, unless you have some really good reason not to designate C:\cygwin as Root Directory, Install For All Users and select Unix as Default Text File Type.
4. Select Local Package Directory
   The folder you specify here is the location where the downloaded installation files will be saved. Enter the path of your choice, e.g. E:\cygwinfiles.
5. Select Connection Type
   Here you select the appropriate connection type; the default option is Direct Connection. Most likely, your antivirus/firewall program will pop up after you click Next, to check if you want to allow cygwin_setup.exe to access the Internet. Yes, that’s exactly what we want!
6. Choose Download Site
   Select any site from the list, preferably one that’s close to you. Depending on your connection speed, it will take a moment or two before the next screen appears.
7. Select Packages
   You only need to make two additions to the by-default selected packages; cygrunsrv (category Admin) and postgresql (category Database). Cygrunsrv is an NT/W2K service initiator, and PostgreSQL is… well, you know what it is. At the time of writing, the version of the Database Management System is 7.3.4. If you’re a control freak, verify that cygipc (category Devel) is among the pre-selected packages. Cygipc provides IPC (InterProcess Communication) support for Cygwin, but will eventually be replaced by the “cygwin-daemon”. At the time of writing, PostgreSQL depends on cygipc. Now you can take a break and drink some coffee while the files are downloaded and installed.
8. Create Icons
   Well, this last screen is quite self-explanatory.

Click Finish, and smile happily when you see…

Installation complete!

Installing Services

Installation complete? No, not yet. The files are there, somewhere in C:\cygwin or wherever you chose to put them. Now you must install services, create a postgres user and initialize PostgreSQL.

These instructions are based on the postgresql-7.3.4.README file, written by Jason Tishler, which is located in /usr/doc/Cygwin. In that file, you can find instructions for the Basic Cygwin PostgreSQL Installation, which is your only option if you’re running Windows 95/98/ME (but if you ask me, the only option for someone running Windows 9x is: upgrade!).

Start your newly-installed Cygwin Bash Shell and complete these steps:

Install ipc-daemon2 as an NT service

As you remember from the Cygwin installation, Cygipc is the interprocess communication daemon that PostgreSQL depends on.

ipc-daemon2 --install-as-service

If you are a curious person (like me), launch the Services MMC Console Snap-in in Windows (services.msc) and verify that you have a new service called Cygwin IPC Daemon 2 in the list.

Create a new user account

A quote from the PostgreSQL documentation states: “It is advisable to run PostgreSQL under a separate user account”.

So, let’s add a new user, and due to lack of imagination, call the new user “postgres”. Note that you should replace ******** below with the password you want to use for postgres.

You can issue one single net user command with all the options (fullname, comment, homedir) on one line, but I’ve written them as separate commands here to make the text more readable. To see the net user syntax, type “net user /?”.

net user postgres ******** /add   

net user postgres /fullname:postgres   

net user postgres /comment:'PostgreSQL User Account'   

net user postgres /homedir:"$(cygpath -w /home/postgres)"

If you feel like it, check in the Local Users and Groups MMC Snap-in (compmgmt.msc) that postgres has joined the team. There should also be a newly created folder called C:\cygwin\home\postgres.

Update the file /etc/passwd

mkpasswd -l -u postgres >> /etc/passwd

The command above, translated into English, means: append information for the specified user postgres to /etc/passwd. Please note that there should be two greater-than characters!

Grant postgres the right to log on as a service

Why? This is rather obvious; postgres is the user that should run the postmaster service later. Launch the Local Security Settings MMC Snap-in (secpol.msc) and add postgres to the list of users that can log on as a service, under Local Policies User Rights Assignment.

Create a data directory

This is where PostgreSQL will store the data, of course.

mkdir /usr/share/postgresql/data

Make postgres the owner of the data area

Another natural thing; none other than postgres should own the data directory, of course.

chown postgres /usr/share/postgresql/data

Later, the initialization process will revoke permissions for everyone except postgres.

Install postmaster as an NT service

Postmaster is the name of the PostgreSQL server, located in /usr/bin. Postmaster will also be the name of the service you create (who said “lack of imagination”?!).

cygrunsrv -I postmaster -p /usr/bin/postmaster   

    -a "-D /usr/share/postgresql/data -i"   

    -y ipc-daemon2 -s INT -u postgres -o

Note that the command has been divided into three lines here to make it more readable, but you should write everything on one line.

Whoa! What do all these options mean? Translation: Install (-I) a new service called postmaster, with application path (-p) /usr/bin/postmaster, arguments (-a) “-D /usr/share/postgresql/data -i”, which in turn means that postmaster should use /usr/share/postgresql/data as data directory (-D) and TCP/IP connections (-i) should be enabled.

Further on, the postmaster service depends on (-y) another service called ipc-daemon2, sends the signal INT when terminated (-s), is run by the user (-u) postgres and stops during system shutdown (-o).

Cygrunsrv will ask you (twice) for the password interactively.

To see the cygrunsrv syntax, type “cygrunsrv -h”. If you want to learn more about options for the PostgreSQL server, type “postmaster –help”.

Now, if you look in the Services MMC Console Snap-in (services.msc), you will see yet another service in the list. Double-click on postmaster to review the different properties you just assigned (Log On, Dependencies etc.).

Create a Database Cluster

Before you can start postmaster (the service), the database storage must be initialized. You must run the command initdb logged in as the postgres user.

Note: log in as the postgres user! To avoid all possible problems, log off from Windows, and log in again, but this time as postgres (i.e. don’t use the su command in cygwin to switch user).

Start the Cygwin Bash Shell and enter:

initdb -D /usr/share/postgresql/data

The switch -D is used to indicate where the data area is located (quite easy to guess, wasn’t it?). Now you can log out, and log in as yourself again.

Test to see whether PostgreSQL is working

Finally, you should now be able to connect to PostgreSQL. Launch Cygwin, connect as the user postgres and use the database template1, created by initdb.

psql -U postgres template1

The database template1 is, as you understand, the default template that’s copied when you create a new database using the createdb command or the CREATE DATABASE statement.

If, for some reason, you receive a message like “psql: could not connect to server”, start the postmaster service with this command:

net start postmaster

To learn more about how to use psql, the PostgreSQL interactive terminal, type “psql –help”. I think we can define a general rule here: any command plus “– help” will show you some helpful text!

Connect to PostgreSQL from PHP

Open your php.ini file (most likely located in C:\winnt), find the section called Dynamic Extensions and un-comment the line that contains “extension=php_pgsql.dll”. Save and close php.ini and restart Apache. If the Web server complains and says it’s unable to load the dynamic library, you probably have entered the wrong path for extension_dir in php.ini (section Paths and Directories). Another possible — though unlikely — cause is that the file php_pgsql.dll isn’t present in the extensions directory.

Now you should be able to run the following little PHP script:

<?   

$conn = pg_connect("host=localhost    

    port=5432    

    dbname=template1    

    user=postgres    

    password=********");   

$sql = "SELECT current_date AS today;";   

$result = pg_query($conn, $sql);   

$row = pg_fetch_object($result, 0);   

echo "Today is: " .$row->today;   

?>

The script isn’t too exciting, but at least it shows you how to connect to the database and execute a query.

Note that the code above isn’t supposed to be an example of best practices in PHP. Of course you should add error handling, use pg_close() to close the connection explicitly when you are done etc. The database template1 should be used as a template (what else?) for the new database(s) you create for your application(s), and you should create new users and give them appropriate privileges (i.e. don’t connect as postgres).

For more information on how to create databases, users, tables etc., see the PostgreSQL documentation.

For more reading on how to use PHP’s PostgreSQL functions, see the articles Migrate your site from MySQL to PostgreSQL Part 1 and Part 2, and of course the PHP documentation.

Useful Tools

Command line SQL can be fun if you are a real geek, but to manage the database(s), I used Aqua Data Studio v 3.0 from AquaFold, Inc. Well, it isn’t MSSQL Enterprise Manager, or even MySQL Control Center, but it gives you a better view of the objects than you’d get through the psql interactive terminal. One nice feature of Aqua Data Studio is that you can connect to, and view in the same tree structure, other types of databases; MySQL, MSSQL, Oracle, and more.

If you just want to manage your PostgreSQL database, pgAdmin is the tool for you. I guess “tens of thousands of developers worldwide” can’t be totally wrong. Remember, this isn’t a software review, hence the few words. Another option is to use MS Access (after all, you’re running Windows, so there’s a high probability you also have MS Office) as a front-end for PostgreSQL, but then you have to install the psqlODBC driver first.

Since you have Apache and PHP, you could use phpPgAdmin, a browser based tool that’s not completely dissimilar to phpMyAdmin for MySQL.

Epilogue

Hopefully, you found this article interesting, you couldn’t wait to try the installation procedure yourself, and now you are busy absorbing knowledge about PostgreSQL (see links above). If you are a MySQL user, I’m sure you will “discover” useful functionality missing in MySQL. If you are a MSSQL user, you will appreciate that PostgreSQL is available free of charge, yet full of powerful features.

Do note that there’s no uninstaller included in the Cygwin setup. If you want to remove the things you’ve added after reading this tutorial, you’ll have to manually delete…

• HKEY_CURRENT_USER\Software\Cygnus Solutions
• HKEY_LOCAL_MACHINE\SOFTWARE\Cygnus Solutions
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ipc-daemon2
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\postmaster
• the postgres user account
• the Cygwin installation directory (don’t forget; you have to become owner of the data directory before you can delete it)
• the local packages directory

Good luck!

Frequently Asked Questions (FAQs) on Using PostgreSQL with PHP on Windows