Learn PostgreSQL - Second Edition

Learn PostgreSQL - Second Edition

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

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

This new edition will help you learn PostgreSQL from scratch with the latest version, providing a complete focused view on aspects like configuration, high performance, partitioning, backup, server-side programming and replication. Purchase of the print or Kindle book includes a free eBook in PDF format.

Key Features

  • Learn the fundamentals of PostgreSQL 16, including SQL statements, replication, and security
  • Enhance your learning journey with the provided Docker images for practical hands-on exercises and tests at the end of each chapter
  • Get new and improved examples, use-cases, and scenarios specifically for concepts like partitioning, replication, back-up and restore, cluster configuration, monitoring and others

Book Description

The latest edition of this PostgreSQL book will help you to start using PostgreSQL from absolute scratch, helping you to quickly understand the internal workings of the database. With a structured approach and practical examples, go on a journey that covers the basics, from SQL statements and how to run server-side programs, to configuring, managing, securing, and optimizing database performance.

This new edition will not only help you get to grips with all the recent changes within the PostgreSQL ecosystem but will also dig deeper into concepts like partitioning and replication with a fresh set of examples. The book is also equipped with Docker images for each chapter which makes the learning experience faster and easier. Starting with the absolute basics of databases, the book sails through to advanced concepts like window functions, logging, auditing, extending the database, configuration, partitioning, and replication. It will also help you seamlessly migrate your existing database system to PostgreSQL and contains a dedicated chapter on disaster recovery. Each chapter ends with practice questions to test your learning at regular intervals.

By the end of this book, you will be able to install, configure, manage, and develop applications against a PostgreSQL database.

What you will learn

  • Gain a deeper understanding of PostgreSQL internals like transactions, MVCC, security and replication
  • Enhance data management with PostgreSQL’s latest partitioning features
  • Choose the right replication strategy for your database
  • See concrete examples of how to migrate data from another database, perform backups and restores, monitor your PostgreSQL installation and more
  • Ensure security and compliance with schemas and user privileges
  • Create customized database functions and extensions
  • Get to grips with server-side programming, window functions, and triggers

Who this book is for

