Postgre SQL

Purpose

To setup a working version of PostgreSQL database on a Debian machine.

Postgres iis a full feature ACID compliant database that is licensed under a BSD-style license and is free and open source software.It is an object-relational database management system (ORDBMS). Although not as popular as MySQL used by many websites in conjunction with MyISAM and php, it is an often overlooked extremely capable database with rich features/syntax very similar to Oracle and DB2, offering transactional capabilities years before InnoDB was added to MySQL.

Always make proper backup before proceeding.
Please report problems and direct all inquiries to Contact US.

Prerequiste

One or more computer with Debian distribution, Lenny. Tested on i386 platform with PostgreSQL 8.3 and pgadmin III.
Root access.

Procedures

1. Installation

From a terminal console in the database server, run as root or use sudo:

 # apt-get install postgresql, postgresql-client

From a terminal console in the client computer, run as root or use sudo:

 # apt-get install postgresql-client, pgadmin3

2. Configuration

Postgresql by default creates a service account called postgres and will run its process under this account. We are going to create a more restricted user account - a proxy account for making connection with it from a client machine.

2.1 Specify Communication Interface

Edit '/etc/postgresql/8.3/main/postgresql.conf' file to specify the communication interface. The default is the Unix Socket, it's for local connection only. Enable TCP/IP listening, remove the '#' comment and change it to the ip interface of database server. e.g. 192.168.15.135. You can also set a range of address with ip# and mask:

	listen addresses 192.168.15.135

For PostgreSQL prior to version 8.0 enable tcpip_socket:

	tcpip_socket = true

Make sure that there is no comment character ("#") in front of that line. JDBC driver requires a TCP/IP port. If you are using it locally, enable localhost or use 127.0.0.1

2.2 Controlling who can login and where these connections may come from.

The '/etc/postgresql/8.3/main/pg_hba.conf' file controls which machines and users can access the database on a given machine via TCP/IP. Add the following line and replace demodb with your database name and user_demo with your user name. Also replace the ip-address with the client machine's ip address - 192.168.15.180.

	 # host   DATABASE    USER          IP-ADDRESS     IP-MASK         METHOD  [OPTION]
	 host     demodb      user_demo     192.168.15.180 255.255.255.255 md5

You can select different authentication method:

	 authen | description
	--------+-----------------------------------------
	 trust  | unconditional acceptance, no password.
	 reject | unconditional reject, banned.
	  md5   | encrypt password with md5.
	  crypt | use crypt() for pre-7.2 clients.
	password| sent password in plain text.
	 krb4/5 | sent using kerberos 4 or 5.
	 ident  | assume o/s user name was authenticated.

For PCI compliance, PostgreSQL can also do SSL connections but we leave that out of this article.

All files in "/etc/postgresql/8.3/main/" are owned by postgres, please make sure nothing is changed, otherwise you make get funny errors. You can check with the following command.

 # ls -l
 -rw-r--r-- 1 postgres postgres   316 2009-11-10 17:32 environment
 -rw-r----- 1 postgres postgres  3844 2009-11-17 19:24 pg_hba.conf
 -rw-r----- 1 postgres postgres  1460 2009-11-10 17:32 pg_ident.conf
 -rw-r--r-- 1 postgres postgres 16813 2009-11-17 13:11 postgresql.conf
 -rw-r--r-- 1 postgres postgres   378 2009-11-10 17:32 start.conf

If the ownership/group is altered, you can reset it with chown.

After the configurations are completed, reload the parameters by restarting the server.

 # /etc/init.d/postgresql-8.3 restart

3. Create a Database and a User Account

The default user which PostgresSQL runs under is postgres, it is also a superuser with special privileges. We will be using it to create the initial database, user account and a working schema.

3.1 Create the Database.

If you make a mistake, you can always drop the database with dropdb <dbname>. Use it with caution because it won't give you warnings. Replace demodb with your database name.

First, switch to user id postgres, then create the database under it. The database will be owned by postgres.

 # su postgres
 $ createdb demodb

Alternatively, you can create databases in another location, here is how:

Create the location with postgres as the owner, e.g. /home/postgres/data
switch user to postgres and start psql client,
create tablespace in new location, i.e. CREATE TABLESPACE <spacename> LOCATION '/home/postgres/data';
set default table space, i.e. SET DEFAULT TABLESPACE=<spacename>;
Create database in new tablespace. i.e. CREATE DATABASE demodb TABLESPACE <spacename>

