Learn PostgreSQL

Learn PostgreSQL

by Luca Ferrari, Enrico Pirozzi
Released October 2020
Publisher(s): Packt Publishing
ISBN: 9781838985288

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

A comprehensive guide to building, managing, and securing scalable and reliable database and data warehousing applications using Postgres 12 and 13

Key Features

  • Set up your database cluster and monitor, secure, and fine-tune it for optimal performance
  • Learn the fundamentals of database management and implement client- and server-side programming using SQL and PL/pgSQL
  • Explore useful tips to develop efficient PostgreSQL database solutions from scratch

Book Description

PostgreSQL is one of the fastest-growing open source object-relational database management systems (DBMS) in the world. As well as being easy to use, it's scalable and highly efficient. In this book, you'll explore PostgreSQL 12 and 13 and learn how to build database solutions using it. Complete with hands-on tutorials, this guide will teach you how to achieve the right database design required for a reliable environment.

You'll learn how to install and configure a PostgreSQL server and even manage users and connections. The book then progresses to key concepts of relational databases, before taking you through the Data Definition Language (DDL) and commonly used DDL commands. To build on your skills, you'll understand how to interact with the live cluster, create database objects, and use tools to connect to the live cluster. You'll then get to grips with creating tables, building indexes, and designing your database schema. Later, you'll explore the Data Manipulation Language (DML) and server-side programming capabilities of PostgreSQL using PL/pgSQL, before learning how to monitor, test, and troubleshoot your database application to ensure high-performance and reliability.

By the end of this book, you'll be well-versed with the Postgres database and be able to set up your own PostgreSQL instance and use it to build robust solutions.

What you will learn

  • Understand how users and connections are managed by running a PostgreSQL instance
  • Interact with transaction boundaries using server-side programming
  • Identify bottlenecks to maintain your database efficiently
  • Create and manage extensions to add new functionalities to your cluster
  • Choose the best index type for each situation
  • Use online tools to set up a memory configuration that will suit most databases
  • Explore how Postgres can be used in multi-instance environments to provide high-availability, redundancy, and scalability

Who this book is for

This Postgres book is for anyone interested in learning about the PostgreSQL database from scratch. Anyone looking to build robust data warehousing applications and scale the database for high-availability and performance using the latest features of PostgreSQL will also find this book useful. Although prior knowledge of PostgreSQL is not required, familiarity with databases is expected.