Learning PostgresSQL 16 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. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
    4. Get in touch
  2. Introduction to PostgreSQL
    1. Technical requirements
    2. PostgreSQL at a glance
      1. A brief history of PostgreSQL
      2. What’s new in PostgreSQL 16?
      3. PostgreSQL release policy, version numbers, and life cycle
    3. Exploring PostgreSQL terminology
    4. Installing PostgreSQL
      1. What to install
      2. Installing PostgreSQL from binary packages
        1. Using the book’s Docker images
        2. Installing PostgreSQL on GNU/Linux Debian, Ubuntu, and derivatives
        3. Installing PostgreSQL on Fedora Linux
        4. Installing PostgreSQL on FreeBSD
      3. Installing PostgreSQL from sources
      4. Installing PostgreSQL via pgenv
    5. Summary
    6. References
    7. Learn more on Discord
  3. 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
        1. Connection refused
        2. No pg_hba.conf entry
    5. Exploring the disk layout of PGDATA
      1. Objects in the PGDATA directory
        1. Tablespaces
    6. Exploring configuration files and parameters
    7. Summary
    8. Verify your knowledge
    9. References
    10. Learn more on Discord
  4. Managing Users and Connections
    1. Technical requirements
    2. Introduction to users and groups
    3. 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
    4. 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
      6. Inspecting pg_hba.conf rules
      7. Including other files in pg_hba.conf
    5. Summary
    6. Verify your knowledge
    7. References
    8. Learn more on Discord
  5. Basic Statements
    1. Technical requirements
      1. Using the Docker image
      2. Connecting the database
    2. Creating and managing databases
      1. Creating a database
      2. Managing databases
      3. Introducing schemas
      4. PostgreSQL and the public schema
      5. The search_path variable
      6. The correct way to start working
      7. Listing all tables
      8. Making a new database from a modified template
      9. Dropping tables and databases
      10. Dropping tables
      11. Dropping databases
      12. Making a database copy
      13. Confirming the database size
      14. The psql method
      15. The SQL method
      16. Behind the scenes of database creation
    3. Managing tables
      1. The EXISTS option
      2. Managing temporary tables
      3. Managing unlogged tables
      4. Creating a table
    4. Understanding basic table manipulation statements
      1. Inserting and selecting data
      2. NULL values
      3. Sorting with NULL values
      4. Creating a table starting from another table
      5. Updating data
      6. Deleting data
    5. Summary
    6. Verify your knowledge
    7. References
    8. Learn more on Discord
  6. Advanced Statements
    1. Technical requirements
    2. Exploring the SELECT statement
    3. Using the like clause
    4. Using ilike
    5. Using distinct
    6. Using limit and offset
    7. Using subqueries
      1. Subqueries and the IN/NOT IN condition
      2. Subqueries and the EXISTS/NOT EXISTS condition
    8. Learning about joins
      1. Using INNER JOIN
      2. INNER JOIN versus EXISTS/IN
      3. Using LEFT JOINS
      4. Using RIGHT JOIN
      5. Using FULL OUTER JOIN
      6. Using LATERAL JOIN
    9. Aggregate functions
      1. UNION/UNION ALL
      2. EXCEPT/INTERSECT
    10. Using UPSERT
      1. UPSERT – the PostgreSQL way
    11. Learning the RETURNING clause for INSERT
      1. Returning tuples out of queries
      2. UPDATE related to multiple records
      3. MERGE
      4. Exploring UPDATE ... RETURNING
      5. Exploring DELETE ... RETURNING
    12. Exploring CTEs
      1. CTE concept
    13. CTE in PostgreSQL since version 12
      1. CTE – use cases
      2. Query recursion
      3. Recursive CTEs
    14. Summary
    15. Verify your knowledge
    16. References
    17. Learn more on Discord
  7. Window Functions
    1. Technical requirements
    2. 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
    3. 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
    4. Summary
    5. Verify your knowledge
    6. References
    7. Learn more on Discord
  8. Server-Side Programming
    1. Technical requirements
    2. Exploring data types
      1. The concept of extensibility
      2. Standard data types
      3. Boolean data type
      4. Numeric data type
        1. Integer types
        2. Numbers with a fixed precision data type
        3. Numbers with an arbitrary precision data type
      5. 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
      6. Date/timestamp data types
        1. Date data types
        2. Timestamp data types
    3. The NoSQL data type
      1. The hstore data type
      2. The JSON data type
    4. Exploring functions and languages
      1. Functions
      2. SQL functions
      3. Basic functions
      4. SQL functions returning a set of elements
      5. SQL functions returning a table
      6. Polymorphic SQL functions
      7. PL/pgSQL functions
      8. First overview
      9. Dropping functions
      10. Declaring function parameters
        1. IN/OUT parameters
        2. Function volatility categories
      11. Control structure
      12. Conditional statements
        1. IF statements
        2. CASE statements
      13. Loop statements
        1. The record type
      14. Exception handling statements
      15. Security definer
    5. Summary
    6. Verify your knowledge
    7. References
    8. Learn more on Discord
  9. Triggers and Rules
    1. Technical requirements
    2. 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
      4. Managing rules on INSERT, DELETE, and UPDATE events
        1. INSERT rules
        2. DELETE rules
        3. UPDATE rules
    3. Managing triggers in PostgreSQL
      1. Trigger syntax
      2. Triggers on INSERT
      3. The TG_OP variable
      4. Triggers on UPDATE / DELETE
    4. Event triggers
      1. An example of an event trigger
    5. Summary
    6. Verify your knowledge
    7. References
    8. Learn more on Discord
  10. Partitioning
    1. Technical requirements
    2. Basic concepts
      1. Range partitioning
      2. List partitioning
      3. Hash partitioning
      4. Table inheritance
        1. Dropping tables
    3. Exploring declarative partitioning
      1. List partitioning
      2. Range partitioning
      3. Partition maintenance
        1. Attaching a new partition
        2. Detaching an existing partition
        3. Attaching an existing table to the parent table
    4. The default partition
    5. Partitioning and tablespaces
    6. A simple case study
    7. Summary
    8. Verify your knowledge
    9. References
    10. Learn more on Discord
  11. Users, Roles, and Database Security
    1. Technical requirements
    2. Understanding roles
      1. Properties related to new objects
      2. Properties related to superusers
      3. Properties related to replication
      4. Properties related to RLS
      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
    3. ACLs
      1. Default ACLs
        1. Knowing the default ACLs
    4. 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 programming 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
    5. RLS
    6. Role password encryption
    7. SSL connections
      1. Configuring the cluster for SSL
      2. Connecting to the cluster via SSL
    8. Summary
    9. Verify your knowledge
    10. References
    11. Learn more on Discord
  12. 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. 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. Verify your knowledge
    11. References
    12. Learn more on Discord
  13. Extending the Database – the Extension Ecosystem
    1. Technical requirements
    2. Introducing extensions
      1. The extension ecosystem
      2. Extension components
        1. The control file
      3. The script file
    3. 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
    4. Exploring the PGXN client
      1. Installing pgxnclient on Debian GNU/Linux and derivatives
      2. Installing pgxnclient on Fedora Linux and Red Hat-based distributions
      3. Installing pgxnclient on FreeBSD
      4. Installing pgxnclient from sources
      5. The pgxnclient command-line interface
    5. 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 pgxnclient
        2. Removing a manually compiled extension
    6. 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
    7. Summary
    8. Verify your knowledge
    9. References
    10. Learn more on Discord
  14. Query Tuning, 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
        2. Parallel nodes
        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. Examples of query tuning
    6. ANALYZE and how to update statistics
    7. Auto-explain
    8. Summary
    9. Verify your knowledge
    10. References
    11. Learn more on Discord
  15. Logging and Auditing
    1. Technical requirements
    2. Introduction to logging
      1. Where to log
      2. When to log
      3. What to log
    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. Verify your knowledge
    7. References
    8. Learn more on Discord
  16. Backup and Restore
    1. Technical requirements
    2. Introducing 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 backup
      4. Compression
      5. Dump formats and pg_restore
      6. Performing a selective restore
      7. Dumping a whole cluster
      8. Parallel backups
      9. Backup automation
      10. The COPY command
    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. Basic concepts behind PITR
    6. Summary
    7. Verify your knowledge
    8. References
    9. Learn more on Discord
  17. 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 and autovacuum-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 and sessions
      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. Verify your knowledge
    7. References
    8. Learn more on Discord
  18. Physical Replication
    1. Technical requirements
    2. Exploring basic replication concepts
      1. Physical replication and WALs
        1. The wal_level directive
      2. Preparing the environment setup for streaming replication
    3. Managing streaming replication
      1. Basic concepts of streaming replication
      2. Asynchronous replication environment
      3. The wal_keep_segments option
      4. The slot way
      5. The pg_basebackup command
      6. Asynchronous replication
        1. Replica monitoring
      7. Synchronous replication
        1. PostgreSQL settings
        2. Cascading replication
        3. Delayed replication
      8. Promoting a replica server to a primary
    4. Summary
    5. Verify your knowledge
    6. References
    7. Learn more on Discord
  19. Logical Replication
    1. Technical requirements
    2. Understanding the basic concepts of logical replication
    3. Comparing logical replication and physical replication
    4. Exploring a logical replication setup and new logical replication features on PostgreSQL 16
      1. Logical replication environment settings
        1. The replica role
        2. Primary server – postgresql.conf
        3. Replica server – postgresql.conf
        4. The pg_hba.conf file
      2. Logical replication setup
      3. Monitoring logical replication
        1. Read-only versus write-allowed
      4. DDL commands
      5. Disabling logical replication
      6. Making a logical replication using a physical replication instance
    5. Summary
    6. Verify your knowledge
    7. References
    8. Learn more on Discord
  20. Useful Tools and Extensions
    1. Technical requirements
    2. Exploring the pg_trgm extension
    3. Using foreign data wrappers and the postgres_fdw extension
    4. Disaster recovery with pgbackrest
      1. Basic concepts
      2. Environment set up
        1. The exchange of public keys
      3. Installing pgbackrest
      4. Configuring pgbackrest
        1. The repository configuration
        2. Using pgbackrest with object store support
        3. The PostgreSQL server configuration
    5. The postgresql.conf file
    6. The pgbackrest.conf file
      1. Creating and managing continuous backups
        1. Creating the stanza
        2. Checking the stanza
        3. Managing base backups
        4. Managing PITR
    7. Migrating from MySQL/MariaDB to PostgreSQL using pgloader
    8. Summary
    9. Verify your knowledge
    10. References
  21. Other Books You May Enjoy
  22. Index

Product information

  • Title: Learn PostgreSQL - Second Edition
  • Author(s): Luca Ferrari, Enrico Pirozzi
  • Release date: October 2023
  • Publisher(s): Packt Publishing
  • ISBN: 9781837635641