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?
- we (the assigner) didn’t want to use Microsoft products any longer (due to the associated licensing cost)
- 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:
- 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…) - Choose Installation Type
Select Install from Internet. - 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. - 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. - 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! - 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. - 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. - 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
How can I install PostgreSQL on Windows for use with PHP?
Installing PostgreSQL on Windows for use with PHP involves several steps. First, download the PostgreSQL installer for Windows from the official PostgreSQL website. Run the installer and follow the prompts to install PostgreSQL on your system. After installation, you’ll need to set up the database and create a user with the necessary permissions. Once this is done, you can connect to the database using PHP’s pg_connect() function.
What are the prerequisites for using PostgreSQL with PHP on Windows?
Before you can use PostgreSQL with PHP on Windows, you need to have PHP and Apache installed on your system. You also need to install the PostgreSQL database and the PHP PostgreSQL extension. The PHP PostgreSQL extension is necessary to enable PHP to interact with the PostgreSQL database.
How can I enable the PHP PostgreSQL extension on Windows?
To enable the PHP PostgreSQL extension on Windows, you need to edit your php.ini file. Locate the line that says “;extension=pgsql” and remove the semicolon at the beginning of the line. This will enable the extension. After making this change, save the php.ini file and restart your Apache server for the changes to take effect.
How can I connect to a PostgreSQL database using PHP?
To connect to a PostgreSQL database using PHP, you can use the pg_connect() function. This function takes a connection string as its argument, which specifies the details of the database connection. The connection string should include the hostname, database name, username, and password.
How can I handle errors when using PostgreSQL with PHP?
When using PostgreSQL with PHP, you can handle errors using the pg_last_error() function. This function returns the last error message from the PostgreSQL server. You can use this function to display an error message to the user or to log the error for debugging purposes.
How can I execute SQL queries using PHP and PostgreSQL?
To execute SQL queries using PHP and PostgreSQL, you can use the pg_query() function. This function takes two arguments: the database connection resource and the SQL query string. The function returns a result resource on success, or FALSE on failure.
How can I fetch data from a PostgreSQL database using PHP?
To fetch data from a PostgreSQL database using PHP, you can use the pg_fetch_assoc() function. This function takes a result resource as its argument and returns an associative array that corresponds to the fetched row.
How can I close a PostgreSQL database connection in PHP?
To close a PostgreSQL database connection in PHP, you can use the pg_close() function. This function takes the database connection resource as its argument and returns TRUE on success, or FALSE on failure.
How can I secure my PostgreSQL database when using it with PHP?
To secure your PostgreSQL database when using it with PHP, you should always use prepared statements or parameterized queries to prevent SQL injection attacks. You should also regularly update your PostgreSQL and PHP installations to the latest versions to benefit from the latest security patches.
How can I optimize the performance of my PHP application when using PostgreSQL?
To optimize the performance of your PHP application when using PostgreSQL, you can use persistent connections, which can reduce the overhead of establishing a new connection for each request. You can also use EXPLAIN to analyze your SQL queries and identify potential performance issues.
Johan is an IT consultant and teacher with interests in logic, mathematics, and database design. He's constantly forced to learn more about computers and programming by his inquisitive nephew.