You are previewing PostgreSQL Cookbook.
O'Reilly logo
PostgreSQL Cookbook

Book Description

Over 90 hands-on recipes to effectively manage, administer, and design solutions using PostgreSQL

In Detail

PostgreSQL is an open source database management system. It is used for a wide variety of development practices such as software and web design, as well as for handling large datasets (big data).

With the goal of teaching you the skills to master PostgreSQL, the book begins by giving you a glimpse of the unique features of PostgreSQL and how to utilize them to solve real-world problems. With the aid of practical examples, the book will then show you how to create and manage databases. You will learn how to secure PostgreSQL, perform administration and maintenance tasks, implement high availability features, and provide replication. The book will conclude by teaching you how to migrate information from other databases to PostgreSQL.

What You Will Learn

  • Perform regular maintenance tasks to keep your database steady and achieve optimal performance

  • Design and implement various high availability and replication features to provide redundancy, fault tolerance, and failover

  • Diagnose and troubleshoot CPU, memory, and I/O related database performance issues

  • Perform database operations using languages such as Perl and Python

  • Discover the different backup and recovery strategies that can be implemented in PostgreSQL

  • Implement connection pooling methods to achieve load balancing

  • Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

    Table of Contents

    1. PostgreSQL Cookbook
      1. Table of Contents
      2. PostgreSQL Cookbook
      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. Sections
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
          5. See also
        5. Conventions
        6. Reader feedback
        7. Customer support
          1. Downloading the example code
          2. Errata
          3. Piracy
          4. Questions
      8. 1. Managing Databases and the PostgreSQL Server
        1. Introduction
        2. Creating databases
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Creating schemas
          1. How to do it...
          2. How it works...
          3. There's more...
        4. Creating users
          1. How to do it...
          2. How it works...
          3. There's more...
        5. Creating groups
          1. How to do it...
          2. How it works...
          3. There's more...
        6. Destroying databases
          1. How to do it...
          2. How it works...
          3. There's more...
        7. Creating and dropping tablespaces
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        8. Moving objects between tablespaces
          1. Getting ready
          2. How to do it...
          3. How it works...
        9. Initializing a database cluster
          1. How to do it...
          2. How it works...
        10. Starting the server
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        11. Stopping the server
          1. How to do it...
          2. How it works...
          3. There's more...
        12. Displaying the server status
          1. How to do it...
          2. How it works...
        13. Reloading the server configuration files
          1. How to do it...
          2. How it works...
        14. Terminating connections
          1. How to do it...
          2. How it works...
          3. There's more...
      9. 2. Controlling Security
        1. Introduction
        2. Securing database objects
          1. How to do it...
          2. How it works...
        3. Controlling access via firewalls
          1. How to do it...
          2. How it works...
        4. Controlling access via configuration files
          1. How to do it...
          2. How it works...
          3. There's more…
        5. Testing remote connectivity
          1. How to do it...
          2. How it works...
        6. Auditing database changes
          1. How to do it...
          2. How it works...
          3. There's more...
        7. Enabling SSL in PostgreSQL
          1. How to do it...
          2. How it works...
          3. There's more...
        8. Testing SSL encryption
          1. How to do it...
          2. How it works...
        9. Encrypting confidential data
          1. How to do it...
          2. How it works...
          3. There's more...
        10. Cracking PostgreSQL passwords
          1. How to do it...
          2. How it works...
      10. 3. Backup and Recovery
        1. Introduction
        2. A logical backup of a single PostgreSQL database
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. A logical backup of all PostgreSQL databases
          1. How to do it...
          2. How it works...
          3. There's more...
        4. A logical backup of specific objects
          1. How to do it...
          2. How it works...
        5. File system level backup
          1. How to do it...
          2. How it works...
          3. There's more...
        6. Taking a base backup
          1. How to do it...
          2. How it works...
        7. Hot physical backup and continuous archiving
          1. How to do it...
          2. How it works...
        8. Point-in-time recovery
          1. How to do it...
          2. How it works...
          3. There's more...
        9. Restoring databases and specific database objects
          1. How to do it...
          2. How it works...
          3. There's more...
      11. 4. Routine Maintenance Tasks
        1. Introduction
        2. Controlling automatic database maintenance
          1. How to do it...
          2. How it works...
          3. There's more...
        3. Preventing auto freeze and page corruption
          1. How to do it...
          2. How it works...
        4. Preventing transaction ID wraparound failures
          1. How to do it...
        5. Updating planner statistics
          1. How to do it...
          2. How it works...
        6. Dealing with bloating tables and indexes
          1. How to do it...
          2. How it works...
          3. There's more...
        7. Monitoring data and index pages
          1. How to do it...
        8. Routine reindexing
          1. How to do it...
          2. How it works...
          3. There's more...
        9. Maintaining log files
          1. How to do it...
      12. 5. Monitoring the System Using Unix Utilities
        1. Introduction
        2. Monitoring CPU usage
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Monitoring paging and swapping
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Finding the worst user on the system
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Monitoring system load
          1. How to do it...
          2. How it works...
        6. Identifying CPU bottlenecks
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Identifying disk I/O bottlenecks
          1. Getting ready
          2. How to do it...
          3. How it works...
        8. Monitoring system performance
          1. Getting ready
          2. How to do it...
          3. How it works...
        9. Examining historical CPU load
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        10. Examining historical memory load
          1. Getting ready
          2. How to do it...
          3. How it works...
        11. Monitoring disk space usage
          1. Getting ready
          2. How to do it...
          3. How it works...
        12. Monitoring network status
          1. Getting ready
          2. How to do it...
          3. How it works...
      13. 6. Monitoring Database Activity and Investigating Performance Issues
        1. Introduction
        2. Checking active sessions
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        3. Finding out what queries users are currently running
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Getting the execution plan for a statement
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Logging slow statements
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Collecting statistics
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Monitoring database load
          1. Getting ready
          2. How to do it...
          3. How it works...
        8. Finding blocking sessions
          1. Getting ready
          2. How to do it...
          3. How it works...
        9. Table access statistics
          1. Getting ready
          2. How to do it...
          3. How it works...
        10. Finding unused indexes
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more…
        11. Forcing a query to use an index
          1. Getting ready
          2. How to do it...
          3. How it works...
        12. Determining disk usage
          1. How to do it...
          2. How it works...
          3. There's more...
      14. 7. High Availability and Replication
        1. Introduction
        2. Setting up hot streaming replication
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Replication using Slony-I
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        4. Replication using Londiste
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Replication using Bucardo
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Replication using DRBD
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Setting up the Postgres-XC cluster
          1. Getting ready
          2. How to do it...
          3. How it works...
      15. 8. Connection Pooling
        1. Introduction
        2. Installing pgpool
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Configuring pgpool and testing the setup
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Starting and stopping pgpool
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Setting up pgbouncer
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Connection pooling using pgbouncer
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        7. Managing pgbouncer
          1. Getting ready
          2. How to do it...
          3. How it works...
      16. 9. Table Partitioning
        1. Introduction
        2. Implementing partitioning
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more
        3. Managing partitions
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more
        4. Partitioning and constraint exclusion
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Alternate partitioning methods
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Installing PL/Proxy
          1. Getting Ready
          2. How to do it...
          3. How it works...
        7. Partitioning with PL/Proxy
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more
      17. 10. Accessing PostgreSQL from Perl
        1. Introduction
        2. Making a connection to a PostgreSQL database using Perl
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Creating tables using Perl
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Inserting records using Perl
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Accessing table data using Perl
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Updating records using Perl
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Deleting records using Perl
          1. Getting ready
          2. How to do it...
          3. How it works...
      18. 11. Accessing PostgreSQL from Python
        1. Introduction
        2. Making connections to a PostgreSQL database using Python
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Creating tables using Python
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Inserting records using Python
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Accessing table data using Python
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Updating records using Python
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Deleting records using Python
          1. Getting ready
          2. How to do it...
          3. How it works...
      19. 12. Data Migration from Other Databases and Upgrading the PostgreSQL Cluster
        1. Introduction
        2. Using pg_dump to upgrade data
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Using the pg_upgrade utility for a version upgrade
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Replicating data from other databases to PostgreSQL using GoldenGate
          1. Getting ready
          2. How to do it...
          3. How it works...
      20. Index