Installing PostgreSQL on a local Windows-based, Apache, server

• March 15, 2006
• James Skemp
• software, tutorials / guides

While we've already setup MySQL, another popular SQL server is PostgreSQL. Like MySQL, PostgreSQL is absolutely free, and will allow us to create databases on our local server.

Downloading PostgreSQL

We'll be downloading the most current version of PostgreSQL, which is 8.1.3 at the time of this writing. You can download the installation file from http://www.postgresql.org/ftp/win32. We'll want to download the regular zip file, called postgresql-8.1.3-1.zip, and weighing in at 21 MB. You'll want to save this to the same folder you save your other installers – c:\home\downloads\ if you've been following along.

Once the download has finished, extract the contents to a temporary folder.

Install PostgreSQL

Now that you've downloaded the zip and extracted it, run postgresql-8.1.msi, not postgresql-8.1-int.msi.

You'll be prompted to select a language for the installation. I chose English. Also, you can either check Write detailed installation log to postgresql-8.1.log in the current directory, or not. If you do, a log of the installation will be saved to the temporary folder. Click Next.

Read through the Installation Notes, and press Next. For Installation options, leave everything as default, and select Next.

For Service configuration, Install as a service. Leave Service name the default of PostgreSQL Database Server 8.1, and Account name the default of postgres.

Account domain will default as the name of your computer, and we can leave this as is, but make a note of it. Mine was PAVA620N.

Now, fill in Account password and Verify password, or leave these empty to have the installer create a (very long) password. Make sure you make a note of this password.

When you click through, a pop-up will say that the user was not found. Click Yes to create an account. If your password is weak, it will say so and offer to create a password for you. Again, this will be a very long password, so if you want to change your password, select No and press Back to try a new password.

On the next screen, Initialise database cluster, keep the defaults, but change Encoding to UTF-8. Make a note that the Port number is 5432. Keep the default Superuser name of postgres, but create a unique password – not the one you used previously.

If you've installed Perl, then you'll be able to check a couple of boxes. Pick the PL/pgsql and PL/perl checkboxes. Otherwise, just pick PL/pgsql. Click Next.

For Enable contrib modules, only pick the default of Admin81. Click Next.

At this point, you'll be "Ready to install." Click Next to install, and watch the Installation proceed.

Once the installation has finished, you can choose to subscribe to pgsql-announce. Otherwise, click Finish.

Verify installation of PostgreSQL

First, delete the temporary folder, but keep the log file, if you'd like, and if you opted to create one.

Next, go to Start > All Programs > PostgreSQL 8.1 > pgAdmin III Copy this shortcut to where you store your other shortcuts, and then run this program.

If we check Help > About, we'll see the version of pgAdmin. Mine is Version 1.4.1. If we need to, we can upgrade this program outside of PostgreSQL.

Now, right click on PostgreSQL Database Server 8.1 and select Connect. Enter the second password from above, and connect. Once you've connected, expand the tree as necessary. To disconnect, select Disconnect after right clicking on PostgreSQL Database Server 8.1.

Setting up PHP to connect with PostgreSQL

Open php.ini and do a search for pgsql. Uncomment "extension=php_pgsql.dll", by removing the ;, and then save and close php.ini. Now, restart Apache via Services.

If you go to http://localhost/phpinfo.php and search for pgsql, you should see some information on your installation.

Now, we'll be testing the connection by creating a very small php file. Create a new file called postgresqlconnection.php, with the below text. Make sure you change PASSWORD as necessary.

<?php
$user="postgres";
$password="PASSWORD";
$connection = pg_connect(“host=localhost user=$user password=$password”) or die(“Connection failed.” . pg_last_error());
echo “Connected to PostgreSQL.”;
pg_close($connection);
?>

Save this to your default Web server directory – c:\home\. Now visit http://localhost/postgresqlconnection.php.

Congrats, you should see "Connected to PostgreSQL." and have now setup PostgreSQL to work with PHP.

View all of the steps to creating a local Web server, for development.

• apache
• php
• postgresql