/Guides/FreeBSD/Databases/

General Information

This guide will help you set up, and start running PostgreSQL using the ports tree.  For information of how to use PostgreSQL, try google.  This guide will not help you with PostgreSQL past installing it.

Requirements

You will need the following items to be able to complete this guide:

1. Root access to a FreeBSD machine
   
2. FreeBSD install with ports up-to-date

Compiling

This is where your up-to-date ports come in handy.

# #	cd /usr/ports/databases/postgresql80-server/ make install

Setting up PostgreSQL

There is little to do for the PostrgreSQL set up, but these steps are required.  You could go with the defaults in the rc script, but that leaves you open for attack from local untrusted users.

# # # #

mkdir /usr/local/pgsql/data chown pgsql:pgsql /usr/local/pgsql/data su -l pgsql -c "initdb -D /usr/local/pgsql/data -W -A md5" echo 'postgresql_enable="YES"' >> /etc/rc.conf

This initializes the database, and lets you set the "super user" password, as well as telling PostgreSQL to only allow authentication if the client sends a md5 encrypted password.  This is for security if you are going to be accessing it over the network.

Starting PostgreSQL

There are two ways that you can start PostgreSQL.  Reboot your system, or execute it's rc file.

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

It should now be running.  To see if it really is running, check with ps.

#	ps auxwwww | grep postmaster

You should see a postmaster process running.  That is what the postgresql people called their server, don't ask why!

Using PostgreSQL

Using PostgreSQL can be done one of two ways.  One way is to install phpPgAdmin, which allows you to use a web interface to add users, add databases, and other such neat features, or one can make use of the psql tool that is available.

#	psql <dbname> <username>

I will not walk you through adding users to psql, or even executing queries.  There are excellent sources out there on the web just for that, one of them is the PostgreSQL documentation.

This guide is © 2005 - 2010 Bert JW Regeer.

Author: Bert JW Regeer
bsdguides at 0x58 dot com

There is now an option where you can set the initdb flags in /etc/rc.conf. So you could do the following

## /etc/rc.conf
postgresql_enable="YES"
postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C -W -A md5"

## Shell
/usr/local/etc/rc.d/postgresql initdb
/usr/local/etc/rc.d/postgresql start