jimcooncat
July 27th, 2005, 10:36 AM
This howto is currently in the draft stage; possibly incomplete as I need to test it again. But if you're looking to install Drupal with a Postgresql backend, these tips will help. I found most of the info on a now-defunct web page, and thanks to Adrian who wrote it. Link to Google's cache of his page: http://shortify.com/1217 (http://64.233.161.104/search?q=cache:gVQO2s1Cz6cJ:www.smop.co.uk/node/2+drupal+postgres&hl=en)
Install in order with apt-get, aptitude, or whatever you like:
apache or apache2
postgresql
php4-pgsql
drupal
Drupal will accept Postgresql as a dependency, but the current package (4.5.2-3) doesn't create the required Postgres user, database, or language. It looks as if running 'sudo dpkg-reconfigure drupal' after installation would be the way to go, but it didn't work for me; apparently the permissions weren't set up correctly.
Su to the postgres user.
sudo su - postgres
Create the drupal user and supply a password for it when prompted.
createuser --no-adduser --no-createdb --pwprompt --encrypted drupal
# or
createuser -A -D -P -E drupal
Create the drupal database.
createdb --owner=drupal drupal
Edit Postgresql's authentication file to allow drupal to use the database. This was the part that tripped me up. Lines in this file are processed in order, so the drupal lines must be placed before any lines of the same TYPE which the user is "all". You can just add the lines at the top of the file to avoid any hassles.
# log back out, and edit the file
exit
sudo gedit /etc/postgresql/pg_hba.conf
# add this line before any "local * all" (using sockets)
local drupal drupal md5
# add this line before any "host * all" (using tcp)
host drupal drupal 127.0.0.1 255.255.255.255 md5
# then save and exit the editor.
Restart the database server. Be careful to check out if this will mess up other database users!?! If you didn't have a Postgresql install before now, then no worries.
sudo /etc/init.d/postgresql restart
Log back in as postgres.
sudo su - postgres
Check that the drupal user can connect. If you get errors on this command, you'll have to recheck your setup.
# runs psql with user drupal, prompts for password, opens database drupal
psql -U drupal -W drupal
# Watch for any errors here
# Quit psql
\q
Start psql using the database; the user will be 'postgres', as that's the current linux user, and it's the superuser of the database. We then run two SQL commands to teach postgresql to use the PL/pgSQL language.
psql drupal
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
'$libdir/plpgsql' LANGUAGE C;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
HANDLER plpgsql_call_handler;
# Quit psql
\q
Now we load the database with the default tables and stuff. You'll see two errors at the end; these are actually the two language commands we just ran as the postgres user. That's the main reason for this howto, because when the Debian devs went to fix a bug, they just slapped the two commands on the end of this file, without adjusting the install to run them as the database superuser.
psql -U drupal -W drupal </usr/share/drupal/database/database.pgsql
You should be set now to run Drupal!
# Get back to your own user prompt.
exit
# Run drupal. Substitute your hostname and browser appropriately.
firefox http://localhost/drupal &
On Adrian's page, he ran into a couple more things which I didn't.
Trying to browse http://hostname/drupal/ failed - no configuration defined. So I copied /usr/share/doc/drupal/examples/conf.php to /etc/drupal and edited it - setting $db_url and $base_url appropriately (don't forget the "/drupal" on $base_url).
Now I got a complaint that pg_connect was not defined, so I installed php4-pgsql and restarted Apache
Install in order with apt-get, aptitude, or whatever you like:
apache or apache2
postgresql
php4-pgsql
drupal
Drupal will accept Postgresql as a dependency, but the current package (4.5.2-3) doesn't create the required Postgres user, database, or language. It looks as if running 'sudo dpkg-reconfigure drupal' after installation would be the way to go, but it didn't work for me; apparently the permissions weren't set up correctly.
Su to the postgres user.
sudo su - postgres
Create the drupal user and supply a password for it when prompted.
createuser --no-adduser --no-createdb --pwprompt --encrypted drupal
# or
createuser -A -D -P -E drupal
Create the drupal database.
createdb --owner=drupal drupal
Edit Postgresql's authentication file to allow drupal to use the database. This was the part that tripped me up. Lines in this file are processed in order, so the drupal lines must be placed before any lines of the same TYPE which the user is "all". You can just add the lines at the top of the file to avoid any hassles.
# log back out, and edit the file
exit
sudo gedit /etc/postgresql/pg_hba.conf
# add this line before any "local * all" (using sockets)
local drupal drupal md5
# add this line before any "host * all" (using tcp)
host drupal drupal 127.0.0.1 255.255.255.255 md5
# then save and exit the editor.
Restart the database server. Be careful to check out if this will mess up other database users!?! If you didn't have a Postgresql install before now, then no worries.
sudo /etc/init.d/postgresql restart
Log back in as postgres.
sudo su - postgres
Check that the drupal user can connect. If you get errors on this command, you'll have to recheck your setup.
# runs psql with user drupal, prompts for password, opens database drupal
psql -U drupal -W drupal
# Watch for any errors here
# Quit psql
\q
Start psql using the database; the user will be 'postgres', as that's the current linux user, and it's the superuser of the database. We then run two SQL commands to teach postgresql to use the PL/pgSQL language.
psql drupal
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
'$libdir/plpgsql' LANGUAGE C;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
HANDLER plpgsql_call_handler;
# Quit psql
\q
Now we load the database with the default tables and stuff. You'll see two errors at the end; these are actually the two language commands we just ran as the postgres user. That's the main reason for this howto, because when the Debian devs went to fix a bug, they just slapped the two commands on the end of this file, without adjusting the install to run them as the database superuser.
psql -U drupal -W drupal </usr/share/drupal/database/database.pgsql
You should be set now to run Drupal!
# Get back to your own user prompt.
exit
# Run drupal. Substitute your hostname and browser appropriately.
firefox http://localhost/drupal &
On Adrian's page, he ran into a couple more things which I didn't.
Trying to browse http://hostname/drupal/ failed - no configuration defined. So I copied /usr/share/doc/drupal/examples/conf.php to /etc/drupal and edited it - setting $db_url and $base_url appropriately (don't forget the "/drupal" on $base_url).
Now I got a complaint that pg_connect was not defined, so I installed php4-pgsql and restarted Apache