PostgreSQL Documentation

PostgreSQL: PostgreSQL is a sophisticated Object-Relational DBMS, supporting almost all SQL constructs, including subselects, transactions, and user-defined types and functions. It is the most advanced open-source database available anywhere.

Why did the Personal Proxy Statistics team pick PostgreSQL over other databases, such as MySQL, Oracle, Sybase, etc? There are a number of reasons. For starters, it's the most complete, free, Open Source database system available. It is distributed with just about every Linux distribution under the sun, so it is widely available to many users. PostgreSQL has very good documentation and an active mailing list. It also has interface support for almost all programming languages, especially web programming languages such as Perl (via DBI:DBD) and PHP. Compared to MySQL, a PostgreSQL database might be considered slow, but MySQL sacrifices a lot of functionality to achieve that speed increase. Not to mention MySQL's licensing issues. Other commercial database systems such as Oracle, Sybase, or Informix are available, but none of them are truely Open Source, and generally could be considered overkill for what Personal Proxy Statistics does.

Some important PostgreSQL resources are listed below.

Please note that the following documentation was created with the Red Hat 6.2 i386 distribution in mind. PostgreSQL works on all Linux distributions, as well as a number of other UNIX type systems such as FreeBSD. Installation procedures on these systems will be slightly different, and you should consult your distributions documentation on how to setup and initialize PostgreSQL. The version of PostgreSQL we are using is 6.5.3, although later and possibly earlier versions would still work.

Installation: Installing the PostgreSQL 6.5 database system on Red Hat 6.2 Linux is trivial. In fact if you did a "stock" install of Red Hat then PostgreSQL might already be installed for you. If not, there are a number of RPM files that need to be installed. Here's what you need to get started.

To query the RPM database to determine if a package is already installed, issue the command:
rpm -q package

To query the RPM database for all postgresql packages, issue the command:
rpm -qa | grep postgresql

To install an RPM file, you need to log in as root. Issue the command:
rpm -Uvh package.rpm

To recompile a source RPM file (*.src.rpm) and install it, issue the commands:
rpm --rebuild package.src.rpm
rpm -Uvh /usr/src/redhat/RPMS/i386/package.rpm

Here is the list of RPM files from the Red Hat 6.2 distribution CD that you should install if they are not already. The other PostgreSQL packages are not needed, but if you can install them anyway if you wish.

Installing the Perl DBI and DBD::Pg modules is also very easy and straightforward. There are two modules that you should download, perl-DBI and perl-DBD-Pg. You can find the source RPMs mirrored in the ppstats contrib directory or from the CPAN archive on Recompile the source RPMs using the instructions provided above. Note that you must recompile and install the perl-DBI package first.

PostreSQL Initialization: PostgreSQL will not run until the initial template1 database has been created. This is normally done once by issuing the command initdb with a few parameters such as the directory location and path to the PostgreSQL libraries. However, Red Hat 6.2 will do all the hard work for you if you start the PostgreSQL database with the command /etc/rc.d/init.d/postgresql start. This is how the database should always be started. Because it is an rc.d script, you can have it run automatically when your server boots up using the ntsysv tool, or any other SysV utility.

By default, Red Hat 6.2 will install PostgreSQL data files to /var/lib/pgsql/. The template1 database, and all other database created in the default location, will be created in /var/lib/pgsql/base/ under it's own subdirectory. This does make it easy to determine exactly how much space is being used by each database by issuing the command: du /var/lib/pgsql/base/ Here is the output of the command /etc/rc.d/init.d/postgresql start when run for the first time.

Checking postgresql installation: no database files found.

We are initializing the database system with username postgres (uid=103).
This user will own all the files and must also own the server process.

Creating Postgres database system directory /var/lib/pgsql/base

Creating template database in /var/lib/pgsql/base/template1

Creating global classes in /var/lib/pgsql/base

Adding template1 database to pg_database...

Vacuuming template1
Creating public pg_user view
Creating view pg_rules
Creating view pg_views
Creating view pg_tables
Creating view pg_indexes
Loading pg_description
Starting postgresql service: postmaster [18136]

After the template1 database has been created, subsequent calls to /etc/rc.d/init.d/postgresql start should produce the following output:

Checking postgresql installation: looks good!
Starting postgresql service: postmaster [17772]    

PostgreSQL User: The next step that is necessary to initialize PostgreSQL for use with the Personal Proxy Statistics script is for you to create a new PostgreSQL user that can create and maintain a new database. This user should be the same one that runs your proxyper server and dnetc client. It is not recommended that you run proxyper, dnetc, or ppstats as root. Its just way to easy to have your system exploited in this manner. Create a new user explicitly for use with the project, such as 'dnet' or 'rc5'. After you create this new system user, you then need to su -l postgres from the root user. Now that you are logged in as the 'postgres' user, issue the command createuser. You will be prompted for a username, this should be the same username that you run proxyper and ppstats with. Here is some sample output from the createuser command.

Enter name of user to add ---> dnet
Enter user's postgres ID or RETURN to use unix user ID: 503 ->
Is user "dnet" allowed to create databases (y/n) y
Is user "dnet" a superuser? (y/n) n
createuser: dnet was successfully added   

PostgreSQL-Perl The last step to installing PostgreSQL is testing the Perl database interface. Log in as your proxyper user, for example su -l dnet and then run the Perl PostgreSQL test script: perl /usr/lib/pgsql/perl5/ If all goes well, you will get the following results.

Pg::conndefaults ........ ok
Pg::connectdb ........... ok
$conn->exec ............. ok
$conn->errorMessage ..... ok
$conn->db ............... ok
$conn->user ............. ok
$conn->port ............. ok
$conn->cmdStatus ........ ok
$conn->oidStatus ........ ok
$conn->getline .......... ok
$conn->endcopy .......... ok
$result->cmdTuples ...... ok
$result->fname .......... ok
$result->ftype .......... ok
$result->fsize .......... ok
$result->fnumber ........ ok
$result->fetchrow ....... ok
test sequence finished.         

At this point Personal Proxy Statistics is ready to run. Be sure that you have installed PostgreSQL correctly, initialized the template1 database, added the new PostgreSQL user, and tested the PostgreSQL Perl interface. If you should encounter errors in any of these steps, please visit the ppstats Forums and post a message indicating the problems you are having. The two programs that will allow you to work with PostgreSQL directly are psql and pgaccess. Please see the user manuals included with these programs for instructions on how they work.

Good luck!

$Id: postgresql.php3,v 1.5 2000/06/12 00:00:28 kpesce Exp $