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.
- Local root access on the box or having your user in the wheel group so you can su to root.
- A SSH client that can support ANSI colors like puTTy or SecureCRT (only if not logging on locally).
- Your favorite text editor (I use nano).
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
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.