Postrgesql is an excellent alternate to MySQL.

It is BSD Licensed instead of GPL, which is especially more attractive if you need to distribute the database software.  In fact, if you are reselling a product, and paying a license fee to MySQL you probably should save your company the money and move to Postgresql.  LANDesk, the company I work for, had just such an experience with our Management Gateway device.

Setting up FreeBSD

Ok, so I already have a post on this here:

How do I install FreeBSD?

Also, make sure to download ports.

What are the first commands I run after installing FreeBSD

Installing Postgresql on FreeBSD

As always, installing software on FreeBSD is simple using the ports system.

Post-installation Setup

There a few post-installation steps.

Initialize the database

Or starting with FreeBSD 8.1, you can now run this command:

Make any changes to the postgresql.conf

The postgresql.conf is located in /usr/local/pgsql/data.

Open the file and read through it and make any desired changes.

Common Changes

Here are two common changes.

Enabled remote connections

If the database is to be accessed by the network, then you should at least uncomment the setting #listen_addresses = 'localhost' and change it to listen_addresses = '*'.

Changing the default TCP Port

Uncomment the setting #port = 5432 and change the port number to the desired value.

Configure password authentication

1. Change to the /usr/local/etc/pgsql/data directory.
2. Edit the pg_hba.conf and change the default authentication method to something more secure, such as md5.

   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD# "local" is for Unix domain socket connections only
   local   all         all                               md5
   # IPv4 local connections:
   host    all         all         127.0.0.1/32          md5
   # IPv6 local connections:
   host    all         all         ::1/128               md5
   

Configure the postgresql server service to start on reboot

Add the string postgresql_enable="YES" to /etc/rc.conf.

Creating a Database

To create a database, su to pgsql and run createdb.

Note: Similarly, use dropdb to drop a database.

Creating a User or Role

To create a user, su to pgsql and run createuser.

If you are not using password authentication, then you can exclude the -P.

Using psql client

A simple way to connect to postgresql is using the shell-based client, psql.

Connecting

To connect, use the following command.

You are now at the psql prompt.

Connecting as a different user

To connect, use the following command.

You are now at the psql prompt.

Changing a a User or Role Password

To change a password, use the following syntax.

Show Databases

To show database, simply type \l, (which is the lowercase letter L not the number 1) which is short hand in psql for this query:
SELECT datname FROM pg_database;

Show Tables

To show tables in the current database, simply type \d, which is short hand in psql for this query:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Show Tables

To show columns of a table in the current database, simply type \d table, which is short hand in psql for this query:
SELECT column_name FROM information_schema.columns WHERE table_name ='table';

Creating a Table

To create a table, use the following syntax:

Inserting data into a Table

To insert data into a table, use the following syntax:

Ok, from here you should be able to start figuring everything out on your own.

Resources:
http://www.postgresql.org/docs/8.4/interactive/index.html

---

Copyright ® Rhyous.com – Linking to this page is allowed without permission and as many as ten lines of this page can be used along with this link. Any other use of this page is allowed only by permission of Rhyous.com.