[go: up one dir, main page]
Everand Logo

Welcome to Everand!

  • Discover millions of ebooks, audiobooks, and so much more with a free trial

    From $11.99/month after trial. Cancel anytime.

    PostgreSQL 9 Administration Cookbook: LITE Edition
    PostgreSQL 9 Administration Cookbook: LITE Edition
    PostgreSQL 9 Administration Cookbook: LITE Edition
    Ebook270 pages1 hour

    PostgreSQL 9 Administration Cookbook: LITE Edition

    By Simon Riggs

    3/5

    ()

    Read preview

    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.
    LanguageEnglish
    PublisherPackt Publishing
    Release dateMay 19, 2011
    ISBN9781849516211
    PostgreSQL 9 Administration Cookbook: LITE Edition

    Read more from Simon Riggs

    Related to PostgreSQL 9 Administration Cookbook

    Related ebooks

    Information Technology For You

    View More
    View More

    Related podcast episodes

    Related categories

    Reviews for PostgreSQL 9 Administration Cookbook

    3/5

    1 rating0 reviews

    What did you think?

    Tap to rate

    Review must be at least 10 words

      Book preview

      PostgreSQL 9 Administration Cookbook - Simon Riggs

      Table of Contents

      PostgreSQL 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

      Errata

      Piracy

      Questions

      1. First Steps

      Introduction

      Introducing PostgreSQL 9

      What makes PostgreSQL different?

      Getting PostgreSQL

      How to do it...

      How it works...

      There's more...

      Connecting to PostgreSQL server

      Getting ready

      How to do it...

      How it works...

      Inspecting your connection information

      See also

      Enabling access for network/remote users

      How to do it...

      How it works...

      There's more...

      See also

      Using graphical administration tools

      How to do it...

      How it works...

      There's more...

      See also

      Using psql query and scripting tool

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Changing your password securely

      How to do it...

      How it works...

      Avoiding hardcoding your password

      Getting ready

      How to do it...

      How it works...

      There's more...

      Using a connection service file

      How to do it...

      How it works...

      Troubleshooting a failed connection

      How to do it...

      There's more...

      2. Exploring the Database

      Introduction

      What version is the server?

      How to do it...

      How it works...

      There's more...

      What is the server uptime?

      How to do it...

      How it works...

      Locate the database server files

      Getting ready

      How to do it...

      How it works...

      There's more...

      Locate the database server message log

      Getting ready

      How to do it...

      How it works...

      Getting ready

      How to do it...

      How it works...

      There's more...

      List databases on this database server?

      How to do it...

      How it works...

      There's more...

      How many tables in a database?

      How to do it...

      How it works...

      There's more...

      How much disk space does a database use?

      How to do it...

      How it works...

      How much disk space does a table use?

      How to do it...

      How it works...

      There's more...

      Which are my biggest tables?

      How to do it...

      How many rows in a table?

      How to do it...

      How it works...

      Quick estimate of the number of rows in a table

      How to do it...

      How it works...

      There's more...

      Function 1

      Function 2

      Understanding object dependencies

      Getting ready

      How to do it...

      How it works...

      There's more...

      3. Database Administration

      Introduction

      Writing a script that either all succeeds or all fails

      How to do it...

      How it works...

      There's more...

      Writing a psql script that exits on first error

      Getting ready

      How to do it...

      How it works...

      There's more...

      Performing actions on many tables

      Getting ready

      How to do it...

      How it works...

      There's more...

      Using pg_batch to run tasks in parallel

      Adding/Removing the columns of a table

      How to do it...

      How it works...

      There's more...

      Changing datatype of a column

      Getting ready

      How to do it...

      How it works...

      There's more...

      Adding/Removing schemas

      How to do it...

      There's more...

      Schema-level privileges

      Moving objects between schemas

      How to do it...

      How it works...

      There's more...

      Adding/Removing tablespaces

      Getting ready

      How to do it...

      How it works...

      There's more...

      Putting pg_xlog on a separate device

      Tablespace-level tuning

      Moving objects between tablespaces

      Getting ready

      How to do it...

      How it works...

      There's more...

      Accessing objects in other PostgreSQL databases

      Getting ready

      How to do it...

      How it works...

      There's more...

      See also

      Making views updateable

      Getting ready

      How to do it...

      How it works...

      There's more...

      Index

      PostgreSQL Administration Cookbook: LITE

      PostgreSQL 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-20-4

      www.packtpub.com

      Cover Image by John M. Quick ( <john.m.quick@gmail.com> )

      Credits

      Authors

      Simon Riggs

      Hannu Krosing

      Reviewers

      Gabriele Bartolini

      Dimitri Fontaine

      Acquisition Editor

      Sarah Cullington

      Technical Editor

      Hithesh Uchil

      Indexer

      Hemangini Bari

      Production Coordinator

      Kruthika Bangera

      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 roles in the banking, telecommunications, and software industries. Simon's early research work has been published by the Royal Society.

      Hannu Krosing is a 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.

      About the Reviewers

      Gabriele Bartolini is a long time open-source programmer, writing Linux/Unix applications in C and C++ for over 10 years, specializing in search engines and web analytics with large databases.

      Gabriele has a degree in Statistics from the University of Florence. His areas of expertise are data mining and data warehousing, having worked on web traffic analysis in Australia and Italy.

      Gabriele is a consultant with 2ndQuadrant and an active member of the international PostgreSQL community.

      Gabriele currently lives in Prato, a small but vibrant city located in the northern part of Tuscany, Italy. His second home is Melbourne, Australia, where he has studied at Monash University and worked in the ICT sector.

      His hobbies include calcio (football or soccer, depending on which part of the world you come from) and playing his Fender Stratocaster electric guitar.

      Thanks to my family, in particular Cathy who encourages me by saying there is always something new to learn.

      Dimitri Fontaine is part of the PostgreSQL community and has been contributing to open source for more than 10 years. He is the lead developer of enterprise ready solutions such as pgloader (ETL), PostgreSQL prefix indexing (telephony routing), pg_staging (development environment maintenance from production backups), preprepare (allow easy usage of prepare statements behind a connection pooler), and some backports of recent PostgreSQL features

      He also contributed to Skytools and the Londiste replication system and, authored a PHP layer for the PGQ event handler, allowing for robust asynchronous processing and code reuse.

      Dimitri's passion is system architecture & design, with the goals of reduced maintenance time and very high availability

      Professional experience, community involvement, and PostgreSQL expertise have led Dimitri to now work as a principal consultant at 2ndQuadrant, France.

      Preface

      PostgreSQL is an advanced SQL

      Enjoying the preview?
      Page 1 of 1