PostgreSQL Administration Essentials PDF
Preview PostgreSQL Administration Essentials
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 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:The list of books you might like
