How to install PostgreSQL with Apache 2 and phpPgAdmin on Windows 7

This is an old blog post dated 2014-12-15 from my original Blogger account. System configurations and available software versions changed since then but I think it still works on modern systems, maybe a few modifications have to be made to suit your needs.

Most PostgreSQL database and web developers, who want to make sure their program is completely cross-platform, might have encouthered the problem: PostgreSQL simply does not want to work on Windows 7. Well, it works but installing it properly is not that matter of course what we first thought to be but it is also not that hard to make it work. In this post, we will cover how PostgreSQL with web components can be installed on a Windows 7 Professional system.

Specification

• Operating System: Windows 7 Professional 64 bit
• Database Server: PostgreSQL 9.3.5-3 X64
• Webserver: Apache 2
• Administration tools: pgAdmin III and phpPgAdmin

Problem

PostgreSQL cannot be installed in its default folder because of filesystem privilege restrictions.

Solution

We simply install it to a different directory.

Steps

Simple solution

This is the simplest, fastest but obviously least secure option when multiple users must have access to the database server with any of its components.

1. Before installing anything we create the c:\PostgreSQL directory and make it a shared folder with full (read/write) access to everyone in the system.
2. We download the PostgreSQL Windows Installer (previously known as 'one-click installer'), right-click it and select 'Run as administrator'.
3. We follow the instruction on the screen but when the installer asks where we want to install the database server we choose the c:\PostgreSQL directory instead of the default (Program Files\...) directory.
4. When the installation process is finished the installer will recommend us to launch the Application Stack Builder program so we accept it and let it launch.
5. Before installing anything from the Stack Builder we create the c:\PostgreSQL\plugins directory where we want to install all of the additional components.
6. After selecting the installed database server in the Stack Builder, we get a large list of applications, we open the Web Development rollout and select the Apache/PHP and phpPgAdmin module for installation, then we click the 'Next' button.
7. We follow the instructions in both installers but when they ask where we want to install the new applications we select the c:\PostgreSQL\plugins directory, create subdirectories for the new applications and install them there. (It is recommended to keep these downloaded installers in a safe directory for future reinstallations.)
8. After closing the installers we are going to test the client systems. First, we open pgAdmin III, select the only database connection on the list, and enter our password, which was specified during the PostgreSQL installation process. If we can connect, then PostgreSQL installation was successful, we disconnect and move on the phpPgAdmin program. We open a web browser and enter the following URL to open phpPgAdmin:
   http://localhost:8080/phppgadmin/
   Then, we enter postgres as username and the same password what we used in pgAdmin III. If we can connect to the database server from here too, then everything went fine, we can sign out now.

Now let's make our installation more secure
This is an optimal but strongly recommended procedure because installing a database server to a shared folder is simply unacceptable in a production environment for security reasons!

1. Open Windows Control Panel, search for the system services menu and open it.
2. In the system services, look for postgres or postgresql service (with any suffix) and stop it.
3. Go to the c:\PostgreSQL directory and remove the sharing from this directory.
4. Now go to the 'Security' tab and click on the 'Advanced' button. We are going to change the owner of this directory and all of its subdirectories. Click on the 'Owner' tab then click on the 'Edit' button. Another window will appear, where we click on the other users and groups button. Type 'SYSTEM' in the text field and click 'OK', then click 'OK' again in the previous window after checking the option to change ownership on all subelements. Now the directory looks like it was created by a normal installation. But that's not enough, because postgres service still does not have write access on it.
5. Go to the permissions tab and like at the ownership windows we give full access to other special users on this directory with all of its subdirectories. These special users are NETWORK SERVICE, SERVICE and system administrators. All of them must have read/write access on this directory with all of its subdirectories, otherwise PostgreSQL will not work!
6. Once all special users (services) have full access on the c:\PostgreSQL directory, go back to the system services and start the previously stopped postgres service. Now PostgreSQL must work without errors and you can start using your new database server.
7. It is recommended to restart Windows and see if it works after reboot.

Notes

• I don't like installing database servers on Windows. Database servers usually work on UNIX/Linux systems because that is a lot more secure. I strongly recommend you to use this installation only for development not in a production environment, especially not on a Windows desktop computer! Use this system on your own risk!
• Just like last time, I wrote this post right after successfully solving the given problem not during the process. I might have forgotten to mention some steps. I assumed that you have at least basic knowledge about Windows system administration including filesystem permissions. If I forgot to mention anything important in this post, please let me know and I will update it.

I hope that I could help you in getting PostgreSQL work on Windows 7. Actually, it was something new for me too because this was the first time I was trying to get this thing done, just for cross-compatibility of my future developments. I'm used to do the database administration thing on Linux.

UPDATE

PostgreSQL v9.3.5 covered by this post seems to work with normal installation process too (default Program Files folder, database server starts, pgAdmin III can connect, reboot not tested).