Dustin's PostgreSQL Setup
Created on January 6th, 2000.
PostgreSQL is a very powerful Object-Relational DBMS. It has been in a "competition" with MySQL for a while now, and a lot of MySQL advocates despise PostgreSQL, and vice-versa. I have begun to like PostgreSQL a lot more recently.
At my former job and my current job, I have done several projects that relate to PostgreSQL. The biggest perk for MySQL was speed, but PostgreSQL is about even, if not faster than MySQL now. PostgreSQL is very robust, and has supprt for many things that are lacking in MySQL; transactions, stored procedures, row-level locking, sub-selects.
PostgreSQL is also supported very much the same way that MySQL is in the PHP community. And, if you use the DB Abstraction classes found with PEAR, then you will have very few problems switching to PostgreSQL.
It is very easy to setup PostgreSQL. It has to run as it's own user, not root. Typically, one creates a 'postgres' user, and that is what I will demonstrate. You should be root for all of the following steps, until I instruct otherwise. You will want to get the latest stable source from http://www.postgresql.org and place it in '/usr/local/src/'. I will show my examples with postgresql-7.0.3.tar.gz, because that was the latest stable at the time of this writing.
adduser postgres cd /usr/local/src tar -zxvf postgresql-7.0.3.tar.gz cd postgresql-7.0.3
This will have created your user and unpacked your source code, you now need to configure and build, which is fairly simple:
./configure --prefix=/usr/local/pgsql --with-odbc make make install
Now you will have the latest build. There are a few residual things to be done. First, I would make all of this belong to your postgres user.
cd /usr/local chown -R postgres pgsql chgrp -R postgres pgsql
I like to make the postgres user's home directory to /usr/local/pgsql, so edit your /etc/passwd file, and replace /home/postgres with /usr/local/pgsql.
Now you should add the following line to /etc/ld.so.conf:
/usr/local/pgsql/lib
Once this is done, you have no more need to be root, switch user to postgres and build your initial system databases.
su postgres cd /usr/local/pgsql/bin ./initdb -D /usr/local/pgsql/data
Now you will be ready to start the postmaster, which keeps track of all the postgres processes. You will need to specify the '-D' flag just like before, which tells the DMBS where the data directory is located, and it would also be a good idea to use the '-i' flag, which tells it to listen on TCP/IP ports as well as Unix Sockets.
./postmaster -D /usr/local/pgsql/data -i &
Now you have several options on how to administrate and use your new system. First, we will create a database called example:
./createdb example
Now you can use the psql client to work on this:
./psql example
This is the SQL interface to PostgreSQL. You can also install the PostgreSQL ODBC driver found at the postgresql.org site, and use a product called WinSQL, for windows, then you can sit at your windows box to adminstrate it. If you plan on using PHP, or you want a web-based admin client, try phpPgAdmin, which is a port of the MySQL version called phpMyAdmin.
PostgreSQL is is very advanced, and you may not need all of the features, but the documentation is a great place to read further. Here are a few other helpful things...
You can easily maintain who can access the PostgreSQL DBMS with the pg_hba.conf found in your data directory, /usr/local/pgsql/data
There is a JDBC driver available for all of you Java Programmers. It can easily be found from http://freshmeat.net
Have fun with PostgreSQL, I know I will.
Having trouble? Got questions? Require further assistance? If so please feel free to visit our Help Forums and ask the experts!