howto: PostgreSQL 8.1.4

hehachris

Verified User
Joined
Nov 24, 2004
Messages
598
Location
Hong Kong
HOWTO: PostgreSQL && phpPgAdmin

First of all,

if you want to allow your users to manage their PgSQL DB in DirectAdmin, and set DB quota for them, you should consider this plugin:
DA-PgSQL - PostgreSQL plugin for DirectAdmin


========================================

Using our Auto Build Script

Our Auto Build Script allows you to install/update PostgreSQL and phpPgAdmin without any pain.

If this is your first time to use our build script, remember to run before moving to the next step:
Code:
mkdir /usr/local/directadmin/customapache/daplugin.com
cd /usr/local/directadmin/customapache/daplugin.com
wget [url]http://files.daplugin.com/daplugin.build[/url]
chmod 755 daplugin.build

Make sure your build script and related files are up-to-date:
Code:
./daplugin.build update

To install/update PostgreSQL, run: (Redhat and Fedora ONLY)
Code:
./daplugin.build pgsql
If you are are using neither Redhat nor Fedora, you should install PostgreSQL from source.


To install/update phpPgAdmin, run:
Code:
./daplugin.build phppgadmin
Just make sure you have re-compile PHP with PgSQL support. Instrutions can be found in the last section of the post.

========================================

Manual Install of PostgreSQL 8.1.4

The following guides you on installing PostgreSQL 8.1.4 manually.


1.1, you can install pgsql with yum, however you may not have the latest version installed.
Code:
yum -y install postgresql postgresql-server

Alternatively

1.2 , Download the rpms from http://www.postgresql.org/ftp/binary/v8.1.4/linux/rpms/ according to your OS distro. The following are required

postgresql-libs-8.1.4-3PGDG.i686.rpm
postgresql-8.1.4-3PGDG.i686.rpm
postgresql-server-8.1.4-3PGDG.i686.rpm
postgresql-devel-8.1.4-3PGDG.i686.rpm

(choose rhel-es-4 if your are using CentOS)

Right click on the mirror link and press "T" to copy the url
Code:
wget paste_the_url_here
wget paste_the_url_here
wget paste_the_url_here
wget paste_the_url_here


2, Install the rpms
Code:
rpm -ivh postgresql-libs-8.1.4-3PGDG.i686.rpm
rpm -ivh postgresql-8.1.4-3PGDG.i686.rpm
rpm -ivh postgresql-server-8.1.4-3PGDG.i686.rpm
rpm -ivh postgresql-devel-8.1.4-3PGDG.i686.rpm

3, You have now installed PostgreSQL on your server. The installation is pretty easy. Now we should modify some settings
Code:
nano /var/lib/pgsql/data/pg_hba.conf
if you got these two lines:
Code:
host    all         all         127.0.0.1/32          ident sameuser
host    all         all         ::1/128               ident sameuser
change them to
Code:
host    all         all         127.0.0.1/32          md5
host    all         all         ::1/128               md5
and the following line to the end of the file
Code:
host all all 0.0.0.0 255.255.255.255 reject
so the pg_hba.conf will look like
Code:
local   all         all                               ident sameuser
host    all         all         127.0.0.1/32          md5
host    all         all         ::1/128               md5
host all all 0.0.0.0 255.255.255.255 reject

4, ok, we can start PostgreSQL Server now
Code:
service postgresql start

5, now you need to create a "super user", like a root account for pgsql that has full privileges.
Code:
su postgres
createuser -P -U postgres da_admin
This command will initiate an user creation script.

Code:
Enter password for user ¡§da_admin¡¨:
Enter it again:
Shall the new user be allowed to create databases? (y/n) [b]y[/b]
Shall the new user be allowed to create more new users? (y/n) [b]y[/b]
CREATE USER
The CREATE USER statement indicates that the command was successful.

6, Restart postgresql
Code:
service postgresql restart

========================================

Re-compiling PHP

We need to re-compile php with pgsql support.

Code:
cd /usr/local/directadmin/customapache
nano configure.php
add

Code:
        --with-pgsql=/var/lib/pgsql \
after

Code:
        --with-mysql=/usr \
save and exit

type
Code:
./build php n
at last restart apache
Code:
service httpd restart

now test with the following php code:
Code:
<?php

$password="password";
$con=pg_connect("host=localhost dbname=template1 user=da_admin password=$password");

?>
You shouldn't see any error message if everything goes well.
 
Last edited:
When I type

su postgres

I am asked for a password, which password could it be?
Can I reset it safely?
 
Problems

in phpPGADMIN i get this error:: "Fatal error: Call to undefined function: pg_cmdtuples() in /var/www/html/phpPgAdmin/libraries/adodb/drivers/adodb-postgres64.inc.php on line 168"


And on the site
"Fatal error: Call to undefined function: pg_affected_rows()"
 
When adding postgresql, how does one control the number of pgsql databases that a user can be assigned? Does it have it own user interface, can it be assiged in a plan like mysql can or does one need a another plugin for this?

Strange, in 7 yrs in the business we have never had 1 user that ever made use of a pgsql db. Seems like an option that very few if any users ever need actually.
 
Re: Problems

make sure you have recompiled php to have pgsql support

mobitoc said:
in phpPGADMIN i get this error:: "Fatal error: Call to undefined function: pg_cmdtuples() in /var/www/html/phpPgAdmin/libraries/adodb/drivers/adodb-postgres64.inc.php on line 168"


And on the site
"Fatal error: Call to undefined function: pg_affected_rows()"
 
pgsql is quite a enterprise-level-liked dbms
from my experience, only advanced users will require pgsql

to manage pgsql like mysql in DA, there is a plugin: http://directadmin.com/forum/showthread.php?s=&threadid=14265

pucky said:
When adding postgresql, how does one control the number of pgsql databases that a user can be assigned? Does it have it own user interface, can it be assiged in a plan like mysql can or does one need a another plugin for this?

Strange, in 7 yrs in the business we have never had 1 user that ever made use of a pgsql db. Seems like an option that very few if any users ever need actually.
 
Any suggestion on how to reset the password for the Postgresql superuser?
 
I upgraded to apache 2.059


./build php n

does want to do stuff but on version 1.3.x

please advice.....

Tnx!
 
PostgresQL 8.20 has been just released.... I wonder if it could be easiliy upgraded using Yum.
 
albatroz said:
PostgresQL 8.20 has been just released.... I wonder if it could be easiliy upgraded using Yum.
The issues with this version of PostgreSQL notwithstanding you can almost never upgrade between developer version numbers with yum; yum updates using RPMs and in general RPMs are provided by distribution managers with minor version numbers, fixing only security issues.

Jeff
 
2006? Im affraid to even try this.

Has anyone installed PostgreSQL recently and on a Debian system?

Is this plugin still any good with the latest version of DA?

thanks for any help.

Denny
 
I have PostgreSql 8.4 on Debian Lenny 64bit with daplugin. Still I have plugin ver. 0.6.3 but I will upgrade to newest version daplugin. I have only problem to create database (by this plugin) with encoding other than main cluster.
Only daplugin support sometimes not brilliant like DirectAdmin support and I had to wait few days for answer.
 
Back
Top