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

Book Description

Over 150 recipes to help you run an efficient PostgreSQL database in the cloud

In Detail

PostgreSQL is a powerful, open source, object-relational database system, fast becoming one of the world's most popular server databases with an enviable reputation for performance and stability and an enormous range of advanced features.

Starting with short and simple recipes to get you back up and running with an exploration of the ins and outs of your database, you will soon dive into core features such as configuration, server control, tables, and data. This is a practical guide aimed at giving sysadmins and database administrators the necessary toolkit to be able to set up, run, and extend powerful databases with PostgreSQL.

What You Will Learn

  • Implement PostgreSQL features for reliability and performance

  • Harness the power of the latest PostgreSQL 9.4 features

  • Manage PostgreSQL versions 9.0, 9.1, 9.2, 9.3, and 9.4

  • Advanced technical tips for experienced users

  • Explore best practices for planning and designing live databases

  • Select and implement robust backup and recovery techniques

  • Concise and clear guidance on replication and high availability

  • Latest details on Logical Replication and Bi-Directional Replication

  • 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 9 Administration Cookbook Second Edition
      1. Table of Contents
      2. PostgreSQL 9 Administration Cookbook Second Edition
      3. Credits
      4. About the Authors
      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. First Steps
        1. Introduction
          1. Introducing PostgreSQL 9
          2. What makes PostgreSQL different?
            1. Robustness
            2. Security
            3. Ease of use
            4. Extensibility
            5. Performance and concurrency
            6. Scalability
            7. SQL and NoSQL
            8. Popularity
            9. Commercial support
            10. Research and development funding
        2. Getting PostgreSQL
          1. How to do it…
          2. How it works…
          3. There's more…
        3. Connecting to the PostgreSQL server
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
          5. See also
        4. Enabling access for network/remote users
          1. How to do it…
          2. How it works…
          3. There's more…
          4. See also
        5. Using graphical administration tools
          1. How to do it…
          2. How it works…
          3. There's more…
          4. See also
        6. Using the 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…
      9. 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. Locating the database server files
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        5. Locating the database server's message log
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        6. Locating the database's system identifier
          1. Getting ready
          2. How to do it…
          3. How it works…
        7. Listing databases on this database server
          1. How to do it…
          2. How it works…
          3. There's more…
        8. How many tables in a database?
          1. How to do it…
          2. How it works…
          3. There's more…
        9. How much disk space does a database use?
          1. How to do it…
          2. How it works…
        10. How much disk space does a table use?
          1. How to do it…
          2. How it works…
          3. There's more…
        11. Which are my biggest tables?
          1. How to do it…
          2. How it works…
        12. How many rows in a table?
          1. How to do it…
          2. How it works…
        13. Quickly estimating the number of rows in a table
          1. How to do it…
          2. How it works…
          3. There's more…
            1. Function 1 – estimating the number of rows
            2. Function 2 – computing the size of a table without locks
        14. Listing extensions in this database
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        15. Understanding object dependencies
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
      10. 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. Finding the current configuration settings
          1. How to do it…
          2. There's more…
          3. How it works…
        6. Which parameters are at nondefault settings?
          1. How to do it…
          2. How it works…
          3. There's more…
        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. The basic server configuration checklist
          1. Getting ready
          2. How to do it…
          3. There's more…
        10. Adding an external module to PostgreSQL
          1. Getting ready
          2. How to do it…
            1. Installing modules using a software installer
            2. Installing modules from PGXN
            3. Installing modules from a manually downloaded package
            4. Installing modules from source code
          3. How it works…
        11. Using an installed module
          1. Getting ready
          2. How to do it…
            1. Using the extension infrastructure
            2. Without the extension infrastructure
          3. How it works…
          4. There's more…
        12. Managing installed extensions
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
      11. 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…
          3. See also
        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. There's more…
        7. Preventing new connections
          1. How to do it…
          2. How it works…
        8. Restricting users to only 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 multitenancy
          1. How to do it…
          2. How it works…
        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. Setting up a connection pool
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        15. Accessing multiple servers using the same host and port
          1. Getting ready
          2. How to do it…
          3. There's more…
      12. 5. Tables and Data
        1. Introduction
        2. Choosing good names for database objects
          1. Getting ready
          2. How to do it…
          3. 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 the same name and definition for columns
          1. Getting ready
          2. How to do it…
          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…
          2. How it works…
          3. There's more…
          4. See also
        9. Randomly sampling data
          1. How to do it…
          2. How it works…
        10. Loading data from a spreadsheet
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        11. Loading data from flat files
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
      13. 6. Security
        1. Introduction
          1. Typical user role
        2. The PostgreSQL superuser
          1. How to do it…
          2. How it works…
          3. There's more…
            1. Other superuser-like attributes
            2. Attributes are never inherited
          4. See also
        3. Revoking user access to a table
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Database creation scripts
            2. Default search path
            3. Securing views
        4. 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 the schema
            2. Granting access to a table through a group role
            3. Granting access to all objects in a schema
        5. Creating a new user
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        6. Temporarily preventing a user from connecting
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Limiting the number of concurrent connections by a user
            2. Forcing NOLOGIN users to disconnect
        7. Removing a user without dropping their data
          1. Getting ready
          2. How to do it…
          3. How it works…
        8. Checking whether all users have a secure password
          1. How to do it…
          2. How it works…
        9. 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
        10. 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 I find this information from the database?
            4. You may still miss some DDL…
        11. Auditing data changes
          1. Getting ready
          2. How to do it…
            1. Collecting data changes from the server log
            2. Collecting changes using triggers
            3. Using a single audit trigger to collect changes from multiple tables
            4. Collecting changes using triggers and saving them in another database using dblink or plproxy
        12. Always knowing which user is logged in
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Not inheriting the user attributes
        13. 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
            2. Replacement for the User Name Map feature
          5. See also
        14. Connecting using SSL
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Getting the SSL key and certificate
            2. Setting up a client to use SSL
            3. Checking server authenticity
        15. Using SSL certificates to authenticate the client
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Avoiding duplicate SSL connection attempts
            2. Using multiple client certificates
            3. Using the client certificate to select the database user
          5. See also
        16. Mapping external usernames to database roles
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        17. 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!
          5. See also
      14. 7. Database Administration
        1. Introduction
        2. Writing a script that either succeeds entirely or fails entirely
          1. How to do it…
          2. How it works…
          3. There's more…
        3. Writing a psql script that exits on the 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 columns on a table
          1. How to do it…
          2. How it works…
          3. There's more…
        6. Changing the data type of a column
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        7. Changing the definition of a data type
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        8. Adding/removing schemas
          1. How to do it…
          2. There's more…
            1. Using schema-level privileges
        9. Moving objects between schemas
          1. How to do it…
          2. How it works…
          3. There's more…
        10. 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
        11. Moving objects between tablespaces
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        12. Accessing objects in other PostgreSQL databases
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
          5. There's more…
        13. Accessing objects in other foreign databases
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        14. Updatable views
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        15. Using materialized views
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
      15. 8. Monitoring and Diagnosis
        1. Introduction
          1. Providing PostgreSQL information to monitoring tools
          2. Finding more information about generic monitoring tools
          3. Real-time viewing using pgAdmin
        2. Checking whether a user is connected
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. What if I want to know whether that computer is connected?
            2. What if I want to repeatedly execute a query in psql?
        3. Checking which queries are running
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Catching queries which only run for a few milliseconds
            2. Watching the longest queries
            3. Watching queries from ps
          5. See also
        4. Checking which queries are active or blocked
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. No need for the = true part
            2. This catches only queries waiting on locks
        5. Knowing who is blocking a query
          1. Getting ready
          2. How to do it…
          3. How it works…
        6. Killing a specific session
          1. How to do it…
          2. How it works…
          3. There's more…
            1. Trying to cancel the query first
            2. What if the backend won't terminate?
            3. Using statement timeout to clean up queries that take too long to run
            4. Killing Idle in transaction queries
            5. Killing the backend from the command line
        7. Detecting an in-doubt prepared transaction
          1. How to do it…
        8. Knowing whether anybody is 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. Knowing when a table was last used
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        10. Usage of disk space by temporary data
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
            1. Finding out whether a temporary file is in use any more
            2. Logging temporary file usage
        11. Understanding why queries slow 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 they did earlier?
            2. Do the queries also run slowly when they are run alone?
            3. Is the second run of the 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…
        13. Producing a daily summary of log file errors
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
          5. See also
        14. Analyzing the real-time performance of your queries
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
      16. 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…
          3. There's more…
        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. Adding a constraint without checking existing rows
          1. Getting ready
          2. How to do it…
          3. There's more…
        10. Finding unused indexes
          1. How to do it…
          2. How it works…
        11. Carefully removing unwanted indexes
          1. How to do it…
          2. How it works…
        12. Planning maintenance
          1. How to do it…
          2. How it works…
      17. 10. Performance and Concurrency
        1. Introduction
        2. Finding slow SQL statements
          1. Getting ready
          2. How to do it…
          3. 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…
            1. Another statistics collection package
        4. Finding out what makes SQL slow
          1. How to do it…
          2. There's more…
            1. The query returns too much data
            2. Locking problems
            3. Not enough CPU power or disk I/O capacity for the current load
            4. EXPLAIN options
          3. See also
        5. Reducing the number of rows returned
          1. How to do it…
          2. There's more…
        6. Simplifying complex SQL queries
          1. Getting ready
          2. How to do it…
          3. There's more…
            1. Using materialized views (long-living, temporary tables)
            2. Using set-returning functions for some parts of queries
        7. Speeding up queries without rewriting them
          1. How to do it…
          2. There's more…
            1. In case of many updates, set fillfactor on the table
            2. Rewriting the schema – a more radical approach
        8. Why a query is not using an index
          1. How to do it…
        9. Forcing a query to use an index
          1. Getting ready
          2. How to do it…
          3. There's more…
        10. Using optimistic locking
          1. How to do it…
          2. How it works…
          3. There's more…
        11. Reporting performance problems
          1. How to do it…
          2. There's more…
      18. 11. Backup and 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…
        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. There's more…
          4. See also
        9. Hot physical backup and 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…
            1. Logical – from the custom dump taken with pg_dump -F c
            2. Logical – from the script dump created by pg_dump –F p
            3. Logical – from the script dump created by pg_dumpall
            4. Physical
          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. Logical – from the custom dump taken with pg_dump -F c
            2. Logical – from the script dump
            3. Physical
          2. How it works…
          3. See also
        13. Recovery of a dropped/damaged tablespace
          1. How to do it…
            1. Logical – from the custom dump taken with pg_dump -F c
            2. Logical – from the script dump
            3. Physical
          2. There's more…
        14. Recovery of a dropped/damaged database
          1. How to do it...
            1. Logical – from the custom dump -F c
            2. Logical – from the script dump created by pg_dump
            3. Logical – from the script dump created by pg_dumpall
            4. Physical
        15. Improving performance of backup/recovery
          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. Hot physical backups with Barman
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        18. Recovery with Barman
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
      19. 12. Replication and Upgrades
        1. Introduction
          1. Replication concepts
          2. Topics
          3. Basic concepts
          4. History and scope
          5. Practical aspects
          6. Data loss
          7. Single-master replication
          8. Multinode architectures
          9. Clustered or massively parallel databases
          10. Multimaster replication
          11. Scalability tools
          12. Other approaches to replication
        2. Replication best practices
          1. How to do it…
          2. There's more…
        3. Setting up file-based replication – deprecated
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
          5. See also
        4. Setting up streaming replication
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        5. Setting up streaming replication security
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        6. Hot Standby and read scalability
          1. Getting ready
          2. How to do it…
          3. How it works…
        7. Managing streaming replication
          1. Getting ready
          2. How to do it…
          3. There's more…
          4. See also
        8. Using repmgr
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        9. Using Replication Slots
          1. Getting ready
          2. How to do it…
          3. There's more…
          4. See also
        10. Monitoring replication
          1. Getting ready
          2. How to do it…
          3. There's more…
        11. Performance and Synchronous Replication
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        12. Delaying, pausing, and synchronizing replication
          1. Getting ready
          2. How to do it…
          3. There's more…
        13. Logical Replication
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
          5. See also
        14. Bi-Directional Replication
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        15. Archiving transaction log data
          1. Getting ready
          2. How to do it…
          3. There's more…
          4. See also
        16. Upgrading – minor releases
          1. Getting ready
          2. How to do it…
          3. How it works…
        17. Major upgrades in-place
          1. Getting ready
          2. How to do it…
          3. How it works…
        18. Major upgrades online
          1. How to do it…
          2. How it works…
      20. Index