Enter your mobile number or email address below and we'll send you a link to download the free Kindle App. Then you can start reading Kindle books on your smartphone, tablet, or computer - no Kindle device required.
To get the free app, enter your mobile phone number.
PostgreSQL 9 Admin Cookbook Paperback – October 26, 2010
|New from||Used from|
Frequently bought together
Customers who viewed this item also viewed
Customers who bought this item also bought
About the Author
Simon Riggs is one of the few Major Developers and Committers on the PostgreSQL database project, and is also CTO of 2ndQuadrant, providing 24x7 support and services to PostgreSQL users worldwide. Simon has worked with enterprise-class database applications for more than 20 years, with prior certifications on Oracle, Teradata and DB2. Simon is responsible for much of the database recovery and replication code in PostgreSQL, and designed or wrote many of the latest performance enhancements. He uses his operational experience to contribute to many aspects of both internal design and usability. Hannu Krosing is Principal Consultant at 2ndQuadrant and a Technical Advisor at Ambient Sound Investments. As the original database architect at Skype Technologies, Hannu was responsible for designing the Skytools suite of replication and scalability technologies. Hannu has more than 12 years experience working with, and contributing to, the PostgreSQL project.
If you buy a new print edition of this book (or purchased one in the past), you can buy the Kindle edition for only $2.99 (Save 90%). Print edition purchase must be sold by Amazon. Learn more.
For thousands of qualifying books, your past, present, and future print-edition purchases now lets you buy the Kindle edition for $2.99 or less. (Textbooks available for $9.99 or less.)
Top customer reviews
There was a problem filtering reviews right now. Please try again later.
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:
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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.
10. 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.
11. 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.
12. Chapter 12: Replication and 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.
As the title suggests, authors Simon Riggs and Hannu Krosing have organized their book similarly to a cookbook, made up of simple "recipes" organized in subject groups. Each recipe covers a simple topic, such as "Connecting using SSL", "Adding/Removing tablespaces", and "Managing Hot Standby", with detail sufficient to guide a user from beginning to end. Of course in many of the more complex cases some amount of detail must be skipped, and in general this book probably won't provide its reader with an in depth education, but it will provide a framework to guide further research into a particular topic. It includes a description of the manuals, and locations of some of the mailing lists to get the researcher started.
I've used PostgreSQL for many different projects and been involved in the community for several years, so I didn't find anything in the book that was completely unfamiliar. But PostgreSQL is an open source project with a large community. There exists a wide array of tools, many of which I've never had occasion to use. Reading about some of them, and seeing examples in print, was a pleasant and educational experience. For instance, one recipe describes "Selective replication using Londiste". My tool of choice for such problems is generally Bucardo, so I'd not been exposed to Londiste's way of doing things. Nor have I used pgstatspack, a project for collecting various statistics and metrics from database views which is discussed under "Collecting regular statistics from pg_stat_* views".
In short, the book gave me the opportunity to look over the shoulder of experienced PostgreSQL users and administrators to see how they go about doing things, and compare to how I've done them. I'm glad to have had the opportunity.
The 'How to do it...' section of 'Backup database object definitions' is missing three pieces of example code, which should follow the colon in the three sentences beginning with
"If you want to dump PostgreSQL role definitions, you can use the following:"
If it's fixed I'll revise the rating.
Every recipe I read in that chapter was poorly explained. I won't read anymore, it's too aggravating.
I was not paid to review the book, nor did I receive a free copy, unfortunately, what a waste of money.
Most recent customer reviews
If you're a sysadmin who has had to deal with the basics of keeping PG running...PostgreSQL 9.0 High PerformanceRead more