PostgreSQL 9 Admin Cookbook

PostgreSQL 9 Admin Cookbook
eBook: $29.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 15%!
Print + free eBook + free PacktLib access to the book: $79.98    Print cover: $49.99
save 44%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • Administer and maintain a healthy database
  • Monitor your database ensuring that it performs as quickly as possible
  • Tips for backup and recovery of your database

Book Details

Language : English
Paperback : 360 pages [ 235mm x 191mm ]
Release Date : October 2010
ISBN : 1849510288
ISBN 13 : 9781849510288
Author(s) : Simon Riggs, Hannu Krosing
Topics and Technologies : All Books, Big Data and Business Intelligence, Cookbooks, Open Source

Table of Contents

Chapter 1: First Steps
Chapter 2: Exploring the Database
Chapter 3: Configuration
Chapter 4: Server Control
Chapter 5: Tables & Data
Chapter 6: Security
Chapter 7: Database Administration
Chapter 8: Monitoring and Diagnosis
Chapter 9: Regular Maintenance
Chapter 10: Performance & Concurrency
Chapter 11: Backup & Recovery
Chapter 12: Replication & Upgrades
  • Chapter 1: First Steps
    • Introduction
    • Introducing PostgreSQL 9
    • Getting PostgreSQL
    • Connecting to PostgreSQL server
    • Enabling access for network/remote users
    • Using graphical administration tools
    • Using psql query and scripting tool
    • Changing your password securely
    • Avoiding hardcoding your password
    • Using a connection service file
    • Troubleshooting a failed connection
  • Chapter 2: Exploring the Database
    • Introduction
    • What version is the server?
    • What is the server uptime?
    • Locate the database server files
    • Locate the database server message log
    • List databases on this database server?
    • How many tables in a database?
    • How much disk space does a database use?
    • How much disk space does a table use?
    • Which are my biggest tables?
    • How many rows in a table?
    • Quick estimate of the number of rows in a table
    • Understanding object dependencies
  • Chapter 3: Configuration
    • Introduction
    • Reading the Fine Manual (RTFM)
    • Planning a new database
    • Changing parameters in your programs
    • What are the current configuration settings?
    • Which parameters are at non-default settings?
    • Updating the parameter file
    • Setting parameters for particular groups of users
    • Basic server configuration checklist
    • Adding an external module to PostgreSQL
    • Running server in power saving mode
  • Chapter 4: Server Control
    • Introduction
    • Starting the database server manually
    • Stopping the server safely and quickly
    • Stopping the server in an emergency
    • Reloading the server configuration files
    • Restarting the server quickly
    • Preventing new connections
    • Restricting users to just one session each
    • Pushing users off the system
    • Deciding on a design for multi-tenancy
    • Using multiple schemas
    • Giving users their own private database
    • Running multiple servers on one system
    • Set up a Connection Pool
  • Chapter 5: Tables & Data
    • Introduction
    • Choosing good names for database objects
    • Handling objects with quoted names
    • Enforcing same name, same column definition
    • Identifying and removing duplicates
    • Preventing duplicate rows
    • Finding a unique key for a set of data
    • Generating test data
    • Randomly sampling data
    • Loading data from a spreadsheet
    • Loading data from flat files
  • Chapter 6: Security
    • Introduction
    • Revoking user access to a table
    • Granting user access to a table
    • Creating a new user
    • Temporarily preventing a user from connecting
    • Removing a user without dropping their data
    • Checking all users have a secure password
    • Giving limited superuser powers to specific users
    • Auditing DDL changes
    • Auditing data changes
    • Integrating with LDAP
    • Connecting using SSL
    • Encrypting sensitive data
  • Chapter 7: Database Administration
    • Introduction
    • Writing a script that either all succeeds or all fails
    • Writing a psql script that exits on first error
    • Performing actions on many tables
    • Adding/Removing the columns of a table
    • Changing datatype of a column
    • Adding/Removing schemas
    • Moving objects between schemas
    • Adding/Removing tablespaces
    • Moving objects between tablespaces
    • Accessing objects in other PostgreSQL databases
    • Making views updateable
  • Chapter 8: Monitoring and Diagnosis
    • Introduction
    • Is the user connected?
    • What are they running?
    • Are they active or blocked?
    • Who is blocking them?
    • Killing a specific session
    • Resolving an in-doubt prepared transaction
    • Is anybody using a specific table?
    • When did anybody last use it?
    • How much disk space is used by temporary data?
    • Why are my queries slowing down?
    • Investigating and reporting a bug
    • Producing a daily summary of logfile errors
  • Chapter 9: Regular Maintenance
    • Introduction
    • Controlling automatic database maintenance
    • Avoiding auto freezing and page corruptions
    • Avoiding transaction wraparound
    • Removing old prepared transactions
    • Actions for heavy users of temporary tables
    • Identifying and fixing bloated tables and indexes
    • Maintaining indexes
    • Finding the unused indexes
    • Carefully removing unwanted indexes
    • Planning maintenance
  • Chapter 10: Performance & Concurrency
    • Introduction
    • Finding slow SQL statements
    • Collecting regular statistics from pg_stat* views
    • Finding what makes SQL slow
    • Reducing the number of rows returned
    • Simplifying complex SQL
    • Speeding up queries without rewriting them
    • Why is my query not using an index?
    • How do I force a query to use an index
    • Using optimistic locking
    • Reporting performance problems
  • Chapter 11: Backup & Recovery
    • Introduction
    • Understanding and controlling crash recovery
    • Planning backups
    • Hot logical backup of one database
    • Hot logical backup of all databases
    • Hot logical backup of all tables in a tablespace
    • Backup of database object definitions
    • Standalone hot physical database backup
    • Hot physical backup & Continuous Archiving
    • Recovery of all databases
    • Recovery to a point in time
    • Recovery of a dropped/damaged table
    • Recovery of a dropped/damaged tablespace
    • Recovery of a dropped/damaged database
    • Improving performance of backup/restore
    • Incremental/Differential backup and restore
  • Chapter 12: Replication & Upgrades
    • Introduction
    • Understanding replication concepts
    • Replication best practices
    • File-based log-shipping replication
    • Setting up streaming log replication
    • Managing log shipping replication
    • Managing Hot Standby
    • Selective replication using Londiste
    • Selective replication using Slony 2.0
    • Load balancing with pgpool-II 3.0
    • Upgrading (minor)
    • Major upgrades in-place
    • Major upgrades online using replication tools

