PostgreSQL Installation

I have started using Postgresql database from today. In my limited DB knowledge itself, I was able make out the strength of this open source DB. Unlike MySQL, its tough to get clear cut documentation about Postgres from a single source. I have installed Postgres on my devel box running on Ubuntu 9.04 (64 bit edition) . Here is the logs from my console.

1. Installation:
****************
root@luttappi:~/perl# apt-get install postgresql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
libpq5 postgresql-8.3 postgresql-client-8.3 postgresql-client-common postgresql-common
Suggested packages:
oidentd ident-server postgresql-doc-8.3
The following NEW packages will be installed:
libpq5 postgresql postgresql-8.3 postgresql-client-8.3 postgresql-client-common postgresql-common
0 upgraded, 6 newly installed, 0 to remove and 184 not upgraded.
Need to get 5245kB of archives.
After this operation, 21.4MB of additional disk space will be used.
Do you want to continue [Y/n]? Y
<---- snipped ---------->

2. Testing the Installation:
*****************************
root@luttappi:~/perl# su - postgres
postgres@luttappi:~$ psql template1
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

template1=#
template1=# \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(3 rows)

template1=# \q
postgres@luttappi:~$

3. New User addition
************************
root@luttappi:~/perl# useradd mayavi
root@luttappi:~/perl# passwd mayavi
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully

root@luttappi:~/perl# su - postgres
postgres@luttappi:~$ psql template1
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

template1=# CREATE USER mayavi WITH PASSWORD 'password';
CREATE ROLE

template1=# create database kuttoosan;
CREATE DATABASE

template1=# GRANT ALL PRIVILEGES ON DATABASE kuttoosan to mayavi;
GRANT
template1=#

root@luttappi:~/perl# su - mayavi
mayavi@luttappi:~$ psql -d kuttoosan -U mayavi
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

kuttoosan=>


4. Installing perl db modules:
*********************************
root@luttappi:~# apt-get install libapache2-mod-perl2 apache2 libdbi-perl libdbd-pg-perl
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
apache2-mpm-worker apache2-utils apache2.2-common libapache2-reload-perl libapr1 libaprutil1 libbsd-resource-perl libdevel-symdump-perl libmysqlclient15off libnet-daemon-perl libplrpc-perl
mysql-common
Suggested packages:
apache2-doc apache2-suexec apache2-suexec-custom dbishell
The following NEW packages will be installed:
apache2 apache2-mpm-worker apache2-utils apache2.2-common libapache2-mod-perl2 libapache2-reload-perl libapr1 libaprutil1 libbsd-resource-perl libdbd-pg-perl libdbi-perl libdevel-symdump-perl
libmysqlclient15off libnet-daemon-perl libplrpc-perl mysql-common
0 upgraded, 16 newly installed, 0 to remove and 184 not upgraded.
Need to get 5719kB of archives.
After this operation, 17.4MB of additional disk space will be used.
Do you want to continue [Y/n]? Y
Get:1 http://us.archive.ubuntu.com jaunty-updates/main libapr1 1.2.12-5ubuntu0.1 [114kB]
Get:2 http://us.archive.ubuntu.com jaunty-updates/main mysql-common 5.1.30really5.0.75-0ubuntu10.2 [62.9kB]
Get:3 http://us.archive.ubuntu.com jaunty-updates/main libmysqlclient15off 5.1.30really5.0.75-0ubuntu10.2 [1878kB]
Get:4 http://us.archive.ubuntu.com jaunty-updates/main libaprutil1 1.2.12+dfsg-8ubuntu0.3 [79.1kB]
Get:5 http://us.archive.ubuntu.com jaunty-updates/main apache2-utils 2.2.11-2ubuntu2.2 [151kB]
Get:6 http://us.archive.ubuntu.com jaunty-updates/main apache2.2-common 2.2.11-2ubuntu2.2 [827kB]
Get:7 http://us.archive.ubuntu.com jaunty-updates/main apache2-mpm-worker 2.2.11-2ubuntu2.2 [258kB]
Get:8 http://us.archive.ubuntu.com jaunty-updates/main apache2 2.2.11-2ubuntu2.2 [46.3kB]
Get:9 http://us.archive.ubuntu.com jaunty/main libdevel-symdump-perl 2.08-1 [16.4kB]
Get:10 http://us.archive.ubuntu.com jaunty/main libapache2-mod-perl2 2.0.4-5ubuntu1 [1147kB]
Get:11 http://us.archive.ubuntu.com jaunty/main libapache2-reload-perl 0.10-2 [19.9kB]
Get:12 http://us.archive.ubuntu.com jaunty/main libnet-daemon-perl 0.43-1 [46.9kB]
Get:13 http://us.archive.ubuntu.com jaunty/main libplrpc-perl 0.2020-1 [36.0kB]
Get:14 http://us.archive.ubuntu.com jaunty/main libdbi-perl 1.607-1 [793kB]
Get:15 http://us.archive.ubuntu.com jaunty/universe libdbd-pg-perl 2.11.7-1 [212kB]
Get:16 http://us.archive.ubuntu.com jaunty/main libbsd-resource-perl 1.2901-1 [31.8kB]
Fetched 5719kB in 10s (523kB/s)
Selecting previously deselected package libapr1.
(Reading database ... 103255 files and directories currently installed.)
Unpacking libapr1 (from .../libapr1_1.2.12-5ubuntu0.1_amd64.deb) ...
Selecting previously deselected package mysql-common.
Unpacking mysql-common (from .../mysql-common_5.1.30really5.0.75-0ubuntu10.2_all.deb) ...
Selecting previously deselected package libmysqlclient15off.
Unpacking libmysqlclient15off (from .../libmysqlclient15off_5.1.30really5.0.75-0ubuntu10.2_amd64.deb) ...
Selecting previously deselected package libaprutil1.
Unpacking libaprutil1 (from .../libaprutil1_1.2.12+dfsg-8ubuntu0.3_amd64.deb) ...
Selecting previously deselected package apache2-utils.
Unpacking apache2-utils (from .../apache2-utils_2.2.11-2ubuntu2.2_amd64.deb) ...
Selecting previously deselected package apache2.2-common.
Unpacking apache2.2-common (from .../apache2.2-common_2.2.11-2ubuntu2.2_amd64.deb) ...
Selecting previously deselected package apache2-mpm-worker.
Unpacking apache2-mpm-worker (from .../apache2-mpm-worker_2.2.11-2ubuntu2.2_amd64.deb) ...
Selecting previously deselected package apache2.
Unpacking apache2 (from .../apache2_2.2.11-2ubuntu2.2_all.deb) ...
Selecting previously deselected package libdevel-symdump-perl.
Unpacking libdevel-symdump-perl (from .../libdevel-symdump-perl_2.08-1_all.deb) ...
Selecting previously deselected package libapache2-mod-perl2.
Unpacking libapache2-mod-perl2 (from .../libapache2-mod-perl2_2.0.4-5ubuntu1_amd64.deb) ...
Selecting previously deselected package libapache2-reload-perl.
Unpacking libapache2-reload-perl (from .../libapache2-reload-perl_0.10-2_all.deb) ...
Selecting previously deselected package libnet-daemon-perl.
Unpacking libnet-daemon-perl (from .../libnet-daemon-perl_0.43-1_all.deb) ...
Selecting previously deselected package libplrpc-perl.
Unpacking libplrpc-perl (from .../libplrpc-perl_0.2020-1_all.deb) ...
Selecting previously deselected package libdbi-perl.
Unpacking libdbi-perl (from .../libdbi-perl_1.607-1_amd64.deb) ...
Selecting previously deselected package libdbd-pg-perl.
Unpacking libdbd-pg-perl (from .../libdbd-pg-perl_2.11.7-1_amd64.deb) ...
Selecting previously deselected package libbsd-resource-perl.
Unpacking libbsd-resource-perl (from .../libbsd-resource-perl_1.2901-1_amd64.deb) ...
Processing triggers for man-db ...
Processing triggers for ufw ...
Setting up libapr1 (1.2.12-5ubuntu0.1) ...