Table of contents

  1. Title Page
  2. Copyright and Credits
    1. Learn PostgreSQL
  3. Dedication
  4. About Packt
    1. Why subscribe?
  5. Contributors
    1. About the authors
    2. About the reviewers
    3. Packt is searching for authors like you
  6. 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. Get in touch
      1. Reviews
  7. Section 1: Getting Started
  8. Introduction to PostgreSQL
    1. Technical requirements
    2. PostgreSQL at a glance
      1. A brief history of PostgreSQL
      2. What's new in PostgreSQL 12?
      3. What's new in PostgreSQL 13?
      4. PostgreSQL release policy, version numbers, and life cycle
    3. Exploring PostgreSQL terminology
    4. Installing PostgreSQL 12 or higher
      1. What to install
      2. Installing PostgreSQL 12 from binary packages
        1. Installing PostgreSQL 12 on GNU/Linux Debian, Ubuntu, and derivatives
        2. Installing PostgreSQL 12 on Linux Fedora
        3. Installing PostgreSQL 12 on FreeBSD
      3. Installing PostgreSQL from sources
      4. Installing PostgreSQL via pgenv
    5. Summary
    6. References
  9. Getting to Know Your Cluster
    1. Technical requirements
    2. Managing your cluster
      1. pg_ctl
      2. PostgreSQL processes
    3. Connecting to the cluster
      1. The template databases
      2. The psql command-line client
        1. Entering SQL statements via psql
        2. A glance at the psql commands
      3. Introducing the connection string
      4. Solving common connection problems
        1. Database "foo" does not exist
        2. Connection refused
        3. No pg_hba.conf entry
    4. Exploring the disk layout of PGDATA
      1. Objects in the PGDATA directory
        1. Tablespaces
    5. Exploring configuration files and parameters
    6. Summary
    7. References
  10. Managing Users and Connections
    1. Introduction to users and groups
    2. Managing roles
      1. Creating new roles
        1. Role passwords, connections, and availability
      2. Using a role as a group
      3. Removing an existing role
      4. Inspecting existing roles
    3. Managing incoming connections at the role level
      1. The syntax of pg_hba.conf
      2. Order of rules in pg_hba.conf
      3. Merging multiple rules into a single one
      4. Using groups instead of single roles
      5. Using files instead of single roles
    4. Summary
    5. References
  11. Section 2: Interacting with the Database
  12. Basic Statements
    1. Technical requirements
    2. Setting up our developing environment
    3. Creating and managing databases
      1. Creating a database
      2. Managing databases
        1. Listing all databases
        2. Making a new database from a modified template
      3. Dropping tables and databases
        1. Dropping tables
        2. Dropping databases
      4. Making a database copy
      5. Confirming the database size
        1. The psql method
        2. The SQL method
      6. Creating a database
    4. Managing tables
      1. The EXISTS option
      2. Managing temporary tables
      3. Managing unlogged tables
      4. Creating a table
    5. Understanding basic table manipulation statements
      1. Inserting and selecting  data
        1. NULL values
          1. Sorting with NULL values
        2. Creating a table starting from another table
      2. Updating data
      3. Deleting data
    6. Summary
    7. References
  13. Advanced Statements
    1. Exploring the SELECT statement
      1. Using the like clause
      2. Using ilike
      3. Using distinct
      4. Using limit and offset
      5. Using subqueries
        1. Using the IN/NOT IN condition
        2. Using the EXISTS/NOT EXISTS condition
      6. Learning joins
        1. Using INNER JOIN
          1. INNER JOIN versus EXISTS/IN
        2. Using  LEFT JOINS
        3. Using RIGHT JOIN
        4. Using  FULL OUTER JOIN
        5. Using SELF JOIN
      7. Aggregate functions
        1. UNION/UNION ALL
        2. EXCEPT/INTERSECT​
    2. Using UPSERT
      1. UPSERT – the PostgreSQL way
      2. Learning the RETURNING clause for INSERT
      3. Returning tuples out of queries
        1. UPDATE related to multiple tables
        2. Exploring UPDATE .... RETURNING
        3. DELETE .... RETURNING
    3. Exploring CTEs
      1. CTE concept
      2. CTE in PostgreSQL 12
      3. CTE – some examples
      4. Query recursion
        1. Recursive CTEs
    4. Summary
    5. References
  14. Window Functions
    1. Using basic statement window functions
      1. Using the PARTITION BY function and WINDOW clause
      2. Introducing some useful functions
        1. The ROW_NUMBER function
        2. The ORDER BY clause
        3. FIRST_VALUE
        4. LAST_VALUE
        5. RANK
        6. DENSE_RANK 
        7. The LAG and LEAD functions
        8. The CUME_DIST function
        9. The NTILE function
    2. Using advanced statement window functions
      1. The frame clause
        1. ROWS BETWEEN start_point and end_point
        2. RANGE BETWEEN start_point and end_point
    3. Summary
    4. References
  15. Server-Side Programming
    1. Exploring data types
      1. The concept of extensibility
      2. Standard data types
        1. Boolean data type
        2. Numeric data type
          1. Integer types
          2. Numbers with a fixed precision data type
          3. Numbers with an arbitrary precision data type
        3. Character data type
          1. Chars with fixed-length data types
          2. Chars with variable length with a limit data types
          3. Chars with a variable length without a limit data types
        4. Date/timestamp data types
          1. Date data types
          2. Timestamp data types
        5. The NoSQL data type
        6. The hstore data type
        7. The JSON data type
    2. Exploring functions and languages
      1. Functions
      2. SQL functions
        1. Basic functions
        2. SQL functions returning a set of elements
        3. SQL functions returning a table
        4. Polymorphic SQL functions
      3. PL/pgSQL functions
        1. First overview
        2. Declaring function parameters
          1. IN/OUT parameters
          2. Function volatility categories
        3. Control structure
        4. Conditional statements
          1. IF statements
          2. CASE statements
        5. Loop statements
          1. The record type
        6. Exception handling statements
    3. Summary
    4. References
  16. Triggers and Rules
    1. Exploring rules in PostgreSQL
      1. Understanding the OLD and NEW variables
      2. Rules on INSERT
        1. The ALSO option
        2. The INSTEAD OF option
      3. Rules on DELETE / UPDATE
        1. Creating the new_tags table
        2. Creating two tables
        3. Managing  rules on INSERT, DELETE, and UPDATE events 
          1. INSERT rules
          2. DELETE rules
          3. UPDATE rules
    2. Managing triggers in PostgreSQL
      1. Trigger syntax
      2. Triggers on INSERT
        1. The TG_OP variable
      3. Triggers on UPDATE / DELETE
    3. Event triggers
      1. An example of an event trigger
    4. Summary
    5. References
  17. Partitioning
    1. Basic concepts
      1. Range partitioning
      2. List partitioning
      3. Hash partitioning
      4. Table inheritance
        1. Dropping tables
    2. Exploring partitioning using inheritance
      1. An example of list partitioning
        1. Creating tables
        2. Creating triggers and functions, and inserting data
        3. Creating triggers and functions and updating data
    3. Exploring declarative partitioning
      1. List partitioning
      2. Range partitioning
        1. Partition maintenance
    4. Summary
    5. References
  18. Section 3: Administering the Cluster
  19. Users, Roles, and Database Security
    1. Understanding roles
      1. Properties related to new objects
      2. Properties related to superusers
      3. Properties related to replication
      4. Properties related to row-level security
      5. Changing properties of existing roles: the ALTER ROLE statement
        1. Renaming an existing role
        2. SESSION_USER versus CURRENT_USER
        3. Per-role configuration parameters
      6. Inspecting roles
      7. Roles that inherit from other roles
        1. Understanding how privileges are resolved
        2. Role inheritance overview
    2. Access control lists
      1. Default ACLs
        1. Knowing default ACLs
    3. Granting and revoking permissions
      1. Permissions related to tables
      2. Column-based permissions
      3. Permissions related to sequences
      4. Permissions related to schemas
        1. ALL objects in the schema
      5. Permissions related to languages
      6. Permissions related to routines
      7. Permissions related to databases
      8. Other GRANT and REVOKE statements
      9. Assigning the object owner
      10. Inspecting ACLs
    4. Row-level security
    5. Role password encryption
    6. SSL connections
      1. Configuring the cluster for SSL
      2. Connecting to the cluster via SSL
    7. Summary
    8. References
  20. Transactions, MVCC, WALs, and Checkpoints
    1. Technical requirements
    2. Introducing transactions
      1. Comparing implicit and explicit transactions
        1. Time within transactions
      2. More about transaction identifiers – the XID wraparound problem
        1. Virtual and real transaction identifiers
      3. Multi-version concurrency control
    3. Transaction isolation levels
      1. Read uncommitted
      2. Read Committed
      3. Repeatable Read
      4. Serializable
    4. Explaining MVCC
    5. Savepoints
    6. Deadlocks
    7. How PostgreSQL handles persistency and consistency: WALs
      1. Write-Ahead Logs (WALs)
        1. WALs as a rescue method in the event of a crash
        2. Checkpoints
      2. Checkpoint configuration parameters
        1. checkpoint_timeout and max_wal_size
        2. Checkpoint throttling
        3. Manually issuing a checkpoint
    8. VACUUM
      1. Manual VACUUM
      2. Automatic VACUUM
    9. Summary
    10. References
  21. Extending the Database - the Extension Ecosystem
    1. Introducing extensions
      1. The extension ecosystem
      2. Extension components
        1. The control file
      3. The script file
    2. Managing extensions
      1. Creating an extension
      2. Viewing installed extensions
      3. Finding out available extension versions
      4. Altering an existing extension
      5. Removing an existing extension
    3. Exploring the PGXN client
      1. Installing pgxnclient on Debian GNU/Linux and derivates
      2. Installing pgxnclient on Fedora Linux
      3. Installing pgxnclient on FreeBSD
      4. Installing pgxnclient from sources
      5. The pgxnclient command-line interface
    4. Installing extensions
      1. Installing the extension via pgxnclient
      2. Installing the extension manually
      3. Using the installed extension
      4. Removing an installed extension
        1. Removing an extension via pgxncliet
        2. Removing a manually installed extension 
    5. Creating your own extension
      1. Defining an example extension
      2. Creating extension files
      3. Installing the extension
      4. Creating an extension upgrade
      5. Performing an extension upgrade
    6. Summary
    7. References
  22. Indexes and Performance Optimization
    1. Technical requirements
    2. Execution of a statement
      1. Execution stages
      2. The optimizer
      3. Nodes that the optimizer uses
        1. Sequential nodes
          1. Sequential Scan
          2. Index nodes
          3. Join nodes
        2. Parallel nodes
          1. Gather nodes
          2. Parallel scans
          3. Parallel joins
          4. Parallel aggregations
        3. When does the optimizer choose a parallel plan?
        4. Utility nodes
      4. Node costs
    3. Indexes
      1. Index types
      2. Creating an index
      3. Inspecting indexes
      4. Dropping an index
      5. Invalidating an index
      6. Rebuilding an index
    4. The EXPLAIN statement
      1. EXPLAIN output formats
      2. EXPLAIN ANALYZE
      3. EXPLAIN options
    5. An example of query tuning
    6. ANALYZE and how to update statistics
    7. Auto-explain
    8. Summary
    9. References
  23. Logging and Auditing
    1. Technical requirements
    2. Introduction to logging
      1. Where to log
      2. When to log
      3. What to log
      4. A complete example of logging configuration
    3. Extracting information from logs – PgBadger
      1. Installing PgBadger
      2. Configuring PostgreSQL logging for PgBadger usage
      3. Using PgBadger
      4. Scheduling PgBadger
    4. Implementing auditing
      1. Installing PgAudit
      2. Configuring PostgreSQL to exploit PgAudit
      3. Configuring PgAudit
      4. Auditing by session
      5. Auditing by role
    5. Summary
    6. References
  24. Backup and Restore
    1. Technical requirements
    2. Introducing various types of backups and restores
    3. Exploring logical backups
      1. Dumping a single database
      2. Restoring a single database
      3. Limiting the amount of data to back up
      4. Dump formats and pg_restore
      5. Performing a selective restore
      6. Dumping a whole cluster
      7. Parallel backups
      8. Backup automation
    4. Exploring physical backups
      1. Performing a manual physical backup
        1. pg_verifybackup
      2. Starting the cloned cluster
      3. Restoring from a physical backup
    5. Summary
    6. Further reading
  25. Configuration and Monitoring
    1. Technical requirements
    2. Cluster configuration
      1. Inspecting all the configuration parameters
      2. Finding configuration errors
      3. Nesting configuration files
      4. Configuration contexts
      5. Main configuration settings
        1. WAL settings
        2. Memory-related settings
        3. Process information settings
        4. Networking-related settings
        5. Archive and replication settings
        6. Vacuum andautovacuum-related settings
        7. Optimizer settings
        8. Statistics collector
      6. Modifying the configuration from a live system
      7. Configuration generators
    3. Monitoring the cluster
      1. Information about running queries
      2. Inspecting locks
      3. Inspecting databases
      4. Inspecting tables and indexes
      5. More statistics
    4. Advanced statistics with pg_stat_statements
      1. Installing the pg_stat_statements extension
      2. Using pg_stat_statements
      3. Resetting data collected from pg_stat_statements
      4. Tuning pg_stat_statements
    5. Summary
    6. Further Reading
  26. Section 4: Replication
  27. Physical Replication
    1. Exploring basic concepts
      1. WAL
        1. The wal_level directive
      2. Preparing the environment setup for streaming replication
    2. Learning WAL archiving and PITR
      1. PITR – the manual way
        1. The WAL archive
        2. Basebackup
        3. Recovery
    3. Managing streaming replication
      1. Basic concept
      2. Replication environment
      3. The wal_keep_segments option
      4. The slot way
      5. The pg_basebackup command
      6. Asynchronous replication
        1. Replica monitoring
        2. Cascading replication
      7. Synchronous replication
        1. PostgreSQL settings
          1. Master server
          2. Standby server
    4. Summary
    5. References
  28. Logical Replication
    1. Understanding basic concepts
      1. Comparing logical replication and physical replication 
    2. Exploring logical replication setup
      1. Logical replication environment settings
        1. The replica role
        2. Master server – postgresql.conf
        3. Replica server – postgresql.conf
        4. The pg_hba.conf file
      2. Logical replication setup
      3. Monitoring logical replication
      4. Comparing physical replication and logical replication
        1. Simulating on test versus bloating elimination
        2. Read-only versus write allowed
      5. DDL commands
      6. Disabling logical replication
    3. Summary
    4. References
  29. Section 5: The PostegreSQL Ecosystem
  30. Useful Tools and Extensions
    1. Exploring the pg_trgm extension
    2. Using foreign data wrappers and the postgres_fdw extension
    3. Exploring the btree_gin extension
    4. Managing the pgbackrest tool
      1. Basic concepts
      2. Environment setting
        1. The exchange of public keys
      3. Installing pgbackrest
      4. Configuring pgbackrest
        1. The repository configuration 
        2. The PostgreSQL server configuration
          1. The postgresql.conf file
          2. The pgbackrest.conf file
      5. Creating and managing continuous backups
        1. Creating the stanza
        2. Checking the stanza
        3. Managing basebackups
        4. Managing PITR
    5. Summary
    6. References
  31. Toward PostgreSQL 13
    1. Introducing PostgreSQL 13's new features
      1. Replication
      2. Administration
      3. psql
      4. Performance
      5. Backup tools
    2. Upgrading to PostgreSQL 13
    3. Summary
    4. References
  32. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: Learn PostgreSQL
  • Author(s): Luca Ferrari, Enrico Pirozzi
  • Release date: October 2020
  • Publisher(s): Packt Publishing
  • ISBN: 9781838985288