PostgreSQL Administration Course, Morrisville, NC, March 2007
In mid-March I attended a PostgreSQL Administration Course put on by the Open Technology Group http://www.otg-nc.com in Morrisville NC. Our instructor Chander Ganesan took us through the full gamut of PostgreSQL administration and configuration, from manual installation to performance tuning to third-party replication tools. Our course covered items such as secure access, user management, schemas, tablespaces, backup and recovery, triggers, and query plan analysis.
Our Instructor Chander Ganesan
If I wasn’t already convinced of the necessity to migrate from PostgreSQL 7.4 to 8.x, this course certainly drove that point home. Many of the performance tuning configuration variables we delved into are not even available in 7.4. One fellow classmate noted a 25% increase in database performance after migrating to 8.x in their workplace. I will cover Postgres Configuration & Performance Tuning more in-depth in a future blog.
Performance aside, 8.x offers a lot of other improvements from 7.4. One thing I especially like is the ability to write functions with dollar-quoting instead of single-quoting which is the only option in 7.4. I’ve needed to write a lot of complicated PL/pgSQL functions, so this makes things a lot less of a pain.
In the course I learned lots of tricks and tips that I wish I knew long ago, such as the psql meta-commands \timing (logs all statement duration times to the client), \x (toggles expanded display), and \e (opens a vi editor for your query). I also learned that views can be mutable by placing rules on them that mutate the underlying tables. This is especially useful for restricting write access of specific table columns, or customizing write access to specific users.
The course also covered backups and replication. I found the point in time recovery section very useful. We also did an exercise of installing and configuring Slony for replication, which our company is now considering as an option. I will cover these topics more in-depth in a future blog.
Chander was extremely knowledgeable and very helpful in answering the multitude of questions we threw his way. The class size (12) gave the right balance between personal attention and good class discussions. The subject matter of the course was very relevant to the work we do here at 2Paths, and my training has been a catalyst in getting an action plan into place for our company-wide database version migration and performance tuning standards. I would highly recommend this course, and would also consider taking the advanced PostgreSQL Performance Tuning course as a follow-up.
Future Blog Installments:
- Database Backup, Recovery and Replication
- Postgres Configuration & Performance Tuning
