PostgreSQL

Audience

Attendees wishing to use the PostgreSQL database who are already familiar with the basics of SQL.

Prerequisites

Attendees will be familiar with:

• The Unix command line
• Unix administration
• Simple SQL statements
• Programming concepts

Duration

4 days. Hands on.

Course Objectives

Delegates will develop a good understanding of PostgreSQL use, installation and administration techniques and procedures.

Course Content

PostgreSql Basics
· Installation of PostgreSQL on Red Hat Linux
· Quick overview of the relational model, keys, normalization with some simple examples.
· PostgreSQL Server Architecture
· Popular PostgreSQL clients
· Psql, The PostgreSQL Command Line Interface
· pgadmin
· phppgadmin
· Using ODBC with other Clients

PostgreSql Intermediate/Advanced
· Triggers, checkpoints, indices
· Real-time storage issues such as update constraints
· Shell script commands, overview of embedded sql methods including common pitfalls

API's for both programmers and administrators
· SLONY and Database replication
· PostgreSQL security, user access & permissions
· Vacuum, PGPOOL
· pg_dump and restore options
· Backup and Recovery and PITR
· Upgrading PostgreSQL
· Warm Standby/Log Shipping

Performance Tuning, and Troubleshooting
· PostgreSQL performance tuning
  Deleting, Vacuum , Analyze, Reindex, duplication issues.
  Initiations of system(max no of connections, inserts, table locking issues, using the logfiles

PostgreSQL Troubleshooting
  Common Pitfalls
  Slow jobs –how to check for/better handle these, killing tasks,
  Restarting, reloading, configuration files

Tips and Tricks