PostgreSQL 9.0 Installation and Setup

We will install the latest PostgreSQL from source and set up everything needed for a database from scratch.

PostgreSQL 9.0.0 was released on September 20, 2010.  This version improves performance significantly and adds bunch of new features.  Details information could be found in the release notes: http://www.postgresql.org/docs/9.0/static/release-9-0.

Installation and Configuration

download source code

./configure, make, make install

create user for your database

initdb -D /usr/local/rd/pgsql-9.0.0/data

fix the privileges for your data folder

start pgsql database server: ./postgres -D /usr/local/rd/pgsql-9.0.0/data

quick test to connect to server: ./psql -d postgres -U username

create database owner: ./createuser -P plone

create database: ./createdb -O plone plone

update the authentication config file for the new user and database: /usr/local/rd/pgsql-9.0.0/data/pg_hba.conf

local plone plone md5
host plone plone    127.0.0.1/32          md5

connect to your database: ./psql -d plone -U plone -W

here you go!

PostgreSQL Basic Structure

The first quick look at PostgreSQL database:

1. PostgreSQL Server and Client could be install in anywhere,
2. All files for a PostgreSQL Database is in one folder called "database directory"
3. All configurations for a PostgreSQL Database are in the database directory
4. PostgreSQL server starts with a database directory, where the serve will find all configurations.

Logging Configuration

PostgreSQL supports different ways of logging: stderr, eventlog, syslog, and cvslog.  It is configured in file postgresql.conf.  Here is details documentation about logging: http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html

By default the logging will be sent to stderr and the logging level will be NOTICE and WARNING.

Buildout for PostgreSQL

Buildout could help to

• compile and build PostgreSQL database server and client
• initialize a PostgreSQL databae
• generate the configurations for PostgreSQL database

Basic Tunning

The first thing to optimize a PostgreSQL database will be increasing the shared buffer size, value of shared_buffers in file data/postgresql.conf.  The default value is 32MB, that is only for a very small application.