Simon Riggs

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

Hannu Krosing was a PostgreSQL user before it was rewritten to use SQL as its main query language in 1995. So, he has both the historic perspective of its development and almost 20 years of experience using it for solving various real-life problems. Hannu was the first Database Administrator and Database Architect at Skype, where he invented the sharding language PL/Proxy that allows scaling the user database to work with billions of users. Since leaving Skype at the end of 2006—about a year after it was bought up by eBay—Hannu has been working as a PostgreSQL consultant with 2ndQuadrant, the premier PostgreSQL consultancy with global reach and local presence in most of the world. Hannu has co-authored another Packt Publishing book, PostgreSQL 9 Administration Cookbook, together with one of the main PostgreSQL developers, Simon Riggs.

Code Downloads

Download the code and support files for this book.

Submit Errata

Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.


- 4 submitted: last submission 10 Feb 2014

Errata type: Code | Page numbers: 90

)The following statement (sudo pg_createcluster 9.0 > database2) is repeated. > > To create an additional data directory, run the following: > sudo pg_createcluster 9.0 database2 > > which can then be started using the following: > sudo pg_createcluster 9.0 database2 The proper start command should be sudo pg_ctlcluster 9.0 database2 start


Errata type: Typo | Page numbers: 136

To give the role bob the ability to create new users, run the following:

but it should be noted that the correct word is "CREATEROLE", not "CREATEUSER".


