Rating: 3 out of 5 stars
3/5
Ebook269 pages1 hour
PostgreSQL 9 Administration Cookbook LITE: Configuration, Monitoring and Maintenance
By Simon Riggs
Rating: 3 out of 5 stars
3/5
()
About this ebook
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 in 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. This book is for Sysadmins, Database Administrators, Architects, Developers, and anyone with an interest in planning for or running live production databases. The book assumes that you are familiar with the basic operation of PostgreSQL.
LanguageEnglish
PublisherPackt Publishing
Release dateMay 19, 2011
ISBN9781849516433
Read more from Simon Riggs
PostgreSQL 9 Administration Cookbook: LITE Edition PostgreSQL 11 Administration Cookbook: Over 175 recipes for database administrators to manage enterprise databases Rating: 0 out of 5 stars0 ratings
Related to PostgreSQL 9 Administration Cookbook LITE
Related ebooks
PostgreSQL 9.0 High Performance Rating: 4 out of 5 stars4/5Instant PostgreSQL Backup and Restore How-to Rating: 0 out of 5 stars0 ratingsPostgreSQL Development Essentials Rating: 5 out of 5 stars5/5Mastering PostgreSQL: A Comprehensive Guide for Developers Rating: 0 out of 5 stars0 ratingsNoSQL Essentials: Navigating the World of Non-Relational Databases Rating: 0 out of 5 stars0 ratingsPostgreSQL for Data Architects Rating: 3 out of 5 stars3/5PostgreSQL Administration Essentials Rating: 0 out of 5 stars0 ratingsMariaDB Cookbook Rating: 0 out of 5 stars0 ratingsTroubleshooting PostgreSQL Rating: 5 out of 5 stars5/5MariaDb Essentials: Quickly get up to speed with MariaDB—the leading, drop-in replacement for MySQL, through this practical tutorial Rating: 0 out of 5 stars0 ratingsLearn DBMS in 24 Hours Rating: 0 out of 5 stars0 ratingsPostgreSQL Server Programming Rating: 0 out of 5 stars0 ratingsMySQL Admin Cookbook LITE: Replication and Indexing Rating: 4 out of 5 stars4/5PostgreSQL 9 High Availability Cookbook Rating: 5 out of 5 stars5/5PostgreSQL for Jobseekers: Introduction to PostgreSQL administration for modern DBAs (English Edition) Rating: 0 out of 5 stars0 ratingsPostgreSQL Server Programming - Second Edition Rating: 0 out of 5 stars0 ratingsMastering PostgreSQL: From Basics to Expert Proficiency Rating: 0 out of 5 stars0 ratingsSQL Made Easy: Tips and Tricks to Mastering SQL Programming Rating: 0 out of 5 stars0 ratingsOracle Database Mastery: Comprehensive Techniques for Advanced Application Rating: 0 out of 5 stars0 ratingsOracle Database 12c Install, Configure & Maintain Like a Professional: Install, Configure & Maintain Like a Professional Rating: 0 out of 5 stars0 ratingsSQL Tutorial For Beginners Rating: 0 out of 5 stars0 ratingsGETTING STARTED WITH SQL: Exercises with PhpMyAdmin and MySQL Rating: 0 out of 5 stars0 ratingsPostgreSQL Replication - Second Edition Rating: 0 out of 5 stars0 ratingsApache Hive Cookbook Rating: 0 out of 5 stars0 ratingsMariaDB High Performance Rating: 0 out of 5 stars0 ratingsAlfresco 3 Cookbook Rating: 0 out of 5 stars0 ratings
Information Technology For You
Data Analytics for Beginners: Introduction to Data Analytics Rating: 4 out of 5 stars4/5Creating Online Courses with ChatGPT | A Step-by-Step Guide with Prompt Templates Rating: 4 out of 5 stars4/5CompTia Security 701: Fundamentals of Security Rating: 0 out of 5 stars0 ratingsCOMPUTER SCIENCE FOR ROOKIES Rating: 0 out of 5 stars0 ratingsAn Ultimate Guide to Kali Linux for Beginners Rating: 3 out of 5 stars3/5ChatGPT: The Future of Intelligent Conversation Rating: 4 out of 5 stars4/5CompTIA A+ CertMike: Prepare. Practice. Pass the Test! Get Certified!: Core 1 Exam 220-1101 Rating: 0 out of 5 stars0 ratingsHow to Write Effective Emails at Work Rating: 4 out of 5 stars4/5Personal Knowledge Graphs: Connected thinking to boost productivity, creativity and discovery Rating: 5 out of 5 stars5/5A Mind at Play: How Claude Shannon Invented the Information Age Rating: 4 out of 5 stars4/5Introduction to Information Systems: Information Technology Essentials, #1 Rating: 0 out of 5 stars0 ratingsAWS Certified Cloud Practitioner: Study Guide with Practice Questions and Labs Rating: 5 out of 5 stars5/5CompTIA ITF+ CertMike: Prepare. Practice. Pass the Test! Get Certified!: Exam FC0-U61 Rating: 5 out of 5 stars5/5Design and Build Modern Datacentres, A to Z practical guide Rating: 3 out of 5 stars3/5Unlocking the Power of Agentic AI: Transforming Work and Life Rating: 5 out of 5 stars5/5Cybersecurity Playbook for Executives Rating: 0 out of 5 stars0 ratingsCompTIA Network+ CertMike: Prepare. Practice. Pass the Test! Get Certified!: Exam N10-008 Rating: 0 out of 5 stars0 ratingsA Practical Guide for IoT Solution Architects Rating: 5 out of 5 stars5/5DevOps Handbook: What is DevOps, Why You Need it and How to Transform Your Business with DevOps Practices Rating: 4 out of 5 stars4/5The TOGAF® Standard, 10th Edition - A Pocket Guide Rating: 0 out of 5 stars0 ratingsThe Rise of AI Income: Using Artificial Intelligence for Financial Success Rating: 5 out of 5 stars5/5Ultimate Microsoft Power Automate Desktop Rating: 0 out of 5 stars0 ratingsA pocket companion to PMI's PMBOK Guide Fifth edition Rating: 0 out of 5 stars0 ratingsCybersecurity for Beginners : Learn the Fundamentals of Cybersecurity in an Easy, Step-by-Step Guide: 1 Rating: 0 out of 5 stars0 ratingsAWS Certified Cloud Practitioner - Practice Paper 3: AWS Certified Cloud Practitioner, #3 Rating: 5 out of 5 stars5/5REMOTE ACCESS VPN- SSL VPN: A deep dive into SSL VPN from basic Rating: 5 out of 5 stars5/5AWS Certified Cloud Practitioner - Practice Paper 1: AWS Certified Cloud Practitioner, #1 Rating: 5 out of 5 stars5/5CompTIA A+ CertMike: Prepare. Practice. Pass the Test! Get Certified!: Core 2 Exam 220-1102 Rating: 0 out of 5 stars0 ratings
Reviews for PostgreSQL 9 Administration Cookbook LITE
Rating: 3 out of 5 stars
3/5
1 rating0 reviews
Book preview
PostgreSQL 9 Administration Cookbook LITE - Simon Riggs
(missing alt)
Table of Contents
PostgreSQL 9 Administration Cookbook: LITE
Credits
About the Authors
About the Reviewers
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example code for this book
Errata
Piracy
Questions
1. Configuration
Introduction
Reading the Fine Manual (RTFM)
How to do it...
How it works...
There's more...
Planning a new database
Getting ready
How to do it...
How it works...
There's more...
Changing parameters in your programs
How to do it...
How it works...
There's more...
What are the current configuration settings?
How to do it...
How it works...
Which parameters are at non-default settings?
How to do it...
How it works...
There's more...
Who set that?
Updating the parameter file
Getting ready
How to do it...
How it works...
There's more...
Setting parameters for particular groups of users
How to do it...
How it works...
Basic server configuration checklist
Getting ready
How to do it...
Adding an external module to PostgreSQL
Getting ready
How to do it...
How it works...
Running server in power saving mode
Getting ready
How to do it...
How it works...
2. Monitoring and Diagnosis
Introduction
Monitoring is important
Providing PostgreSQL information to monitoring tools
Where to find more information about generic monitoring tools
Realtime view using pgAdmin
Is the user connected?
Getting ready
How to do it...
How it works...
There's more...
What if I want to know is that computer connected?
What are they running?
Getting ready
How to do it...
How it works...
There's more...
How to catch queries which runs only for a few milliseconds
How to watch longest queries
Watching queries from ps
See also
Are they active or blocked?
Getting ready
How to do it...
How it works...
There's more...
No need for = true
This shows only queries waiting on locks
Who is blocking them?
Getting ready
How to do it...
How it works...
Killing a specific session
Getting ready
How to do it...
How it works...
There's more...
Try to cancel the query first
If the backend won't terminate
Use statement timeout to clean up queries which take too long
Killing Idle in transaction
queries
You can also kill the backend from command line
Resolving an in-doubt prepared transaction
Is anybody using a specific table?
Getting ready
How to do it...
How it works...
There's more...
The quick and dirty way
Collecting daily usage statistics
When did anybody last use it?
Getting ready
How to do it...
Looking at file dates
How it works...
There's more...
There may be last-use information in future version of PostgreSQL
How much disk space is used by temporary data?
Getting ready
How to do it...
When temp_tablespaces has one or more tablespaces
When temp_tablespaces is empty
How it works...
There's more...
Finding out if temporary file is in use any more
Logging temporary file usage
Why are my queries slowing down?
Getting ready
How to do it...
How it works...
There's more...
Do the queries return significantly more data than earlier?
Do the queries also run slowly when run alone?
Is the second run of same query also slow?
See also
Investigating and reporting a bug
Getting ready
How to do it...
How it works...
See also
The official PostgreSQL bug/problem reporting guides
Producing a daily summary of logfile errors
Getting ready
How to do it...
How it works...
There's more...
Writing your own log processor
See also
3. Regular Maintenance
Introduction
Controlling automatic database maintenance
Getting ready
How to do it...
How it works...
There's more...
See also
Avoiding auto freezing and page corruptions
Getting ready
How to do it...
Avoiding transaction wraparound
Getting ready
How to do it...
How it works...
There's more...
See also
Removing old prepared transactions
Getting ready
How to do it...
How it works...
There's more...
Actions for heavy users of temporary tables
How to do it...
How it works...
Identifying and fixing bloated tables and indexes
How to do it...
How it works...
There's more...
Maintaining indexes
Getting ready
How to do it...
How it works...
There's more...
See also
Locating the unused indexes
How to do it...
How it works...
See also
Carefully removing unwanted indexes
How to do it...
How it works...
Planning maintenance
How to do it...
How it works...
Index
PostgreSQL 9 Administration Cookbook: LITE
PostgreSQL 9 Administration Cookbook: LITE
Copyright © 2011 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 authors, 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: April 2011
Production Reference: 1130411
Published by Packt Publishing Ltd. 32 Lincoln Road Olton Birmingham, B27 6PA, UK.
ISBN 978-1-849516-42-6
www.packtpub.com
Cover Image by John M. Quick (<[email protected]>)
Credits
Authors
Simon Riggs
Hannu Krosing
Reviewers
Gabriele Bartolini
Dimitri Fontaine
Acquisition Editor
Sarah Cullington
Technical Editor
Prashant Macha
Indexer
Hemangini Bari
Production Coordinator
Adline Swetha Jesuthas
Cover Work
Kruthika Bangera
About the Authors
Simon Riggs is a major developer and one of the few committers on the PostgreSQL database project, as well as CTO of 2ndQuadrant, providing 24x7 support and services to PostgreSQL users worldwide.
Simon works actively as a database architect and support troubleshooter, skills which drive and shape his contributions to the development of operational features for PostgreSQL. Feature credits include Point in Time Recovery, Warm Standby replication, Hot Standby, Asynchronous Commit, Partitioning and many other performance and tuning features. His designs and solutions can be found throughout the PostgreSQL code and documentation.
Simon has also previously worked with Oracle, Teradata, and DB2 and holds multiple certifications. His previous experience covers management and senior technical
Enjoying the preview?
Page 1 of 1