PostgreSQL Administration Essentials

PostgreSQL Administration Essentials

by Hans-Jürgen Schönig
Released October 2014
Publisher(s): Packt Publishing
ISBN: 9781783988983

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Discover efficient ways to administer, monitor, replicate, and handle your PostgreSQL databases

In Detail

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.

What You Will Learn

  • Perform routine backups and restore your databases safely
  • Set up replication quickly and easily
  • Handle logfiles and extract information from PostgreSQL
  • Monitor PostgreSQL and integrate it into the existing infrastructure
  • Build desktop applications using C#
  • Optimize PostgreSQL for high speed and tune your database parameters
  • Adjust your user permissions and lock out intruders
  • Detect bottlenecks and find missing indexes with ease

Table of contents

  1. PostgreSQL Administration Essentials
    1. Table of Contents
    2. PostgreSQL Administration Essentials
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why subscribe?
      2. Free access for Packt account holders
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Errata
        2. Piracy
        3. Questions
    8. 1. Installing PostgreSQL
      1. Preparing your setup
        1. Understanding the PostgreSQL version numbers
          1. Choosing the right version
        2. Installing binary packages
          1. Installing PostgreSQL on Debian or Ubuntu
          2. Installing PostgreSQL on Red-Hat-based systems
        3. Compiling PostgreSQL from source
          1. How it works
          2. Installing the contrib packages
          3. Finalizing your installation
          4. Creating a database instance
          5. Firing up PostgreSQL
      2. Understanding the existing databases
      3. Creating databases
      4. Summary
    9. 2. Indexing and Performance Tuning
      1. Using simple binary trees
        1. Preparing the data
      2. Understanding the concept of execution plans
        1. Calculating costs
        2. Drawing important conclusions
        3. Creating indexes
        4. Analyzing the performance of a query
        5. The internal structure of a B-tree index
          1. Understanding the B-tree internals
          2. Providing a sorted order
        6. Combined indexes
        7. Partial indexes
      3. Dealing with different types of indexes
      4. Detecting missing indexes
      5. Detecting slow queries
        1. How to reset statistics
      6. Adjusting memory parameters
        1. Optimizing shared buffers
        2. Considering huge pages
        3. Tweaking work_mem
        4. Improving maintenance_work_mem
        5. Adjusting effective_cache_size
      7. Summary
    10. 3. Users and Permissions
      1. Understanding PostgreSQL security
        1. Configuring the TCP
        2. Managing network authentication
          1. Managing contradictions
          2. Authentication methods available
          3. Some more examples
          4. Handling SSL
          5. Changing pg_hba.conf
        3. Handling instance-level permissions
          1. Creating roles
          2. Modifying and dropping roles
        4. Controlling database-level permissions
        5. Understanding schema-level permissions
        6. Handling table-level permissions
        7. Managing column rights
        8. Improving security with SELinux
      2. Summary
    11. 4. Managing Logfiles
      1. Understanding the PostgreSQL log architecture
        1. Configuring log destinations
          1. Creating local logfiles
          2. Using syslog
          3. Configuring logs on Windows
          4. Performance considerations
      2. Configuring the amount of log output
        1. Making logs more readable
        2. Additional settings
      3. Making log creation more fine grained
        1. Logging selectively
        2. Focusing on slow queries
        3. Silencing notices
      4. Summary
    12. 5. Backup and Recovery
      1. Importing and exporting data
        1. Using the COPY command
          1. Basic operations of the COPY command
          2. Making use of pipes
      2. Performing backups
        1. Handling pg_dump
          1. More sophisticated dumping
          2. Performing partial replays
          3. Passing users and passwords
        2. Dumping an entire instance
          1. Understanding backups and user creation
      3. Summary
    13. 6. Handling Replication and Improving Performance
      1. Understanding the PostgreSQL transaction log
        1. The purpose of the transaction log
        2. Inspecting the size of the transaction log
      2. Configuring the checkpoints
        1. Optimizing the checkpoints
          1. Configuring the distance between checkpoints
          2. Controlling writes
      3. Setting up an asynchronous replication
        1. Obtaining a high-level overview
        2. Setting up replication step by step
          1. Preparing the slave
          2. Configuring the master
          3. Fetching an initial backup
          4. Creating and modifying the recovery.conf file
          5. Firing up the slave
          6. Turning slaves into masters
      4. Upgrading to synchronous replication
      5. Improving and monitoring the replication
        1. Keeping an eye on streaming
        2. Making things more robust
        3. Managing conflicts
      6. Handling point-in-time recovery
        1. Setting up PITR
        2. Replaying transaction logs
      7. Understanding timelines
        1. The importance of timelines
      8. Summary
    14. 7. Monitoring PostgreSQL
      1. Understanding the system statistics of PostgreSQL
        1. Checking out the pg_stat_activity file
        2. Monitoring databases
        3. Monitoring tables
        4. Monitoring indexes
        5. Checking out the information in the background writer
        6. Resetting statistics
      2. Integrating Nagios
      3. Handling Linux cgroups
        1. Setting up cgroups
      4. Summary
    15. Index

Product information

  • Title: PostgreSQL Administration Essentials
  • Author(s): Hans-Jürgen Schönig
  • Release date: October 2014
  • Publisher(s): Packt Publishing
  • ISBN: 9781783988983