PostgreSQL 9 Admin Cookbook Review

December 22, 2010

Uncategorized

I’ve already finished reading “PostgreSQL 9 Admin Cookbook”:http://link.packtpub.com/zoKTs1, authored by Simon Riggs, Hannu Krosing, and published by Packt Publishing.

This is my first book review, I thought it should be quite easy to write one. So far I’ve written many different academic papers, many blog articles, almost one full chapter of my new book, but there wasn’t any review never before. I like reading, I also like reading technical books, but usually I read fast and only to get the information that I need. This time it was a little bit harder, I had to read every page in the book, but not only for getting the information, but also for gathering some critical remarks about that.

The whole idea for this book is great. First of all it is a cookbook. It means that there are many problems and practical answers to those problem in this book.

Personally I haven’t found there any new information that I didn’t know earlier, so reading this book was a little bit boring sometimes. I also haven’t found any errors or misconceptions there, and I think it is a really great source of information on administering PostgreSQL database. Some topics were not fully covered, but on the other hand I think that it wouldn’t be possible to cover each possible topic in such a short book.

This book is titled for PostgreSQL 9, but most of the recipes are suitable for previous versions too. Although sometimes with small changes, but the whole idea of each solution shouldn’t stay the same, so I think everyone will be able to find out how to perform all the tasks in earlier PostgreSQL versions. What’s more I think that in the future versions they would be valid too.

*Chapters Review*

*Chapter 1*: First Steps. The most simple chapter you could imagine, just basic PostgreSQL installation and connecting.

*Chapter 2*: Exploring the Database. Basic checking database version, locale, size of database files and quite nice way of performing fast count(*).

*Chapter 3*: Configuration. This chapter should be read by every system administrator who wants to make PostgreSQL a little bit more efficient. It also covers more advanced topics such as changing maximum kernel memory per process and installing external modules.

*Chapter 4*: Server Control. This is a must read for every administrator who wants to run sql patches on production database or upgrade PostgreSQL version. Sometimes it is not trivial, as there can be some database connections blocking something, or some scripts try to connect. This chapter will tell you how to start, stop database, disconnect user, run multiple servers on the same system and create a connection pool.

*Chapter 5*: Tables & Data. Interesting chapter about good practices on naming database objects, generating sample data, loading data from files and spreadsheets.

*Chapter 6*: Security. Chapter on granting and revoking access and other rights for database objects, configuring logging and encrypting data and connection.

*Chapter 7*: Database Administration. Fundamentals of DDL operations for creating schemas, tablespaces, and also moving objects between schemas and tablespaces. It also covers two much more advanced topics such as connecting to other databases using dblink and creating updateable views using rules.

*Chapter 8*: Monitoring and Diagnosis. This chapter describes how to monitor queries, check if user is connected, get blocking queries and how to use pgFouine for tracking slow queries.

*Chapter 9*: Regular Maintenance. Chapter on fixing bloat, taking care of vacuuming tables and indexes.

*Chapter 10*: Performance & Concurrency. Great chapter on finding slow queries, speeding them up and using optimistic locking.

*Chapter 11*: Backup & Recovery. Another must read for admins. Chapter on backing up database and restoring from backups. It also covers making differential/incremental and streaming backups.

*Chapter 12*: Replication & Upgrades. I think the best chapter of all, full of replication: slony, pgpool, londsite and hot standby.

*Small Problems*

This book is really great, although I found some errors with listings formatting and confusing pdf index, that made it a little bit harder to read. I’m a little bit freak on typography, so I will write a little bit on that. The overall typography of this book is quite nice, however there are some minor problems.

On page 46 and 60 there are listings that don’t fit the page width.

On pages 60-64 there are many results of database queries that are not placed on one page, instead only the header is on the first page, and the rest of data is on another.

On page 141 there is an example of a trigger procedure that is formatted really terribly, due to too long lines.

Page 246: this one is a really big error. Result of explain command is written on the bottom margin. The page number and bottom separator line is also written there. All is quite unreadable

Another repeating error is in index. The index at the beginning of the book is quite OK. I’ve got pdf version and there is another index, with the list of pages that appears in my pdf reader. The problem is that when there is a two line title, it appears as two separate positions in the index, e.g. page 63: “Which parameters are at non-default settings?”. There is one position in index titled: “Which parameters are at non-default”, and another one titled “settings?”. This is not a big problem, but it was a little bit confusing, as both positions point to the same page 63.

*Summary*

While reading some parts of the book I felt like I’d add there much more information, but later I thought that it would result in a 1k pages book with too much information. I think that everyone who wants to have much detailed information will be able to find it, and this book is a quite good start for that.

In my opinion this is a must-read book for anyone who seriously thinks about administering PostgreSQL, or wants to understand this great database a much better.