Table Of ContentPostgreSQL 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
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 Development Editor
Dayan Hyames
Technical Editor
Mrunmayee Patil
Copy Editors
Sayanee Mukherjee Laxmi Subramanian Karuna Narayanan Project
Coordinator
Harshal Ved
Proofreaders
Maria Gould
Paul Hindle
Description:Discover efficient ways to administer, monitor, replicate, and handle your PostgreSQL databases About This BookLearn how to detect bottlenecks and make sure your database systems offer superior performance to your end usersReplicate your databases to achieve full redundancy and create backups quickl