PHP/PostgreSQL/Apache setup for newbies

The following is a description of my steps for setting up PHP and PostgreSQL under a chroot'ed Apache environment. This how-to is intended for those new to web-based apps or those who have used PHP and MySQL and would like to try out PostgreSQL as an alternative.

Before I get flamed for "the info is already out there- why do we need another how-to?" The overwhelming tutorials focus on MySQL and most that I came across for PostgreSQL either had older data, didn't address the chroot Apache issues or were not really geared for the beginner audience.

#pkg_add -v
ftp://ftp.openbsd.org/pub/3.3/OpenBSD/packages/i386/postgresql-7.3.2.tgz

Or you can grab packages from one of the many mirrors to load balance off the main site :-)

For more details about adding packages:

http://www.openbsd.org/ports.html

3. Post package configuration- most of this is posted on the screen after the package additions are complete but here's a summary.

Create your php.ini file:

#cp /usr/local/share/doc/php4/php.ini-dist /var/www/conf/php.ini

#/usr/local/sbin/phpxs -s

#/usr/local/sbin/phpxs -a pgsql

LoadModule php4_module /usr/lib/apache/modules/libphp4.so

AddType application/x-httpd-php .php .html

# useradd -c "Postgresql Admin User" -g =uid -m -d /var/postgresql -s /bin/sh postgresql

5. Create the database data folder and initial environment:

#su - postgresql

$mkdir /var/postgresql/data

$initdb -D /var/postgresql/data

6. Create the unix socket folder:

$mkdir /var/www/tmp

As far as permissions are concerned, I assigned full permissions to the postgresql account and group, read/list to world and owner deletion only (numerically represented as 1755). Many how-to guides say to set liberal 777 permissions but the above was the most restrictive setting that did not result in errors for me.

7. On the subject of the unix socket folder, open the database configuration file /var/postgresql/data/postgresql.conf and "uncomment" and edit the line:

unix_socket_directory = '/var/www/tmp'

8. Setting Apache and PostgreSQL to start at boot:

I know there are many ways to skin a cat but here's what I did...

Create a rc.conf.local file if you don't have one:

#touch /etc/rc.conf.local

httpd_flags=""

The above will override the httpd_flags=NO in /etc/rc.conf

And the following line to section 3:

shlib_dirs="$shlib_dirs /usr/local/lib/postgresql"

In /etc/rc.local I added the following to start PostgreSQL:

su -l postgresql -c "/usr/local/bin/pg_ctl start
	-D /var/postgresql/data -l /var/postgresql/logfile
	-o '-D /var/postgresql/data'"
	echo -n 'postgresql'

9. To shutdown the database gracefully add the following to /etc/rc.shutdown:

if [ -f /var/postgresql/data/postmaster.pid ]; then
	su -l postgresql -c "/usr/local/bin/pg_ctl stop -m fast -D /var/postgresql/data"
	rm -f /var/postgresql/data/postmaster.pid
fi

10. Changes to the /var/www/conf/php.ini file:

I secured persistent database connections because I have read it can cause reliability issues (maybe someone can comment on their experience)

[Postgresql]
pgsql.allow_persistent = Off

[Sessions]
session.auto_start=1

#top

12. Test that PHP initialized with Apache:

#touch /var/www/htdocs/info.php

<?php
phpinfo();
?>

13. Verify that PostgreSQL create unix socket files properly:

Look in /var/www/tmp and you should see something like this:

.s.PGSQL.5432
.s.PGSQL.5432.lock

For those of you thinking about trying an alternative to PHP/MySQL, the php database commands for PostgreSQL are strikingly similar to those used for MySQL. For the basic database function, don't be turned off due to extensive learning of new commands because there isn't.

It was my intention to help people get setup. Actually writing a PHP/PostgreSQL application is beyond the scope but the following have plenty of information: