Installing PostgreSQL

General Information

Postgre is a very powerful SQL server that’s known for it’s reliability, scaleability, and ability to deliver. I felt it necessary to dig deeper in to the other SQL servers out there; PostgreSQL ended up impressing me the most.

Requirements

1. Local root access on the box or having your user in the wheel group so you can su to root.
2. A SSH client that can support ANSI colors like puTTy or SecureCRT (only if not logging on locally).
3. Your favorite text editor (I use nano).

Installation

As with almost everything on this site, installation is simple and straightforward. So, login as root or su to root.

# cd /usr/ports/databases/postgresql7
# make install clean; rehash
# su -l pgsql -c initdb

Configuration

Now that the database server is installed, you probably want it to do something and be able to accept incoming connections. First, let’s add a user to the database so we can actually login. As root, su to the user pgsql.

# /usr/local/bin/createuser username

Make the username the same as your local login to the server. This will make everything easier down the road.

# cd /usr/local/pgsql/data
# nano -w postgresql.conf

Uncomment the following lines:

tcpip_socket = false (Change to true)
port = 5432
hostname_lookup = false

There’s some other options in the configuration that google or the postgresql website would probably be really helpful for explanations on. The current changes I mentioned is enough to get the server started and accept connections from phpPgAdmin, php, and other such things.

# nano -w pg_hba.conf

Find the line that says:

host all all 127.0.0.1 255.255.255.255 trusted

and change it to:

host all all 127.0.0.1 255.255.255.255 password passwords

This will force users to login to the server instead of just being able to type in a valid username on the server and being able to access data. Next we need to start the server:

# /usr/local/etc/rc.d/010.pgsql.sh start

Start up the server, install the wonderful phpPgAdmin (requires a little configuration) somewhere and login using the same username and password that you use to login to your shell on the server.