PostgreSQL Documentation

PostgreSQL

Introduction

PostgreSQL is a full featured, modern database available in mports.

Recently, the MidnightBSD project began migrating our package cluster software, Magus, over to PostgreSQL. This is a starter guide on basic functions under PostgreSQL.

Install

Install the PostgreSQL server mport via package or mports. To install by package, you may use a command such as mport install postgresql91-server.

For mports, cd /usr/mports/databases/postgresql94-server; make install clean

There are several versions of PostgreSQL available in mports and via package. You may wish to check for the latest.

A new user called pgsql will be created as part of the installation. You may become root and then run su - pgsql to get access to this user. The command line utilities will be available on the path.

Examples

These examples assume you're using the pgsql user account. May of these commands can be done through direct connects to the server as well.

Create Database

Via console: createdb databasename

Via psql prompt

create database dbname ENCODING 'UTF-8' OWNER username;
Granting Permissions
grant all on database dbname to username;
List Tables

psql then \d

List Databases

psql then \l

Describe Table

psql then \d+ table

Create User
$ psql > create user username password 'password';