Adding PostgreSQL portable to XAMPP (on Windows)

For those who don’t know it, XAMPP is a package containing the main things you need to setup and run a local webserver within a few minutes (configured as a testing/dev platform, not a production platform). I used to be reluctant to using it, but I eventually found out that the Lite version used without the installer was really useful, notably on PCs where I don’t have admin rights. XAMPP has a few limitations, though. Notably, there’s no PostgreSQL (even in the “not-Lite” version). So, here’s how to add PostgreSQL to XAMPP Lite (or not Lite).

PostgreSQL

First, get PostgreSQL portable here (topic on portable freeware).
Edit: unfortunately that link seems to be down and I didn’t keep a copy of the installer… :/ If you find one, please do post the link in a comment. However, it should be possible to make the non-portable version of PostgreSQL work with XAMPP just the same way as for the portable version (see below).
Update: PostgreSQL provides a binary download which can be used as a portable version. You can grab it here. I haven’t tried it yet, but I guess my instructions don’t apply to this version… I’ll update them some day…
Unzip it anywhere you want, then launch PostgreSqlPortable.exe. At first you’ll need to initialize the database: click on “Initialize PostgreSQL 8.3”, choose a user name (you can leave the default on, I chose “root”), a password and the port (leaving the default 5432 is good, it will let you configure phpPgAdmin faster). The database initialization will then take a few seconds (over 15 seconds on my very slow PC…). Then click on “Start PostgreSQL 8.3”. On a restricted computer, you may get an error message “Error starting PostgreSQL”. This seems to be an issue on the launcher: PostgreSQL is probably still running successfully (check in the task manager if you see a few running postgres.exe).

Now, all you need is to configure PHP to be PostgreSQL-enabled. In XAMPP’s folder, find php/php.ini and uncomment (or create) the lines extension=php_pgsql.dll and extension=php_pdo_pgsql.dll. Restart Apache and voilà, PostgreSQL is now usable from your XAMPP installation.

phpPgAdmin

phpPgAdmin is to PostgreSQL what phpMyAdmin is to MySQL: every newbie needs it, most power-users enjoy it. It can be downloaded from phppgadmin.sourceforge.net. Extract it in XAMPP’s htdocs folder (e.g. in htdocs/phppgadmin). Copy the default configuration file (conf/config.inc.php-dist) and rename it to conf/config.inc.php. Then in this file edit the following lines:

• $conf['servers'][0]['host'] = 'localhost';
• $conf['servers'][0]['defaultdb'] = 'postgres';: not sure if that’s really needed but I configured it this way…
• $conf['extra_login_security'] = false;: so you can log in as root on the database. Not needed if you chose a custom username as the admin username (during the database initialization process). But I chose root, so leaving it to true gave me the infamous “Login disallowed for security reasons.” error.

Finally, phpPgAdmin triggers a deprecation warning in PHP 5.3. And this deprecation warning is quite misplaced, as you can see from the error it generates:

Warning: session_start() [function.session-start]: Cannot send session cookie – headers already sent by (output started at xampplite\htdocs\phppgadmin\classes\Misc.php:344) in xampplite\htdocs\phppgadmin\libraries\lib.inc.php on line 56

To fix this, you can either:
– fix Misc.php: replace, on line 344, $data =& new $_type($_connection->conn); with unset($data);$data = new $_type($_connection->conn);
– or turn off deprecation notices (NB: not tested): in php.ini, replace error_reporting = E_ALL & ~E_NOTICE & ~E_DEPRECATED with error_reporting = E_ALL & ~E_NOTICE or even error_reporting = 0.

That’s it, you can now access phpPgAdmin at http://127.0.0.1/phppgadmin/ (that is, if you chose the same path as me).

Sources and versions

• Installing phpPgAdmin
• PHP documentation – Runtime configuration
• PHP.net – References Explained – comment by Anonymous on 01-Sep-2009 08:07 about fixing the “Assigning the return value of new by reference is deprecated” notice in PHP 5.3

For this short guide I used:

• PostgreSQL Portable v1.2.0 (contains PostgreSQL v8.3.7-1), 23 Aug 2009
• XAMPP Lite 1.7.3 with PHP patched to 5.3.2 (see upcoming tutorial about this)
• phpPgAdmin 4.2.3, 29 March 2010