Errata type: Typo | Page numbers: 309

Under point 3: The sentence "Add the following line to the " is missing "Master node"
Under point 5: The configuration values erroneously lists "wal_mode", which should be "wal_level".


Errata type: Typo | Page numbers: 66

the line that start with "ALTER ROLE saas" should be "ALTER ROLE simon"


Errata type: Typo | Page numbers: 27

The line in the middle that goes like "you can use just onr or the other"  should probably be "you can use just one or the other"

Errata type: Typo | Page numbers: 309

Under point 3: The sentence "Add the following line to the " is missing "Master node" Under point 5: The configuration values erroneously lists "wal_mode", which should be "wal_level".


Errata type: Typo | Page numbers: 147

The line "There was also a nice presentacion named "Encrypted PostgreSQL" explaining these issues 

at pgcon2009."  should probably be "There was also a nice presentation named "Encrypted PostgreSQL" explaining these issues 

at pgcon2009."

Errata Typo: Code related | Page no: 278

It is: archive_command = 'test ! ../standalone/archiving_active || cp -i %p


It should be: archive_command = 'test ! -f ../standalone/archiving_active || cp -i %p 


Sample chapters

You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

Frequently bought together

PostgreSQL 9 Admin Cookbook +    ElasticSearch Cookbook =
50% Off
the second eBook
Price for both: €33.25

Buy both these recommended eBooks together and get 50% off the cheapest eBook.

What you will learn from this book

  • How to implement PostgreSQL features for reliability and performance
  • How to use the latest PostgreSQL 9.0 features
  • Summaries to help DBAs with experience of other database systems
  • Advanced technical tips and scripts for experienced users
  • Best practice for planning and designing live databases
  • How and when to VACUUM your databases
  • Selecting and implementing robust backup techniques
  • Concise and clear guidance on replication and high availability

In Detail

PostgreSQL is a powerful, open source object-relational database system. An enterprise database, PostgreSQL includes features such as Multi-Version Concurrency Control (MVCC), point-in-time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write-ahead logging for fault tolerance. PostgreSQL 9 Admin cookbook covers everything a database administrator needs to protect, manage and run a healthy and efficient PostgreSQL 9.0 database.

PostgreSQL 9 Admin Cookbook describes key aspects of the PostgreSQL open source database system. The book covers everything a sysadmin or DBA needs to protect, manage, and run a healthy and efficient PostgreSQL 9 database. This hands-on guide will assist developers working on live databases, supporting web or enterprise software applications using Java, Python, Ruby, or .Net from any development framework. It's easy to manage your database when you've got PostgreSQL 9 Admin Cookbook to hand.

PostgreSQL is fast becoming one of the world's most popular server databases with an enviable reputation for performance, stability, and an enormous range of advanced features. PostgreSQL is one of the oldest open source projects, completely free to use and developed by a very diverse worldwide community. Most of all, It Just Works!

PostgreSQL 9 Admin Cookbook offers the information you need to manage your live production databases on PostgreSQL. The book contains insights direct from the main author of the PostgreSQL replication and recovery features, and the database architect of the most successful startup using PostgreSQL, Skype.

This practical guide gives quick answers to common questions and problems, building on the authors' experience as trainers, users, and core developers of the PostgreSQL database server.

Each technical aspect is broken down into short recipes that demonstrate solutions with working code then explain why and how that works. The book is intended to be a desk reference for both new users and technical experts.

The book covers all the latest features in PostgreSQL 9. Soon you will be running a smooth database with ease!

A practical guide, this cookbook will ensure you run a smooth PostgreSQL database


Written in the cookbook style, this book offers learning and techniques through recipes. It contains step-by-step instructions for administrators and developers to manage databases on PostgreSQL. The book is designed in such a way that you can read it chapter by chapter or refer to recipes in no particular order.

Who this book is for

This book is for Sysadmins, Database Administrators, Architects, Developers, and anyone with an interest in planning for or running live production databases. This book is most suited to those who have some technical experience.

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software