How to Integrate postgreSQL Database to XAMPP in Windows

Over the weekend, I decided to learn postgreSQL Database. For me to be able to achieve it, I need to as a matter of fact install and get it running on my local machine.
Since I’m using XAMPP already as my LAMP stack, it will be easier to just integrate postgreSQL into it rather than compiling it with PHP from scratch.

In this article, I will show us how I was able to install and integrate postgreSQL to XAMPP as well as pgAdmin and phpPgadmin in Windows.

Installing postgreSQL

1. Download the postgreSQL installer from EnterpriseDB.
2. Run the installer and follow the on-screen instruction.
   Note: pgAdmin also get installed alongside.
3. Assuming XAMPP is located in C:\xampp; using the pgSQL installer, install postgreSQL in say C:\xampp\pgsql\9.1 folder.
4. You will be prompted to set a password for postgres root user.
5. By now, pgSQL has been installed.

Getting postgreSQL to talk with PHP

We need to perform the following rituals to get PHP talking / communicating with pgSQL.

1. Open php.ini file located in C:\xampp\php.
2. Uncomment the following lines in php.ini
   extension=php_pdo_pgsql.dll
   extension=php_pgsql.dll
3. Add the below code snippet to httpd.conf

   LoadFile "C:\xampp\php\libpq.dll"

4. Done.

postgreSQL Database Administration tool

When we were installing postgreSQL, pgAdmin – a graphical pgSQL database administration tool was installed alongside.
There is also phpPgAdmin (web base Postgres database management tool) which is to postgreSQL what phpMyAdmin is to MySQL / MariaDB.

If you are like me that is intimidated by the command line, you will find these tools useful.

pgAdmin Quick-start

To get started with pgAdmin in creating and managing postgres database; firstly create a server, connect to it and create the database for that project of yours.

Let’s start with creating the server.

1. Click on File > Add Server…
2. Fill the form as shown in the image below.
   

   Take note: in the username and password field, insert postgres as the username and in the password field, the password you entered while installing postgreSQL.

You can now go ahead to create a Database.

To create the database, under the server we created, right-click on the Database menu and click the New Database… link.

phpPgAdmin installation

I will be integrating phpPgAdmin to XAMPP to just have a MySQL-like XAMPP experience.

1. Head over to the Github repository and clone the repo to C:\xampp\phppgadmin.
   Alternatively, download the repo. as a Zip, and extract the content to C:\xampp\phppgadmin.
2. In C:\xampp\phppgadmin\conf, rename the config.inc.php-dist file to config.inc.php
3. Edit the config.inc.php and replace all instances of the following with the values below.

   
   $conf['servers'][0]['host'] = 'localhost';
   $conf['servers'][0]['pg_dump_path'] = 'C:\\xampp\\pgsql\\9.1\\pg_dump.exe';
   $conf['servers'][0]['pg_dumpall_path'] = 'C:\\xampp\\pgsql\\9.1\\pg_dumpall.exe';
   $conf['extra_login_security'] = false;
   

4. Edit XAMPP’s httpd-xampp.conf and add the below code.

   
   Alias /phppgadmin "C:/xampp/phppgadmin/"
   <directory "C:/xampp/phppgadmin">
   AllowOverride AuthConfig
   Require all granted
   </directory>
   

5. Restart Apache
6. You should now be able to use phpPgAdmin when you visit http://localhost/phppgadmin.

Putting pgSQL and PHP to test

I will be creating a test that check a PHP connection to postgres using pg_connect().
If successful, PostgreSQL connection resource is returned or FALSE on failure.

&lt?php
$link = pg_connect("host=localhost port=5432 dbname=postgres user=postgres password=mypassword");
print_r($link);

I hope you found this guide useful.