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 the PostgreSQL server mport via package or mports. To install by package,
you may use a command such as
mport install postgresql91-server.
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.
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.
Via psql prompt
create database dbname ENCODING 'UTF-8' OWNER username;
grant all on database dbname to username;
psql then \d
psql then \l
psql then \d+ table
$ psql > create user username password 'password';