Set up postgres

Most linux oses come with postgres and mysql out of the box. If yours doesn’t or you want to run a newer version than the the one your os comes with you should be able to install it using the yum installer. Fedora 11 comes with a visual installer you can use to pick and choose what rpms you want running on your machine. Once you have postgres installed on your box you’ll want to initialize some dbs stuff:

[root@bedrock ~]# service postgresql initdb

open up postgres to listen to domains other than localhost:

[root@bedrock ~]# vi /var/lib/pgsql/data/pg_hba.conf

# connect from anywhere but use a cleartext password
host    all         all         0.0.0.0/0             password

There are a lot of flexible options you can use to configure permissions for logging into the database, ip restrictions, usernames, domains, authentication policies, its quite extensive. The pg_hba.conf file has a lot of examples you can gloss over and apply as you like. Once you’ve figured this out you’ll want to fire up the service:

[root@bedrock ~]# service postgresql start

also make sure the postgres service is flagged to fire up on boot:

[root@bedrock ~]# ntsysv

Then you’ll want to log into the database and set up some permissions:

[root@bedrock ~]# psql -d template1 -U postgres

Welcome to psql 8.3.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# create database new_database_dev;
CREATE DATABASE
template1=# grant all privileges on database new_database_dev to admin;
GRANT

template1=# create user developer with password 'somepassword';
CREATE ROLE

template1=# alter user developer with password 'password';
ALTER ROLE

and that’s it, you should be ready to work with your new postgres database.

Related posts:

1. Write a Stored Procedure in Postgres 8+
2. Using Jboss Datasource files
3. Setting up a Subversion server

Avatars by Sterling Adventures