O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

PostgreSQL Administration Cookbook, 9.5/9.6 Edition

Book Description

Over 150 recipes to help you administer your PostgreSQL database more efficiently

About This Book

  • Get to grips with the capabilities of PostgreSQL 9.6 to administer your database more efficiently
  • Monitor, tune, secure and protect your database
  • A step-by-step, recipe-based guide to help you tackle any problem in PostgreSQL administration with ease

Who This Book Is For

This book is for system administrators, database administrators, data architects, developers, and anyone with an interest in planning for, or running, live production databases. This book is most suited to those who have some technical experience.

What You Will Learn

  • Implement PostgreSQL features for performance and reliability
  • Harness the power of the latest PostgreSQL 9.6 features
  • Manage open source PostgreSQL versions 9.5 and 9.6 on various platforms
  • Discover advanced technical tips for experienced users
  • Explore best practices for planning and designing live databases
  • Select and implement robust backup and recovery techniques
  • Explore concise and clear guidance on replication and high availability
  • See the latest details on Logical Replication and Bi-Directional Replication

In Detail

PostgreSQL is a powerful opensource database management system; now recognized as the expert's choice for a wide range of applications, it has an enviable reputation for performance and stability. PostgreSQL provides an integrated feature set comprising relational database features, object-relational, text search, Geographical Info Systems, analytical tools for big data and JSON/XML document management.

Starting with short and simple recipes, you will soon dive into core features, such as configuration, server control, tables, and data. You will tackle a variety of problems a database administrator usually encounters, from creating tables to managing views, from improving performance to securing your database, and from using monitoring tools to using storage engines. Recipes based on important topics such as high availability, concurrency, replication, backup and recovery, as well as diagnostics and troubleshooting are also given special importance.

By the end of this book, you will have all the knowledge you need to run, manage, and maintain PostgreSQL efficiently.

Style and approach

This book takes a step-by-step, recipe-based approach, where each recipe focuses on a particular challenge faced by a PostgreSQL administrator while administering his/her database. Explained in a very easy to follow manner, every task is supported with best practices, tips and tricks.

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 code file.

Table of Contents

  1. Customer Feedback
  2. 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. Downloading the color images of this book
      3. Errata
      4. Piracy
      5. Questions
  3. First Steps
    1. Introduction
      1. Introducing PostgreSQL 9.6
        1. 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. 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...
  4. 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 are there 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 are there 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...
  5. Configuration
    1. Introduction
    2. Reading the fine manual
      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 non-default 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...
      3. How it works...
    11. Using an installed module
      1. Getting ready
      2. How to do it...
      3. How it works...
    12. Managing installed extensions
      1. How it works...
      2. There's more...
  6. 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...
  7. 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...
        1. 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...
  8. 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. Granting user access to specific columns
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    6. Granting user access to specific rows
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    7. Creating a new user
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. 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
    9. Removing a user without dropping their data
      1. Getting ready
      2. How to do it...
      3. How it works...
    10. Checking whether all users have a secure password
      1. How to do it...
      2. How it works...
    11. 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
    12. 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...
    13. 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. Collecting changes using triggers and saving them in another database
    14. 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 user attributes
    15. 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
    16. 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
    17. Using SSL certificates to authenticate
      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
    18. Mapping external usernames to database roles
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    19. 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
  9. 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. Investigating a psql error
      1. Getting ready
      2. How to do it
      3. There's more...
    5. Performing actions on many tables
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    6. Adding/removing columns on a table
      1. How to do it...
      2. How it works...
      3. There's more...
    7. Changing the data type of a column
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. Changing the definition of a data type
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    9. Adding/removing schemas
      1. How to do it...
      2. There's more...
      3. Using schema-level privileges
    10. Moving objects between schemas
      1. How to do it...
      2. How it works...
      3. There's more...
    11. 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
    12. Moving objects between tablespaces
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    13. 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...
    14. Accessing objects in other foreign databases
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    15. Updatable views
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    16. Using materialized views
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  10. Monitoring and Diagnosis
    1. Introduction
      1. Providing PostgreSQL information to monitoring tools
      2. Finding more information about generic monitoring tools
    2. Real-time viewing using pgAdmin
    3. 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 a computer is connected?
        2. What if I want to repeatedly execute a query in psql?
    4. Checking which queries are running
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Catching queries that only run for a few milliseconds
        2. Watching the longest queries
        3. Watching queries from ps
      5. See also
    5. 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. Do we catch all queries waiting on locks?
    6. Knowing who is blocking a query
      1. Getting ready
      2. How to do it...
      3. How it works...
    7. Killing a specific session
      1. How to do it...
      2. How it works...
      3. There's more...
        1. Try 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
    8. Detecting an in-doubt prepared transaction
      1. How to do it...
    9. 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
    10. Knowing when a table was last used
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    11. 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
    12. 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
    13. Investigating and reporting a bug
      1. Getting ready
      2. How to do it...
      3. How it works...
    14. 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
    15. Analyzing the real-time performance of your queries
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  11. 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. How to do it...
    4. Removing issues that cause bloat
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    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. Monitoring and tuning vacuum
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    9. Maintaining indexes
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    10. Adding a constraint without checking existing rows
      1. Getting ready
      2. How to do it...
      3. How it works...
    11. Finding unused indexes
      1. How to do it...
      2. How it works...
    12. Carefully removing unwanted indexes
      1. Getting ready
      2. How to do it...
      3. How it works...
    13. Planning maintenance
      1. How to do it...
      2. How it works...
  12. Performance and Concurrency
    1. Introduction
    2. Finding slow SQL statements
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    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. Getting ready
      2. How to do it...
      3. There's more...
        1. Not enough CPU power or disk I/O capacity for the current load
        2. Locking problems
        3. EXPLAIN options
      4. See also
    5. Reducing the number of rows returned
      1. How to do it...
      2. There's more...
      3. See also
    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...
        1. Increasing work_mem
        2. More ideas with indexes
      2. There's more...
        1. Using a TABLESAMPLE view
        2. In case of many updates, set fillfactor on the table
        3. Rewriting the schema - a more radical approach
    8. Discovering why a query is not using an index
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    9. Forcing a query to use an index
      1. Getting ready
      2. How to do it...
      3. There's more...
    10. Using parallel query
      1. How to do it...
      2. How it works...
      3. There's more...
    11. Using optimistic locking
      1. How to do it...
      2. How it works...
      3. There's more...
    12. Reporting performance problems
      1. How to do it...
      2. There's more...
  13. 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 backups of one database
      1. How to do it...
      2. How it works...
      3. There's more...
      4. See also
    5. Hot logical backups of all databases
      1. How to do it...
      2. How it works...
      3. See also
    6. Backups of database object definitions
      1. How to do it...
      2. There's more...
    7. Standalone hot physical database backup
      1. How to do it...
      2. How it works...
      3. There's more...
      4. See also
    8. Hot physical backup and continuous archiving
      1. Getting ready
      2. How to do it...
      3. How it works...
    9. Recovery of all databases
      1. Getting ready
      2. How to do it...
        1. Logical - from 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
    10. 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
    11. Recovery of a dropped/damaged table
      1. How to do it...
        1. Logical - from custom dump taken with pg_dump -F c
        2. Logical - from the script dump
        3. Physical
      2. How it works...
      3. See also
    12. 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
    13. Improving performance of backup/recovery
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    14. Incremental/differential backup and restore
      1. How to do it...
      2. How it works...
      3. There's more...
    15. Hot physical backups with Barman
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    16. Recovery with Barman
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  14. 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...
      4. See also
    13. Logical replication
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    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...