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

Book Description

By working through the recipes in this book, you can soon be running a more efficient database. Bring your PostreSQL know-how up to date in record time with a hands-on guide that covers all the vital techniques.

  • Administer and maintain a healthy database

  • Monitor your database ensuring that it performs as quickly as possible

  • Tips for backup and recovery of your database

  • In Detail

    PostgreSQL is a powerful, open source object-relational database system. An enterprise database, PostgreSQL includes features such as Multi-Version Concurrency Control (MVCC), point-in-time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write-ahead logging for fault tolerance. PostgreSQL 9 Admin cookbook covers everything a database administrator needs to protect, manage and run a healthy and efficient PostgreSQL 9.0 database.

    PostgreSQL 9 Admin Cookbook describes key aspects of the PostgreSQL open source database system. The book covers everything a sysadmin or DBA needs to protect, manage, and run a healthy and efficient PostgreSQL 9 database. This hands-on guide will assist developers working on live databases, supporting web or enterprise software applications using Java, Python, Ruby, or .Net from any development framework. It's easy to manage your database when you've got PostgreSQL 9 Admin Cookbook to hand.

    PostgreSQL is fast becoming one of the world's most popular server databases with an enviable reputation for performance, stability, and an enormous range of advanced features. PostgreSQL is one of the oldest open source projects, completely free to use and developed by a very diverse worldwide community. Most of all, It Just Works!

    PostgreSQL 9 Admin Cookbook offers the information you need to manage your live production databases on PostgreSQL. The book contains insights direct from the main author of the PostgreSQL replication and recovery features, and the database architect of the most successful startup using PostgreSQL, Skype.

    This practical guide gives quick answers to common questions and problems, building on the authors' experience as trainers, users, and core developers of the PostgreSQL database server.

    Each technical aspect is broken down into short recipes that demonstrate solutions with working code then explain why and how that works. The book is intended to be a desk reference for both new users and technical experts.

    The book covers all the latest features in PostgreSQL 9. Soon you will be running a smooth database with ease!

    A practical guide, this cookbook will ensure you run a smooth PostgreSQL database

    Table of Contents

    1. PostgreSQL 9 Administration Cookbook
      1. PostgreSQL 9 Administration Cookbook
      2. Credits
      3. About the Authors
      4. About the Reviewers
      5. 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
      6. 1. First Steps
        1. Introduction
        2. Introducing PostgreSQL 9
          1. What makes PostgreSQL different?
        3. Getting PostgreSQL
          1. How to do it...
          2. How it works...
          3. There's more...
        4. Connecting to PostgreSQL server
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Inspecting your connection information
          4. See also
        5. Enabling access for network/remote users
          1. How to do it...
            1. How it works...
            2. There's more...
            3. See also
          2. Using graphical administration tools
            1. How to do it...
            2. How it works...
            3. There's more...
            4. See also
        6. Using psql query and scripting tool
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Changing your password securely
          1. How to do it...
          2. How it works...
        8. Avoiding hardcoding your password
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        9. Using a connection service file
          1. How to do it...
          2. How it works...
        10. Troubleshooting a failed connection
          1. How to do it...
          2. There's more...
      7. 2. Exploring the Database
        1. Introduction
        2. What version is the server?
          1. How to do it...
          2. How it works...
          3. There's more...
        3. What is the server uptime?
          1. How to do it...
          2. How it works...
          3. See Also
        4. Locate the database server files
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        5. Locate the database server message log
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. Getting ready
          5. How to do it...
          6. How it works...
          7. There's more...
        6. List databases on this database server?
          1. How to do it...
          2. How it works...
          3. There's more...
        7. How many tables in a database?
          1. How to do it...
          2. How it works...
          3. There's more...
        8. How much disk space does a database use?
          1. How to do it...
          2. How it works...
        9. How much disk space does a table use?
          1. How to do it...
          2. How it works...
          3. There's more...
        10. Which are my biggest tables?
          1. How to do it...
        11. How many rows in a table?
          1. How to do it...
          2. How it works...
        12. Quick estimate of the number of rows in a table
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Function 1
            2. Function 2
        13. Understanding object dependencies
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
      8. 3. Configuration
        1. Introduction
        2. Reading the Fine Manual (RTFM)
          1. How to do it...
          2. How it works...
          3. There's more...
        3. Planning a new database
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        4. Changing parameters in your programs
          1. How to do it...
          2. How it works...
          3. There's more...
        5. What are the current configuration settings?
          1. How to do it...
          2. How it works...
        6. Which parameters are at non-default settings?
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Who set that?
        7. Updating the parameter file
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        8. Setting parameters for particular groups of users
          1. How to do it...
          2. How it works...
        9. Basic server configuration checklist
          1. Getting ready
          2. How to do it...
        10. Adding an external module to PostgreSQL
          1. Getting ready
          2. How to do it...
            1. How it works...
        11. Running server in power saving mode
          1. Getting ready
          2. How to do it...
          3. How it works...
      9. 4. Server Control
        1. Introduction
        2. Starting the database server manually
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Stopping the server safely and quickly
          1. How to do it...
          2. How it works...
        4. Stopping the server in an emergency
          1. How to do it...
          2. How it works...
        5. Reloading the server configuration files
          1. How to do it...
          2. How it works...
          3. There's more...
        6. Restarting the server quickly
          1. How to do it...
          2. How it works...
        7. Preventing new connections
          1. How to do it...
          2. How it works...
        8. Restricting users to just one session each
          1. How to do it...
          2. How it works...
        9. Pushing users off the system
          1. How to do it...
          2. How it works...
        10. Deciding on a design for multi-tenancy
          1. How to do it...
        11. Using multiple schemas
          1. Getting ready
          2. How to do it...
          3. How it works...
        12. Giving users their own private database
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        13. Running multiple servers on one system
          1. Getting ready
          2. How to do it...
          3. How it works...
        14. Set up a Connection Pool
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
      10. 5. Tables & Data
        1. Introduction
        2. Choosing good names for database objects
          1. How to do it...
          2. There's More
        3. Handling objects with quoted names
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        4. Enforcing same name, same column definition
          1. Getting ready
          2. How to do it...
            1. Columns
            2. Tables
          3. How it works...
          4. There's more...
        5. Identifying and removing duplicates
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        6. Preventing duplicate rows
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Duplicate indexes
            2. Uniqueness without indexes
            3. Real World Example: IP address range allocation
            4. Real World Example: Range of time
            5. Real World Example: Prefix ranges
        7. Finding a unique key for a set of data
          1. Getting ready
          2. How to do it...
          3. How it works...
        8. Generating test data
          1. How to do it...
            1. Rows
            2. Columns
            3. ORDER
          2. How it works...
          3. There's more...
          4. See also
        9. Randomly sampling data
          1. How to do it...
          2. How it works...
          3. See also
        10. Loading data from a spreadsheet
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        11. Loading data from flat files
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      11. 6. Security
        1. Introduction
          1. Typical user role
        2. Revoking user access to a table
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Best practices
            2. Default search path
        3. Granting user access to a table
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Access to schema is also needed
            2. Granting access to a table through a group role
            3. Granting access to all objects in schema
        4. Creating a new user
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Checking roles of a user
            2. CREATE USER and CREATE GROUP
        5. Temporarily preventing a user from connecting
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Limiting number of concurrent connections by a user
            2. Forcing NOLOGIN users to disconnect
        6. Removing a user without dropping their data
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. You can assign the rights of the "deleted" user to a new user
            2. Assigning ownerships to other users
          4. Reassigning ownership in older databases
        7. Checking all users have a secure password
          1. Getting ready
          2. How to do it...
        8. Giving limited superuser powers to specific users
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Writing a debugging_info function for developers
        9. Auditing DDL changes
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Was the change committed
            2. Who made the change
            3. Can't I find out this information from the database
        10. Auditing data changes
          1. Getting ready
          2. How to do it...
            1. Collecting data changes from server log
            2. Collecting changes using triggers
              1. Collecting changes using triggers and saving them to another database using dblink or plproxy
        11. Integrating with LDAP
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Setting up the client to use LDAP
          5. See also
        12. Connecting using SSL
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Getting SSL key and certificate
            2. Setting up a client to use SSL
            3. Checking server authenticity
          5. See also
        13. Encrypting sensitive data
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. For really sensitive data
            2. For really, really really, sensitive data
            3. Two versions of pg_crypto
          5. See also
      12. 7. Database Administration
        1. Introduction
        2. Writing a script that either all succeeds or all fails
          1. How to do it...
          2. How it works...
          3. There's more...
        3. Writing a psql script that exits on first error
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        4. Performing actions on many tables
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Using pg_batch to run tasks in parallel
        5. Adding/Removing the columns of a table
          1. How to do it...
          2. How it works...
          3. There's more...
        6. Changing datatype of a column
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        7. Adding/Removing schemas
          1. How to do it...
          2. There's more...
            1. Schema-level privileges
        8. Moving objects between schemas
          1. How to do it...
          2. How it works...
          3. There's more...
        9. Adding/Removing tablespaces
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Putting pg_xlog on a separate device
            2. Tablespace-level tuning
        10. Moving objects between tablespaces
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        11. Accessing objects in other PostgreSQL databases
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        12. Making views updateable
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
      13. 8. Monitoring and Diagnosis
        1. Introduction
          1. Monitoring is important
            1. Providing PostgreSQL information to monitoring tools
            2. Where to find more information about generic monitoring tools
            3. Realtime view using pgAdmin
        2. Is the user connected?
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. What if I want to know "is that computer connected?"
        3. What are they running?
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. How to catch queries which only run for a few milliseconds
            2. How to watch longest queries
            3. Watching queries from ps
          5. See also
        4. Are they active or blocked?
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. No need for "= true"
            2. This catches only queries waiting on locks
        5. Who is blocking them?
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Killing a specific session
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Try to cancel the query first
            2. If the backend won't terminate
            3. Use statement timeout to clean up queries which take too long
            4. Killing "Idle in transaction" queries
            5. You can also kill the backend from command line
        7. Resolving an in-doubt prepared transaction
          1. Getting ready
        8. Is anybody using a specific table?
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. The quick and dirty way
            2. Collecting daily usage statistics
        9. When did anybody last use it?
          1. Getting ready
          2. How to do it...
            1. Looking at file dates
          3. How it works...
          4. There's more...
            1. There may be last-use information in future version of PostgreSQL
        10. How much disk space is used by temporary data?
          1. Getting ready
          2. How to do it...
            1. When temp_tablespaces has one or more tablespaces
            2. When temp_tablespaces is empty
          3. How it works...
          4. There's more...
            1. Finding out if temporary file is in use any more
            2. Logging temporary file usage
        11. Why are my queries slowing down?
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Do the queries return significantly more data than earlier?
            2. Do the queries also run slowly when run alone?
            3. Is the second run of same query also slow?
            4. Table and index bloat
          5. See also
        12. Investigating and reporting a bug
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
            1. The official PostgreSQL bug/problem reporting guides
        13. Producing a daily summary of logfile errors
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Writing your own log processor
          5. See also
      14. 9. Regular Maintenance
        1. Introduction
        2. Controlling automatic database maintenance
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        3. Avoiding auto freezing and page corruptions
          1. Getting ready
          2. How to do it...
        4. Avoiding transaction wraparound
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        5. Removing old prepared transactions
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        6. Actions for heavy users of temporary tables
          1. How to do it...
          2. How it works...
        7. Identifying and fixing bloated tables and indexes
          1. How to do it...
          2. How it works...
          3. There's more...
        8. Maintaining indexes
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        9. Finding the unused indexes
          1. How to do it...
          2. How it works...
          3. See also
        10. Carefully removing unwanted indexes
          1. How to do it...
          2. How it works...
        11. Planning maintenance
          1. How to do it...
          2. How it works...
      15. 10. Performance & Concurrency
        1. Introduction
        2. Finding slow SQL statements
          1. Getting ready
          2. How to do it...
            1. Finding queries that make the server slow
            2. Once a suspect is found, make the query slow, so that it is logged
            3. Finding slow queries run as prepared statements
            4. See also
        3. Collecting regular statistics from pg_stat* views
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. Another statistics collection package
        4. Finding what makes SQL slow
            1. Too much data is processed
            2. Too little of the data fits in the memory
            3. The query returns too much data
            4. Locking problems
            5. Not enough CPU power or disk I/O capacity for the current load
        5. Reducing the number of rows returned
            1. A full text search returns 10,000 documents, but only first the 20 are displayed to user
            2. An application requests all products for a branch office to run a complex calculation over them
            3. Application runs a huge number of small lookup queries
        6. Simplifying complex SQL
          1. Getting ready
          2. How to do it...
            1. Moving part of the query into a view
            2. Using the WITH statement instead of a separate view
            3. Using temporary tables for parts of the query
            4. Use materialized views (long-living temp tables)
            5. Using set-returning functions for some parts of queries
        7. Speeding up queries without rewriting them
            1. Providing better information to the optimizer
            2. Adding a multi-column index tuned specifically for that query
            3. Adding a special conditional index
            4. Cluster tables on specific indexes
            5. Use table partitioning and constraint exclusion
            6. In case of many updates set fillfactor on table
            7. Rewriting the schema—a more radical approach
        8. Why is my query not using an index?
          1. How to do it...
        9. How do I force a query to use an index
          1. Getting ready
          2. How to do it...
            1. set enable_seqscan to false
            2. Lower random_page_cost
        10. Using optimistic locking
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Move the whole computation into the database function
        11. Reporting performance problems
      16. 11. Backup & Recovery
        1. Introduction
        2. Understanding and controlling crash recovery
          1. How to do it...
          2. How it works...
          3. There's more...
        3. Planning backups
          1. How to do it...
          2. How it works...
        4. Hot logical backup of one database
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also
        5. Hot logical backup of all databases
          1. How to do it...
          2. How it works...
          3. See also
        6. Hot logical backup of all tables in a tablespace
          1. How to do it...
          2. How it works...
        7. Backup of database object definitions
          1. How to do it...
          2. There's more...
        8. Standalone hot physical database backup
          1. How to do it...
          2. How it works...
          3. See also
        9. Hot physical backup & Continuous Archiving
          1. Getting ready
          2. How to do it...
          3. How it works...
        10. Recovery of all databases
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        11. Recovery to a point in time
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        12. Recovery of a dropped/damaged table
          1. How to do it...
            1. How it works...
            2. See also
        13. Recovery of a dropped/damaged tablespace
          1. How to do it...
            1. There's more...
        14. Recovery of a dropped/damaged database
          1. How to do it...
        15. Improving performance of backup/restore
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        16. Incremental/Differential backup and restore
          1. How to do it...
          2. How it works...
          3. There's more...
      17. 12. Replication & Upgrades
        1. Introduction
        2. Understanding replication concepts
          1. How it works...
          2. There's more...
            1. See also
        3. Replication best practices
          1. How to do it...
        4. File-based log-shipping replication
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        5. Setting up streaming log replication
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        6. Managing log shipping replication
          1. Getting ready
          2. How to do it...
            1. Monitoring
            2. Switchover and Failover
            3. Switchback
          3. How it works...
          4. See also
        7. Managing Hot Standby
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        8. Selective replication using Londiste
          1. Getting ready
          2. How to do it...
            1. How it works...
            2. There's more...
            3. See also
        9. Selective replication using Slony 2.0
          1. Getting ready
          2. How to do it...
            1. Full replication process
            2. Maintaining replication
          3. How it works...
          4. There's more...
          5. See also
        10. Load balancing with pgpool-II 3.0
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        11. Upgrading (minor)
          1. Getting ready
          2. How to do it...
          3. How it works...
        12. Major upgrades in-place
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        13. Major upgrades online using replication tools
          1. How to do it...
          2. How it works...