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 10 Administration Cookbook

Book Description

A practical guide to administer, monitor and replicate your PostgreSQL 10 database

About This Book
  • Get to grips with the capabilities of PostgreSQL 10 to administer your database more efficiently
  • Monitor, tune, secure and protect your database for optimal performance
  • A step-by-step, recipe-based guide to help you tackle any problem in PostgreSQL 10 administration with ease
Who This Book Is For

This book is for database administrators, data architects, developers, or anyone with an interest in planning for, or running, live production databases using PostgreSQL. It is most suited to those looking for hands-on solutions to any problem associated with PostgreSQL administration.

What You Will Learn
  • Get to grips with the newly released PostgreSQL 10 features to improve database performance and reliability
  • Manage open source PostgreSQL versions 10 on various platforms.
  • Explore best practices for planning and designing live databases
  • Select and implement robust backup and recovery techniques in PostgreSQL 10
  • Explore concise and clear guidance on replication and high availability
  • Discover advanced technical tips for experienced users
In Detail

PostgreSQL is a powerful, open source database management system with an enviable reputation for high performance and stability. With many new features in its arsenal, PostgreSQL 10 allows users to scale up their PostgreSQL infrastructure. This book takes a step-by-step, recipe-based approach to effective PostgreSQL administration.

Throughout this book, you will be introduced to these new features such as logical replication, native table partitioning, additional query parallelism, and much more. You will learn how to tackle a variety of problems that are basically the pain points for any database administrator - from creating tables to managing views, from improving performance to securing your database. More importantly, the book pays special attention to topics such as monitoring roles, backup, and recovery of your PostgreSQL 10 database, ensuring high availability, concurrency, and replication.

By the end of this book, you will know everything you need to know to be the go-to PostgreSQL expert in your organization.

Style and approach