Setting up mysql-common (5.1.30really5.0.75-0ubuntu10.2) ...
Setting up libmysqlclient15off (5.1.30really5.0.75-0ubuntu10.2) ...

Setting up libaprutil1 (1.2.12+dfsg-8ubuntu0.3) ...

Setting up apache2-utils (2.2.11-2ubuntu2.2) ...
Setting up apache2.2-common (2.2.11-2ubuntu2.2) ...
Enabling site default.
Enabling module alias.
Enabling module autoindex.
Enabling module dir.
Enabling module env.
Enabling module mime.
Enabling module negotiation.
Enabling module setenvif.
Enabling module status.
Enabling module auth_basic.
Enabling module deflate.
Enabling module authz_default.
Enabling module authz_user.
Enabling module authz_groupfile.
Enabling module authn_file.
Enabling module authz_host.

Setting up apache2-mpm-worker (2.2.11-2ubuntu2.2) ...
* Starting web server apache2
apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName
...done.

Setting up apache2 (2.2.11-2ubuntu2.2) ...
Setting up libdevel-symdump-perl (2.08-1) ...
Setting up libapache2-mod-perl2 (2.0.4-5ubuntu1) ...
Enabling module perl.
Run '/etc/init.d/apache2 restart' to activate new configuration!

Setting up libapache2-reload-perl (0.10-2) ...
Setting up libnet-daemon-perl (0.43-1) ...
Setting up libplrpc-perl (0.2020-1) ...
Setting up libdbi-perl (1.607-1) ...
Setting up libdbd-pg-perl (2.11.7-1) ...
Setting up libbsd-resource-perl (1.2901-1) ...
Processing triggers for libc6 ...
ldconfig deferred processing now taking place
root@luttappi:~#


