HowTo : PostgreSQL 9.0.0

=NickJ=

New member
Joined
Aug 27, 2010
Messages
4
Hi all,

With the release of PostgreSQL 9.0.0 I thought I would write a new HowTo to brush the cobwebs off. Please note that most of this information has come from or been adapted from http://www.directadmin.com/forum/showthread.php?threadid=14266, this is merely a refresher.

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

Installing and configuring PostgreSQL

Step 1.
Download the rpms from http://www.postgresql.org/ftp/binary/v9.0.0/linux/rpms/redhat/ according to your distro and arch. The files needed are:

postgresql90-9.0.0-1PGDG
postgresql90-devel-9.0.0-1PGDG
postgresql90-libs-9.0.0-1PGDG
postgresql90-server-9.0.0-1PGDG
Optional: postgresql90-docs-9.0.0-1PGDG

Here is a sample for those with RHEL5 / CentOS 5, x86_64:

Code:
wget http://wwwmaster.postgresql.org/redir/198/h/binary/v9.0.0/linux/rpms/redhat/rhel-5-x86_64/postgresql90-9.0.0-1PGDG.rhel5.x86_64.rpm
wget http://wwwmaster.postgresql.org/redir/198/h/binary/v9.0.0/linux/rpms/redhat/rhel-5-x86_64/postgresql90-devel-9.0.0-1PGDG.rhel5.x86_64.rpm
wget http://wwwmaster.postgresql.org/redir/198/h/binary/v9.0.0/linux/rpms/redhat/rhel-5-x86_64/postgresql90-libs-9.0.0-1PGDG.rhel5.x86_64.rpm
wget http://wwwmaster.postgresql.org/redir/198/h/binary/v9.0.0/linux/rpms/redhat/rhel-5-x86_64/postgresql90-server-9.0.0-1PGDG.rhel5.x86_64.rpm

wget http://wwwmaster.postgresql.org/redir/198/h/binary/v9.0.0/linux/rpms/redhat/rhel-5-x86_64/postgresql90-docs-9.0.0-1PGDG.rhel5.x86_64.rpm

Step 2.
Install the rpms:
Code:
rpm -ivh postgresql90-*

Step 3.
Initialize the data store:
Code:
/sbin/service postgresql-9.0 initdb

Step 3b.
Optionally, check to see if PG starts:
Code:
/sbin/service postgresql-9.0 start
/sbin/service postgresql-9.0 stop

Step 4.
Modify pg_hba.conf in its new location:
Code:
nano /var/lib/pgsql/9.0/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
Also add the following line to the end of the file:
Code:
host all all 0.0.0.0 255.255.255.255 reject
So pg_hba.conf now looks like this:
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

Step 5.
Start PostgreSQL:
Code:
/sbin/service postgresql-9.0 start

Step 6.
Now we create a "super user", like a root account for pgsql that has full privileges:
Code:
su postgres
createuser -P -U postgres da_admin
This will invoke the user creation script:
Code:
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) [B]y[/B]
If there is no error displayed, then the process has been successful.

Step 7.
Restart PostgreSQL:
Code:
/sbin/service postgresql-9.0 restart

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

Re-compiling PHP

We need to re-compile PHP to enable PG support.

Step 1.
Edit the PHP config file:
Code:
cd /usr/local/directadmin/custombuild
nano configure/ap2/configure.php5

Step 2.
Add this into the list, I add it after --with-pear to keep it alphabetical:
Code:
        --with-pgsql=/usr/pgsql-9.0 \
Save and Exit.

Step 3.
Re-build PHP:
Code:
./build php n

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

Install phpPgAdmin

Step 1.
Create a daplugin.com directory if you don't already have one:
Code:
mkdir /usr/local/directadmin/daplugin.com
cd /usr/local/directadmin/daplugin.com

Step 2.
Download the daplugin script and make it executable:
Code:
wget http://files.daplugin.com/daplugin.build
chmod 755 daplugin.build

Step 3.
Make sure it is up-to-date:
Code:
./daplugin.build update

Step 4.
Run phpPgAdmin utility:
Code:
./daplugin.build phppgadmin
Which will invoke this:
Code:
***************************************************
*   phpPgAdmin upgrading and installing utility   *
***************************************************

Use which mirror to pull 'phpPgAdmin-{$version}.tar.gz' from?

Step 5.
Press enter to select the offered mirror. The script will then offer where to install/upgrade from. The default is to install it in the same directory as phpMyAdmin, though you are obviously free to change it:
Code:
Where should we install phpPgAdmin locally? [/var/www/html/phpPgAdmin]
Press enter again to stick with the default and be offered:
Code:
Can not locate a copy of phpPgAdmin in /var/www/html/phpPgAdmin to upgrade..
If you would like to install phpPgAdmin to specified directory, hit enter.
If you would like to specify a different directory to upgrade from, enter it now.
Press enter again, finally to be told:
Code:
Successfully installed phpPgAdmin {$version} to /var/www/html/phpPgAdmin!

Step 6.
Edit the phpPgAdmin conf file:
Code:
nano /var/www/html/phpPgAdmin/conf/config.inc.php
So that:
Code:
        $conf['servers'][0]['host'] = '';
Now reads:
Code:
        $conf['servers'][0]['host'] = 'localhost';

Step 7.
Test it! Go to http://yourserver/phpPgAdmin/ and log in with "da_admin" and the password you entered in Step 6 of installing PostgreSQL.
 
update CeontOS 7

Is there somewhere an update of this installation for CentOS 7

And is it possible to run it without destroying mysql/phpmyadmin and nginx ?

Greetings Theo
 
I believe the domain was registered by a new owner after its original owner failed to renew it, and it's expired, see what whois says:

Code:
   Updated Date: 2018-03-21T04:27:55Z
   Creation Date: 2018-03-21T04:27:54Z

Hence the link does not work.
 
Back
Top