The book is a step by step guide with example-driven recipes, focused on the new features of the latest PostgreSQL version10. This book will serve as a specific guide to understand and leverage useful PostgreSQL functionalities to create better and more efficient databases.

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. Title Page
  2. Copyright and Credits
    1. PostgreSQL 10 Administration Cookbook
  3. Packt Upsell
    1. Why subscribe?
    2. PacktPub.com
  4. Contributors
    1. About the authors
    2. About the reviewer
    3. Packt is searching for authors like you
  5. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the example code files
      2. Download the color images
      3. Conventions used
    4. Sections
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
      5. See also
    5. Get in touch
      1. Reviews
  6. First Steps
    1. Introduction
      1. Introducing PostgreSQL 10
        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…
    6. OmniDB
      1. See also
    7. 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
    8. Changing your password securely
      1. How to do it…
      2. How it works…
    9. Avoiding hardcoding your password
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    10. Using a connection service file
      1. How to do it…
      2. How it works…
    11. Troubleshooting a failed connection
      1. How to do it…
      2. There's more…
  7. 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…
  8. 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…
        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…
      3. How it works...
    12. Managing installed extensions
      1. How to do it…
      2. How it works…
      3. There's more…
  9. 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…
  10. Tables and Data
    1. Choosing good names for database objects
      1. Getting ready
      2. How to do it…
      3. There's more…
    2. Handling objects with quoted names
      1. Getting ready
      2. How to do it...
      3. How it works…
      4. There's more…
    3. Enforcing the same name and definition for columns
      1. Getting ready
      2. How to do it...
      3. How it works…
      4. There's more…
    4. Identifying and removing duplicates
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    5. 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
    6. Finding a unique key for a set of data
      1. Getting ready
      2. How to do it…
      3. How it works…
    7. Generating test data
      1. How to do it...
      2. How it works…
      3. There's more…
      4. See also
    8. Randomly sampling data
      1. How to do it…
      2. How it works...
    9. Loading data from a spreadsheet
      1. Getting ready
      2. How to do it...
      3. How it works...
        1. There's more...
    10. Loading data from flat files
      1. Getting ready
      2. How to do it...
      3. How it works…
      4. There's more…
  11. 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 database access
      1. Getting ready
        1. Auditing SQL
        2. Auditing table access
        3. Managing the audit log
        4. Auditing data changes
    13. 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
    14. 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
    15. 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
    16. 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
    17. Mapping external usernames to database roles
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    18. 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
  12. 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. Using psql variables
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There’s more…
    5. Placing query output into psql variables
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There’s more…
    6. Writing a conditional psql script
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There’s more…
    7. Investigating a psql error
      1. Getting ready
      2. How to do it…
      3. There's more…
    8. Performing actions on many tables
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    9. Adding/removing columns on a table
      1. How to do it…
      2. How it works…
      3. There's more…
    10. Changing the data type of a column
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    11. Changing the definition of a data type
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    12. Adding/removing schemas
      1. How to do it…
      2. There's more…
      3. Using schema-level privileges
    13. Moving objects between schemas
      1. How to do it…
      2. How it works…
      3. There's more…
    14. Adding/removing tablespaces
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
        1. Putting pg_wal on a separate device
        2. Tablespace-level tuning
    15. Moving objects between tablespaces
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    16. 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…
    17. Accessing objects in other foreign databases
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    18. Updatable views
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    19. Using materialized views
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
  13. Monitoring and Diagnosis
    1. Providing PostgreSQL information to monitoring tools
      1. Finding more information about generic monitoring tools
    2. Real-time viewing using pgAdmin or OmniDB
      1. Getting ready
      2. How to do it… (with pgAdmin)
      3. How to do it… (with OmniDB)
    3. Checking whether a user is connected
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    4. Checking whether a computer is connected
      1. How to do it…
      2. There's more…
    5. Repeatedly executing a query in psql
      1. How to do it…
      2. There's more…
    6. 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
    7. 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?
    8. Knowing who is blocking a query
      1. Getting ready
      2. How to do it…
      3. How it works…
    9. 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
    10. Detecting an in-doubt prepared transaction
      1. How to do it…
    11. 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
    12. Knowing when a table was last used
      1. Getting ready
      2. How to do it…
      3. How it works..
      4. There's more…
    13. 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
    14. 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
    15. Investigating and reporting a bug
      1. Getting ready
      2. How to do it…
      3. How it works…
    16. Producing a daily summary of log file errors
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    17. Analyzing the real-time performance of your queries
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
  14. Regular Maintenance
    1. Controlling automatic database maintenance
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
      5. See also
    2. Avoiding auto-freezing and page corruptions
      1. How to do it…
    3. Removing issues that cause bloat
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    4. Removing old prepared transactions
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    5. Actions for heavy users of temporary tables
      1. How to do it…
      2. How it works…
    6. Identifying and fixing bloated tables and indexes
      1. How to do it…
      2. How it works…
      3. There's more…
    7. Monitoring and tuning vacuum
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    8. Maintaining indexes
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    9. Adding a constraint without checking existing rows
      1. Getting ready
      2. How to do it…
      3. How it works…
    10. Finding unused indexes
      1. How to do it…
      2. How it works…
    11. Carefully removing unwanted indexes
      1. Getting ready
      2. How to do it…
      3. How it works…
    12. Planning maintenance
      1. How to do it…
      2. How it works…
  15. 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. Collect 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. Time Series Partitioning
        2. Using a TABLESAMPLE view
        3. In case of many updates, set fillfactor on the table
        4. 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…
      4. There's even 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…
  16. 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. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
      5. 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…
  17. Replication and Upgrades
    1. Replication concepts
      1. Topics
      2. Basic concepts
      3. History and scope
      4. Practical aspects
      5. Data loss
      6. Single-master replication
      7. Multinode architectures
      8. Clustered or massively parallel databases
      9. Multimaster replication
      10. Scalability tools
      11. Other approaches to replication
    2. Replication best practices
      1. Getting ready
      2. How to do it…
      3. 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…
      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...
  18. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think