I was excited when PostgreSQL 9 Admin Cookbook by Simon Riggs and Hannu Krosing and the companion book PostgreSQL 9 High Performance, by Greg Smith
were available. All three authors are well known experts in the PostgreSQL community and consultants at 2ndQuadrant, so you are sure to learn a lot from both books.
Both books are published by Packt Publishing and can be bought directly from Packt Publishing or via Amazon. Packt is currently running a 50% off sale if you
buy both books (e-Book version) directly from Packt. In addition Packt offers free shipping for US, UK, Europe and select Asian countries. The pair of books make attractive companions.
The main thing I felt missing in this duo was a book dedicated to PostgreSQL: The platform
that would cover all the various PL languages and the various neat ways PostgreSQL is used and has been extended by many to do things one would not normally expect of a database.
Some day perhaps someone will write such a book.
This article is a review about PostgreSQL 9 Admin Cookbook and we'll be following up later with PostgreSQL 9 High Performance.
This is my first book review. I have a lot of patience for writing, but little patience
when it comes to reading. That said, I found PostgreSQL 9 Admin Cookbook an easy and enjoyable read,
and a book that I managed to learn more tricks from than I care to admit. It is a handy book to have for reference regardless of if you consider yourself
a novice, intermediate or advanced user.
As the book title suggests, it's a cookbook, but a cookbook that combines a question and answer style with a discussion
style of writing. The tasks are neatly categorized into 12 chapters and each task smoothly builds on previous tasks discussed.
It is still categorized in such a way that you can jump to a particular task you are currently having problems with without having read the other parts of the book.
Although it is titled PostgreSQL 9 -- it covers earlier versions as well.
This is one of those books I wish I had when training some of our customers or had early on. A lot of the questions - we are commonly asked
or have stumbled on - like how to troubleshoot bad queries, how to tell what are my biggest tables, how to deal with data corruption etc, are
all succinctly covered in this book.
Just to get a taste of what this book offers:
- Chapter 1: First steps This is mostly a newbie chapter, that introduces you to PostgreSQL, guides you thru connecting to the database using commandline and PgAdmin. Some
other examples of commonly used GUIs. Configuring
access control, troubleshooting failed connections. It provides tips both for the Linux as well as the Windows user.
- Chapter 2: Exploring the database starts to get into what I would call intermediate territory. It covers tasks such as determining
where your database files are, how to determine disk space utilization for both whole database and individual tables. Getting quick estimate
of number of rows for large tables where doing a count would be really slow. Using psql and the various system tables to determine object dependency.
- Chapter 3: Configuration A good chapter not just for PostgreSQL users but I would say any database designer. I think this chapter holds useful nuggets for all levels of users. It covers basic considerations when planning your
database and based on the needs how to configure the key PostgreSQL configuration settings by server, user, and database.
It had tricks like how to determine which parameters are at non-default settings. I am ashamed to admit, that I learned a lot from
this chapter. Stuff I really should have known before. My only lame excuse is that PostgeSQL has always worked so well, that
I never had a need to delve into these nuances.
- Chapter 4: Server Control - A very intermediate chapter slowly getting into more nuances. It covers connection pooling, viciously and not so viciously kicking users off, gracefully shutting down, using schemas to partition data. Again a lot of stuff here
I didn't know so will have to reread when I find myself in these situations.
- Chapter 5: Tables & Data - This chapter covers general good practices for namings tables and other objects, setting up keys, finding duplicate data, deleting duplicate data,
. My favorite pasttime, techniques to generate test data. It concludes with loading data from flat files and csv files. Fundamentals of using pgloader
utility created by Dmitri Fountaine.
- Chapter 6: Security Covers new GRANT management in PostgreSQL 9.0, setting up users, auditing DDL and data changes, integrating with LDAP, using SSL,
ensuring secure passwords, and encrypting data with pgcrypto.
- Chapter 7: Database Administration - starts getting a bit into creating functions using plpgsql, plproxy. Running SQL scripts in parallel. Fundamentals of PostgreSQL DDL such
as creating schemas, tablespaces and setting new configuration options for those introduced in 9.0. Moving objects to different schemas and tablespaces.
- Chapter 8: Monitoring and Diagnosis - covers how to catch blocking queries, query monitoring. Using pgFouine for log processing and alerting you of issues.
The thing I most enjoyed about this chapter were the cut and paste queries utilizing various PostgreSQL system tables to track down issues.
- Chapter 9: Regular Maintenance - this topic is normally a very dry one,at least for me. It covers fundamentals you should
know, like backup,recovery testing, reclaiming space, transaction wrap around (less of an issue with newer versions of PostgreSQL), but that puts a narcoleptic like me into a comatose state. I awoke when reading the topic of Carefully removing unwanted indexes. It shows
you how to turn indexes off without removing them so you can see if they are really useful. I've really got to try this one out.
- Chapter 10: Performance & Concurrency - My favorite chapter. Can't get enough of this stuff. Just read it. You'll learn a lot regardless your walk of life.
- Chapter 11: Backup & Recovery - Covers using PostgreSQL specific tools such as pg_dump and other tools such as rman and rsynch for doing differential backups.
- Chapter 12: Replication and Upgrades - Covers in a nutshell everything you could ever dream of knowing (well all my limited imagination can dream of) about replication and using replication for high availability and how to do upgrades.
It covers replication terminology, the new 9.0 streaming replication, hot standby as well as longtime favorites like Londiste, Slony-2.0 and load balancing with pgpool, and using pg_upgrade.
PostGIS in Action
About the Authors
Consulting
Tracked: Nov 03, 22:53
Tracked: Dec 28, 16:29