PostgreSQL 9 Admin Cookbook

Simon Riggs, Hannu Krosing

Overview | Reviews | Author | Support | Sample Chapters

eBook: $29.99
Formats: PDF, PacktLib, ePub and Mobi formats

$25.49
save 15%!

Print + free eBook + free PacktLib access to the book: $79.98 Print cover: $49.99

$44.99
save 44%!

Packt
Anytime, Anywhere

Unlimited eBook downloads and up to 50% off the print version!

Free Shipping!

UK, US, Europe and selected countries in Asia.

Also available on:

Overview

Table of Contents

Author

Reviews

Support

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, Cookbooks, Open Source

Preface
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
Index

Preface

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

Index

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 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.

I think it is a really great source of information on administering PostgreSQL database and is a must-read book for anyone who wants to understand this great database much better. Read more

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. Read more

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.

Errata

- 2 submitted: last submission 06 Dec 2012

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:
ALTER ROLE BOB WITH CREATEUSER;

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".

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

50% Off
the eBooks

Buy both these recommended eBooks together and get 50% off the total price

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

Approach

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.