Installing and Using Postgresql on FreeBSD

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.

#
#
cd /usr/ports/databases/postgresql84-server
make WITH_OPTIMIZED_CFLAGS=true BATCH=yes install clean

Post-installation Setup

There a few post-installation steps.

Initialize the database

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

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

# service postgresql initdb

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.

# echo ‘postgresql_enable=”YES”‘ >> /etc/rc.conf

Creating a Database

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

#
$
su pgsql
createdb MyDBName

Note: Similarly, use dropdb to drop a database.

Creating a User or Role

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

#
$
su pgsql
createuser -P

Enter name of role to add: MyUserOrRoleName
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y

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.

$ psql MyDBName

MyDBName-#

You are now at the psql prompt.

Connecting as a different user

To connect, use the following command.

$ psql MyDBName MyUserOrRoleName

MyDBName-#

You are now at the psql prompt.

Changing a a User or Role Password

To change a password, use the following syntax.

MyDBName-# alter role pgsql with password ‘pw’;

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;

MyDBName-# \l

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';

MyDBName-# \d

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';

MyDBName-# \d MyTableName

Creating a Table

To create a table, use the following syntax:

MyDBName-# CREATE TABLE Users (
FirstName text,
LastName text,
DateOfBirth date
);

Inserting data into a Table

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

MyDBName-# CREATE TABLE Users (
UserId serial,
FirstName text,
LastName text,
DateOfBirth date
);

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.

4 Comments

  1. secrete says:

    Thanks for sharing your thoughts about freebsd postgresql.
    Regards

  2. [...] Note: I already have a post on Installing and Using Postgresql on FreeBSD. [...]

  3. [...] Note: I already have a post on Installing and Using Postgresql on FreeBSD. [...]

  4. sadotmd says:

    Thanks for this verbose guide

Leave a Reply

How to post code in comments?