NOTES/PostgreSQL

[ HOME | REFERENCE | FreeBSD | MySQL | PostgreSQL | Apache | qmail | PHP | Pure-FTPd ]

introduction

I initially wanted to use PostgreSQL for twinwork.net, but I didn't like administrating through the console, and I couldn't find a decent PostgreSQL front end. That's all changed now. pgAdmin III has matured greatly as a PostgreSQL front end, and I've also matured as a database administrator.

MySQL's 3.23.xx series didn't cut what I needed to do. NOTES now has a MySQL installation for a 5.0.xx series, but I opted to move over to a totally different database. I don't want to start a war over which database is better. But MySQL and PostgreSQL NOTES will stay on NOTES.

Please check the PostgreSQL website for my information.

These NOTES were done with PostgreSQL 8.3.3.


track

  1. ports & rc configuration
  2. create database cluster
  3. edit configuration files
  4. start the server
  5. create privileged user & test database
  6. connect with pgAdmin III
  7. backup
  8. references

i. ports & rc configuration

Install PostgreSQL from the ports collection. As root

# cd /usr/ports/databases/postgresql83-server
# make install clean

You can use the default options that are presented.

After installation, edit /etc/rc.conf to enable PostgreSQL at startup.

# echo 'postgresql_enable="YES"' >> /etc/rc.conf
# echo 'postgresql_data="/usr/data/pgdb"' >> /etc/rc.conf


ii. create database cluster

PostgreSQL's database directory isn't set during ports' installation. You'll need to create a database cluster. The ports' installation created a new user, pgsql. Use sudo to do everything as the pgsql user.

# mkdir -m 700 /usr/data/pgdb
# chown pgsql:pgsql /usr/data/pgdb
# /usr/local/etc/rc.d/postgresql initdb

 


iii. edit configuration files

There are a number of PostgreSQL configuration files you may want to edit. One is ~pgsql/.profile which allows you to set certain environmental values.

The other configuration file you may want to check is /usr/data/pgdb/postgresql.conf. This configuration file has many options including PostgreSQL tuning. Use with care and always check the PostgreSQL manual if you are unsure of something.

If you plan on connecting remotely to your PostgreSQL database, you will want to edit /usr/data/pgdb/pg_hba.conf. Read pg_hba.conf carefully and follow the examples they give. It's not that hard to follow.

You might want to use md5 for METHOD in pg_hba.conf if you plan to connect to PostgreSQL with pgAdmin III.


iv. start the server

Start the PostgreSQL server with the following command

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

 


v. create privileged user & test database

Add a new user that has the ability to create new users and create new databases.

# sudo -u pgsql createuser -E -P -a -d rey

A password prompt will display for you to enter your password.

If you want to add a normal user that does not have the ability to add new users or create new databases, do the following

# sudo -u pgsql createuser -E -P -A -D john

You may want to create a test database to mess around with.

# sudo -u pgsql createdb test

If you want to create a database owned by a particular user, do the following

# sudo -u pgsql createdb -O john test

Simple enough.


vi. connect with pgAdmin III

First thing you need to do is download the latest version of pgAdmin III.

Install it.

Run it.

And hit the big square button on the upper left part of pgAdmin III to connect to your PostgreSQL server.

Wasn't so tough, was it? If you configured your pg_hba.conf file correctly, you should be able to connect without any problems.

PostgreSQL also support SSL. I haven't been able to figure that out yet, so instead of connecting with an insecure connection, I connect over SSH. Putty is a great SSH client for Windows. Just setup port forwarding (default, 5432 for PostgreSQL) and Putty, and you'll be all set.

I do the same with MySQL (even though MySQL 5.x.xx suports SSL, I haven't been able to figure it out yet). MySQL uses the default port of 3306.


vii. backup

...


viii. references

 


Valid XHTML 1.0!QUESTIONS/COMMENTS/CORRECTIONS? notes@twinwork.net
$NOTES: /postgresql/, v.0.17 2010/12/11 13:44:35 PST /7235/ NkM$
Maintainer: Neafevoc K. Marindale