3.2 Create Store Procedure Language

Create pgsql for use with store procedures. Store procedure won't work without this.

 $ createlang plpgsql demodb

There are other languages but we won't get into it here, to wet your appetite, here is a list:

	Procedural Language
	-------------------
	PL/pgSQL
	PL/Tcl
	PL/Perl
	PL/Python

3.3 Connect to Database with pSQL client.

Start Postgres client, and go into demodb as user id postgres. Some common parameters are -L for logfile, -h for hostname, -U for userid. If the user has a password, it will automatically prompted. Without providing a userid, it will assume the current operating system user id.

 $ psql -d demodb

3.4 Create New User

Create a user with password for remote connection from inside psql. You won't be able to connect remotely without a password. The exception is when you are a local user with a operating system account with the same user id.

 demodb=# CREATE USER user_demo WITH PASSWORD 'password';
 CREATE ROLE

For existing users without password, you can add password with ALTER USER.

3.5 Schema (Optional)

Create a schema and associate it to the user. You can skip this and the next three steps if you choose to use the default "Public" schema only.

There are three usage patterns for schemas:

Everyone use the public schema,
Everyone has their own schema. Name the schema the same as the user id, this is good for the development environment where each developer can do development in their own schema (this is how most database uses schema),
Everyone shares a schema. For example, a schema to separate objects, say for a third party application or a stagging schema prior to promotion to production.

3.5.1 Create Schema

Append command with 'AUTHORIZATION user_demo' if you wish the said user to own the created object in the schema in the same statement.

 demodb=# CREATE SCHEMA schema_demo;
 CREATE SCHEMA

3.5.2 Grant Privilege to Schema

Users don't have any rights under the newly created schema, we need to grant privileges to create database, make queries, etc. Either selectively grant privilege to our schema. :

 demodb=# GRANT USAGE ON SCHEMA schema_demo TO user_demo;
 GRANT USAGE

Or alternatively, grant all privilages to schema:

 demodb=>GRANT ALL ON SCHEMA schema_demo TO user_demo;

3.5.3 Set Default Search Path

Set the default path so that when the remote user connects, it will be the default schema and no qualifying name is required when accessing objects. The default is the Public schema.

 demodb=# ALTER USER user_demo SET search_path TO schema_demo, public;
 SET

Show current search path as baseline, leave the client and check again after connecting as new user.

 demodb=# SHOW SEARCH_PATH;
 search_path
 ----------------
 "$user",public
 (1 row)
 demodb=\q
 #

4. Backup and Restore

Dumps can be created while the database system is running; pg_dump creates an internally consistent backup. This means any pending transaction while dumping will not be part of the back up.

Exceptions are those operations that need to operate with an exclusive lock, such as VACUUM FULL.

A backup user account should be created with read only access. This will need to be both an operating system account and a PostgreSQL account. A scheduled cron job will backup the database periodically.

Backup schema only with the following:

 # pg_dump --schema-only demodb

Or backup database schema and data plus blob and OID (large binary objects) with the following:

 # pg_dump -Ft -ob demodb > backup.tar

To restore data, do the following:

 # createdb oscardb
 # pg_restore -d demodb  backup.tar

Use pg_dumpall for clusters. Clusters is beyond the scope of this article.

Test and Verify

From a client machine, connect remotely via a terminal.

 $ psql -d demodb -h server.domain.com -U user_demo
 Password for user demo_user:
 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
 demodb=> show search_path;
  search_path
 ---------------
 schema_demo, public
 (1 row)
 demodb=>CREATE TABLE test ...

After databases, indices and data are created, run analyze or vacuum analyze to update planner.

 demodb=>ANALYZE;
 demodb=>\q

If you are connecting via a graphical client such as pgAdmin, here is the connection info:

Some Words of Advice

For crying out loud,

do some planning,
avoid creating database objects with reserved names,
create store procedures, triggers sparingly,
choose a naming convention/standard and be consistent,
always create a primary key with each of your tables,
n-way joins are very expensive, limit multiple joins where practical - use the KISS principle,
consider normal forms when designing and break it when there are good practical reasons.

But most of all have fun!

References

Page locked (last edited December 2, 2009) [info] [diff])
FindPage by browsing or searching

5 best incoming links: FrontPage (4)
5 best outgoing links:
5 most popular nearby: FrontPage (2047)