PostgreSQL Administration Essentials

Hans-Jurgen Schonig

• Publisher: Packt Publishing
• Year: 2014
• ISBN:
  9781783988983
• Edition: 1
• Topic: Informatica
• Subtopic: Reti di computer

With more than a quarter of a century of development and innovation, PostgreSQL has gained a reputation for stability, robustness, and ease of use. The variety of features available within PostgreSQL makes it a powerful tool for your professional relational database infrastructure.

With the database administrator in mind, this compact, easy-to-read, and practically-oriented book will make your daily handling of PostgreSQL more efficient and rewarding. You will learn about optimization, bottleneck detection, backup and recovery, replication, logfile management, indexing, and much more in a systematic and carefully structured manner. This invaluable guide will enable you to develop the fundamental skills required to deploy PostgreSQL in order to efficiently manage your databases.

PostgreSQL Administration Essentials

---

Table of Contents

PostgreSQL Administration Essentials

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. Installing PostgreSQL

Preparing your setup

Understanding the PostgreSQL version numbers

Choosing the right version

Installing binary packages

Installing PostgreSQL on Debian or Ubuntu

Installing PostgreSQL on Red-Hat-based systems

Compiling PostgreSQL from source

How it works

Installing the contrib packages

Finalizing your installation

Creating a database instance

Firing up PostgreSQL

Understanding the existing databases

Creating databases

Summary

2. Indexing and Performance Tuning

Using simple binary trees

Preparing the data

Understanding the concept of execution plans

Calculating costs

Drawing important conclusions

Creating indexes

Analyzing the performance of a query

The internal structure of a B-tree index

Understanding the B-tree internals

Providing a sorted order

Combined indexes

Partial indexes

Dealing with different types of indexes

Detecting missing indexes

Detecting slow queries

How to reset statistics

Adjusting memory parameters

Optimizing shared buffers

Considering huge pages

Tweaking work_mem

Improving maintenance_work_mem

Adjusting effective_cache_size

Summary

3. Users and Permissions

Understanding PostgreSQL security

Configuring the TCP

Managing network authentication

Managing contradictions

Authentication methods available

Some more examples

Handling SSL

Changing pg_hba.conf

Handling instance-level permissions

Creating roles

Modifying and dropping roles

Controlling database-level permissions

Understanding schema-level permissions

Handling table-level permissions

Managing column rights

Improving security with SELinux

Summary

4. Managing Logfiles

Understanding the PostgreSQL log architecture

Configuring log destinations

Creating local logfiles

Using syslog

Configuring logs on Windows

Performance considerations

Configuring the amount of log output

Making logs more readable

Additional settings

Making log creation more fine grained

Logging selectively

Focusing on slow queries

Silencing notices

Summary

5. Backup and Recovery

Importing and exporting data

Using the COPY command

Basic operations of the COPY command

Making use of pipes

Performing backups

Handling pg_dump

More sophisticated dumping

Performing partial replays

Passing users and passwords

Dumping an entire instance

Understanding backups and user creation

Summary

6. Handling Replication and Improving Performance

Understanding the PostgreSQL transaction log

The purpose of the transaction log

Inspecting the size of the transaction log

Configuring the checkpoints

Optimizing the checkpoints

Configuring the distance between checkpoints

Controlling writes

Setting up an asynchronous replication

Obtaining a high-level overview

Setting up replication step by step

Preparing the slave

Configuring the master

Fetching an initial backup

Creating and modifying the recovery.conf file

Firing up the slave

Turning slaves into masters

Upgrading to synchronous replication

Improving and monitoring the replication

Keeping an eye on streaming

Making things more robust

Managing conflicts

Handling point-in-time recovery

Setting up PITR

Replaying transaction logs

Understanding timelines

The importance of timelines

Summary

7. Monitoring PostgreSQL

Understanding the system statistics of PostgreSQL

Checking out the pg_stat_activity file

Monitoring databases

Monitoring tables

Monitoring indexes

Checking out the information in the background writer

Resetting statistics

Integrating Nagios

Handling Linux cgroups

Setting up cgroups

Summary

Index

PostgreSQL Administration Essentials

Copyright © 2014 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: October 2014

Production reference: 1081014

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78398-898-3

www.packtpub.com

Credits

Author

Hans-Jürgen Schönig

Reviewers

Debasis Roy

Sheldon E. Strauch

Steve Perkins

Vasilis Ventirozos

Commissioning Editor

Julian Ursell

Acquisition Editor

Greg Wild

Content Dev...