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
- 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).
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.
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.
Author: Kyle Symonds
ksymonds at gmail dot com