5. Web based administration - PhpPgAdmin
***********************************************
root@luttappi:~# apt-get install phppgadmin
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
apache2-mpm-prefork libapache2-mod-php5 php5-common php5-pgsql postgresql-doc postgresql-doc-8.3
Suggested packages:
php-pear slony1-bin
The following packages will be REMOVED:
apache2-mpm-worker
The following NEW packages will be installed:
apache2-mpm-prefork libapache2-mod-php5 php5-common php5-pgsql phppgadmin postgresql-doc postgresql-doc-8.3
0 upgraded, 7 newly installed, 1 to remove and 184 not upgraded.
Need to get 6157kB of archives.
After this operation, 24.1MB of additional disk space will be used.
Do you want to continue [Y/n]? Y
Get:1 http://us.archive.ubuntu.com jaunty-updates/main apache2-mpm-prefork 2.2.11-2ubuntu2.2 [253kB]
Get:2 http://us.archive.ubuntu.com jaunty-updates/main php5-common 5.2.6.dfsg.1-3ubuntu4.1 [372kB]
Get:3 http://us.archive.ubuntu.com jaunty-updates/main libapache2-mod-php5 5.2.6.dfsg.1-3ubuntu4.1 [2615kB]
Get:4 http://us.archive.ubuntu.com jaunty-updates/main php5-pgsql 5.2.6.dfsg.1-3ubuntu4.1 [57.8kB]
Get:5 http://us.archive.ubuntu.com jaunty/main postgresql-doc-8.3 8.3.7-1 [1963kB]
Get:6 http://us.archive.ubuntu.com jaunty/main postgresql-doc 8.3.7-1 [3408B]
Get:7 http://us.archive.ubuntu.com jaunty/universe phppgadmin 4.2.2-1 [894kB]
Fetched 6157kB in 13s (463kB/s)
dpkg: apache2-mpm-worker: dependency problems, but removing anyway as you request:
apache2 depends on apache2-mpm-worker (>= 2.2.11-2ubuntu2.2) | apache2-mpm-prefork (>= 2.2.11-2ubuntu2.2) | apache2-mpm-event (>= 2.2.11-2ubuntu2.2); however:
Package apache2-mpm-worker is to be removed.
Package apache2-mpm-prefork is not installed.
Package apache2-mpm-event is not installed.
(Reading database ... 104442 files and directories currently installed.)
Removing apache2-mpm-worker ...
* Stopping web server apache2
apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName
... waiting ...done.
Selecting previously deselected package apache2-mpm-prefork.
(Reading database ... 104434 files and directories currently installed.)
Unpacking apache2-mpm-prefork (from .../apache2-mpm-prefork_2.2.11-2ubuntu2.2_amd64.deb) ...
Selecting previously deselected package php5-common.
Unpacking php5-common (from .../php5-common_5.2.6.dfsg.1-3ubuntu4.1_amd64.deb) ...
Selecting previously deselected package libapache2-mod-php5.
Unpacking libapache2-mod-php5 (from .../libapache2-mod-php5_5.2.6.dfsg.1-3ubuntu4.1_amd64.deb) ...
Selecting previously deselected package php5-pgsql.
Unpacking php5-pgsql (from .../php5-pgsql_5.2.6.dfsg.1-3ubuntu4.1_amd64.deb) ...
Selecting previously deselected package postgresql-doc-8.3.
Unpacking postgresql-doc-8.3 (from .../postgresql-doc-8.3_8.3.7-1_all.deb) ...
Selecting previously deselected package postgresql-doc.
Unpacking postgresql-doc (from .../postgresql-doc_8.3.7-1_all.deb) ...
Selecting previously deselected package phppgadmin.
Unpacking phppgadmin (from .../phppgadmin_4.2.2-1_all.deb) ...
Processing triggers for doc-base ...
Processing 23 changed, 1 added doc-base file(s)...
Registering documents with scrollkeeper...
Setting up apache2-mpm-prefork (2.2.11-2ubuntu2.2) ...
* Starting web server apache2
apache2: Syntax error on line 278 of /etc/apache2/apache2.conf: Could not open configuration file /etc/apache2/conf.d/phppgadmin: No such file or directory
...fail!
invoke-rc.d: initscript apache2, action "start" failed.

Setting up php5-common (5.2.6.dfsg.1-3ubuntu4.1) ...
Setting up libapache2-mod-php5 (5.2.6.dfsg.1-3ubuntu4.1) ...

Creating config file /etc/php5/apache2/php.ini with new version
Your apache2 configuration is broken, so we're not restarting it for you.

Setting up php5-pgsql (5.2.6.dfsg.1-3ubuntu4.1) ...

Setting up postgresql-doc-8.3 (8.3.7-1) ...

Setting up postgresql-doc (8.3.7-1) ...
Setting up phppgadmin (4.2.2-1) ...
* Reloading web server config apache2
root@luttappi:~#

6. PhpPgAdmin configuration file:
**************************************

root@luttappi:/usr/share/phppgadmin/conf# pwd
/usr/share/phppgadmin/conf
root@luttappi:/usr/share/phppgadmin/conf# ls -l config.inc.php
lrwxrwxrwx 1 root root 30 2009-08-10 13:24 config.inc.php -> /etc/phppgadmin/config.inc.php
root@luttappi:/usr/share/phppgadmin/conf#

7. Access:
************
http://localhost/phppgadmin