Learn PostgreSQL: Use, manage and build secure and scalable databases with PostgreSQL 16. 2 Ed

Preface....22

Who this book is for....24

What this book covers....25

To get the most out of this book....29

Get in touch....32

Introduction to PostgreSQL....35

Technical requirements....37

PostgreSQL at a glance....37

A brief history of PostgreSQL....40

What’s new in PostgreSQL 16?....41

PostgreSQL release policy, version numbers, and life cycle....42

Exploring PostgreSQL terminology....44

Installing PostgreSQL....50

What to install....51

Installing PostgreSQL from binary packages....52

Using the book’s Docker images....54

Installing PostgreSQL on GNU/Linux Debian, Ubuntu, and derivatives....56

Installing PostgreSQL on Fedora Linux....58

Installing PostgreSQL on FreeBSD....60

Installing PostgreSQL from sources....61

Installing PostgreSQL via pgenv....63

Summary....65

References....66

Learn more on Discord....66

Getting to Know Your Cluster....68

Technical requirements....69

Managing your cluster....69

pg_ctl....70

PostgreSQL processes....79

Connecting to the cluster....82

The template databases....84

The psql command-line client....86

Entering SQL statements via psql....89

A glance at the psql commands....91

Introducing the connection string....93

Solving common connection problems....94

Database “foo” does not exist....95

Connection refused....95

No pg_hba.conf entry....96

Exploring the disk layout of PGDATA....98

Objects in the PGDATA directory....100

Tablespaces....102

Exploring configuration files and parameters....104

Summary....107

Verify your knowledge....107

References....108

Learn more on Discord....109

Managing Users and Connections....110

Technical requirements....111

Introduction to users and groups....111

Managing roles....113

Creating new roles....114

Role passwords, connections, and availability....115

Using a role as a group....118

Removing an existing role....120

Inspecting existing roles....122

Managing incoming connections at the role level....125

The syntax of pg_hba.conf....127

Order of rules in pg_hba.conf....129

Merging multiple rules into a single one....131

Using groups instead of single roles....132

Using files instead of single roles....134

Inspecting pg_hba.conf rules....135

Including other files in pg_hba.conf....136

Summary....137

Verify your knowledge....137

References....138

Learn more on Discord....139

Basic Statements....140

Technical requirements....141

Using the Docker image....141

Connecting the database....142

Creating and managing databases....143

Creating a database....143

Managing databases....144

Introducing schemas....144

PostgreSQL and the public schema....145

The search_path variable....146

The correct way to start working....147

Listing all tables....148

Making a new database from a modified template....149

Dropping tables and databases....151

Dropping tables....151

Dropping databases....152

Making a database copy....152

Confirming the database size....153

The psql method....153

The SQL method....154

Behind the scenes of database creation....155

Managing tables....158

The EXISTS option....160

Managing temporary tables....162

Managing unlogged tables....164

Creating a table....165

Understanding basic table manipulation statements....167

Inserting and selecting data....168

NULL values....172

Sorting with NULL values....175

Creating a table starting from another table....177

Updating data....178

Deleting data....179

Summary....182

Verify your knowledge....182

References....184

Learn more on Discord....185

Advanced Statements....186

Technical requirements....186

Exploring the SELECT statement....187

Using the like clause....187

Using ilike....190

Using distinct....190

Using limit and offset....194

Using subqueries....196

Subqueries and the IN/NOT IN condition....196

Subqueries and the EXISTS/NOT EXISTS condition....199

Learning about joins....201

Using INNER JOIN....202

INNER JOIN versus EXISTS/IN....204

Using LEFT JOINS....204

Using RIGHT JOIN....208

Using FULL OUTER JOIN....210

Using LATERAL JOIN....213

Aggregate functions....214

UNION/UNION ALL....218

EXCEPT/INTERSECT....220

Using UPSERT....222

UPSERT – the PostgreSQL way....222

Learning the RETURNING clause for INSERT....225

Returning tuples out of queries....226

UPDATE related to multiple records....227

MERGE....229

Exploring UPDATE ... RETURNING....231

Exploring DELETE ... RETURNING....231

Exploring CTEs....232

CTE concept....232

CTE in PostgreSQL since version 12....233

CTE – use cases....234

Query recursion....237

Recursive CTEs....237

Summary....239

Verify your knowledge....240

References....242

Learn more on Discord....242

Window Functions....244

Technical requirements....245

Using basic statement window functions....245

Using the PARTITION BY function and WINDOW clause....247

Introducing some useful functions....249

The ROW_NUMBER function....249

The ORDER BY clause....250

FIRST_VALUE....251

LAST_VALUE....252

RANK....252

DENSE_RANK....253

The LAG and LEAD functions....254

The CUME_DIST function....257

The NTILE function....257

Using advanced statement window functions....259

The frame clause....260

ROWS BETWEEN start_point and end_point....260

RANGE BETWEEN start_point and end_point....268

Summary....273

Verify your knowledge....274

References....275

Learn more on Discord....276

Server-Side Programming....277

Technical requirements....278

Exploring data types....278

The concept of extensibility....279

Standard data types....279

Boolean data type....280

Numeric data type....282

Integer types....283

Numbers with a fixed precision data type....283

Numbers with an arbitrary precision data type....284

Character data type....286

Chars with fixed-length data types....287

Chars with variable length with a limit data types....288

Chars with a variable length without a limit data types....290

Date/timestamp data types....291

Date data types....291

Timestamp data types....296

The NoSQL data type....298

The hstore data type....299

The JSON data type....302

Exploring functions and languages....307

Functions....307

SQL functions....308

Basic functions....308

SQL functions returning a set of elements....310

SQL functions returning a table....311

Polymorphic SQL functions....313

PL/pgSQL functions....314

First overview....315

Dropping functions....318

Declaring function parameters....318

IN/OUT parameters....319

Function volatility categories....322

Control structure....325

Conditional statements....325

IF statements....325

CASE statements....327

Loop statements....329

The record type....332

Exception handling statements....333

Security definer....335

Summary....337

Verify your knowledge....338

References....339

Learn more on Discord....339

Triggers and Rules....341

Technical requirements....342

Exploring rules in PostgreSQL....342

Understanding the OLD and NEW variables....343

Rules on INSERT....345

The ALSO option....345

The INSTEAD OF option....347

Rules on DELETE/UPDATE....350

Creating the new_tags table....351

Creating two tables....351

Managing rules on INSERT, DELETE, and UPDATE events....353

INSERT rules....354

DELETE rules....355

UPDATE rules....357

Managing triggers in PostgreSQL....359

Trigger syntax....361

Triggers on INSERT....363

The TG_OP variable....368

Triggers on UPDATE / DELETE....369

Event triggers....375

An example of an event trigger....377

Summary....380

Verify your knowledge....380

References....381

Learn more on Discord....382

Partitioning....383

Technical requirements....383

Basic concepts....384

Range partitioning....386

List partitioning....387

Hash partitioning....389

Table inheritance....391

Dropping tables....395

Exploring declarative partitioning....396

List partitioning....396

Range partitioning....399

Partition maintenance....402

Attaching a new partition....403

Detaching an existing partition....403

Attaching an existing table to the parent table....404

The default partition....405

Partitioning and tablespaces....407

A simple case study....410

Summary....415

Verify your knowledge....416

References....417

Learn more on Discord....418

Users, Roles, and Database Security....419

Technical requirements....420

Understanding roles....420

Properties related to new objects....421

Properties related to superusers....422

Properties related to replication....423

Properties related to RLS....423

Changing properties of existing roles: the ALTER ROLE statement....424

Renaming an existing role....425

SESSION_USER versus CURRENT_USER....426

Per-role configuration parameters....427

Inspecting roles....429

Roles that inherit from other roles....432

Understanding how privileges are resolved....436

Role inheritance overview....440

ACLs....440

Default ACLs....446

Knowing the default ACLs....449

Granting and revoking permissions....450

Permissions related to tables....452

Column-based permissions....453

Permissions related to sequences....458

Permissions related to schemas....460

ALL objects in the schema....464

Permissions related to programming languages....464

Permissions related to routines....466

Permissions related to databases....467

Other GRANT and REVOKE statements....468

Assigning the object owner....469

Inspecting ACLs....470

RLS....471

Role password encryption....478

SSL connections....479

Configuring the cluster for SSL....480

Connecting to the cluster via SSL....481

Summary....483

Verify your knowledge....484

References....485

Learn more on Discord....486

Transactions, MVCC, WALs, and Checkpoints....487

Technical requirements....488

Introducing transactions....488

Comparing implicit and explicit transactions....491

Time within transactions....498

More about transaction identifiers – the XID wraparound problem....500

Virtual and real transaction identifiers....502

Multi-version concurrency control....505

Transaction isolation levels....512

READ UNCOMMITTED....515

READ COMMITTED....515

REPEATABLE READ....516

SERIALIZABLE....516

Explaining MVCC....519

Savepoints....522

Deadlocks....525

How PostgreSQL handles persistency and consistency: WALs....529

WALs....529

WALs as a rescue method in the event of a crash....536

Checkpoints....537

Checkpoint configuration parameters....540

checkpoint_timeout and max_wal_size....540

Checkpoint throttling....543

Manually issuing a checkpoint....544

VACUUM....545

Manual VACUUM....546

Automatic VACUUM....553

Summary....558

Verify your knowledge....558

References....560

Learn more on Discord....560

Extending the Database – the Extension Ecosystem....562

Technical requirements....563

Introducing extensions....563

The extension ecosystem....565

Extension components....567

The control file....569

The script file....570

Managing extensions....571

Creating an extension....572

Viewing installed extensions....574

Finding out available extension versions....575

Altering an existing extension....576

Removing an existing extension....580

Exploring the PGXN client....581

Installing pgxnclient on Debian GNU/Linux and derivatives....582

Installing pgxnclient on Fedora Linux and Red Hat-based distributions....583

Installing pgxnclient on FreeBSD....583

Installing pgxnclient from sources....584

The pgxnclient command-line interface....585

Installing extensions....587

Installing the extension via pgxnclient....587

Installing the extension manually....589

Using the installed extension....592

Removing an installed extension....594

Removing an extension via pgxnclient....595

Removing a manually compiled extension....596

Creating your own extension....596

Defining an example extension....597

Creating extension files....598

Installing the extension....600

Creating an extension upgrade....601

Performing an extension upgrade....603

Summary....604

Verify your knowledge....605

References....606

Learn more on Discord....606

Query Tuning, Indexes, and Performance Optimization....608

Technical requirements....609

Execution of a statement....609

Execution stages....611

The optimizer....613

Nodes that the optimizer uses....616

Sequential nodes....616

Parallel nodes....621

When does the optimizer choose a parallel plan?....623

Utility nodes....625

Node costs....626

Indexes....628

Index types....630

Creating an index....632

Inspecting indexes....635

Dropping an index....637

Invalidating an index....638

Rebuilding an index....640

The EXPLAIN statement....641

EXPLAIN output formats....644

EXPLAIN ANALYZE....646

EXPLAIN options....649

Examples of query tuning....654

ANALYZE and how to update statistics....666

Auto-explain....671

Summary....676

Verify your knowledge....677

References....678

Learn more on Discord....679

Logging and Auditing....680

Technical requirements....680

Introduction to logging....681

Where to log....683

When to log....687

What to log....692

Extracting information from logs – pgBadger....695

Installing pgBadger....695

Configuring PostgreSQL logging for pgBadger usage....696

Using pgBadger....697

Scheduling pgBadger....702

Implementing auditing....705

Installing PgAudit....707

Configuring PostgreSQL to exploit PgAudit....708

Configuring PgAudit....709

Auditing by session....710

Auditing by role....713

Summary....715

Verify your knowledge....716

References....717

Learn more on Discord....717

Backup and Restore....719

Technical requirements....720

Introducing types of backups and restores....720

Exploring logical backups....723

Dumping a single database....725

Restoring a single database....730

Limiting the amount of data to backup....735

Compression....737

Dump formats and pg_restore....738

Performing a selective restore....742

Dumping a whole cluster....746

Parallel backups....747

Backup automation....750

The COPY command....753

Exploring physical backups....758

Performing a manual physical backup....760

pg_verifybackup....763

Starting the cloned cluster....764

Restoring from a physical backup....765

Basic concepts behind PITR....766

Summary....768

Verify your knowledge....769

References....770

Learn more on Discord....771

Configuration and Monitoring....772

Technical requirements....773

Cluster configuration....773

Inspecting all the configuration parameters....775

Finding configuration errors....778

Nesting configuration files....780

Configuration contexts....781

Main configuration settings....783

WAL settings....783

Memory-related settings....786

Process information settings....788

Networking-related settings....788

Archive and replication settings....790

Vacuum and autovacuum-related settings....791

Optimizer settings....791

Statistics collector....791

Modifying the configuration from a live system....792

Configuration generators....793

Monitoring the cluster....796

Information about running queries and sessions....797

Inspecting locks....798

Inspecting databases....800

Inspecting tables and indexes....801

More statistics....803

Advanced statistics with pg_stat_statements....805

Installing the pg_stat_statements extension....805

Using pg_stat_statements....806

Resetting data collected from pg_stat_statements....808

Tuning pg_stat_statements....808

Summary....809

Verify your knowledge....810

References....811

Learn more on Discord....812

Physical Replication....813

Technical requirements....814

Exploring basic replication concepts....816

Physical replication and WALs....817

The wal_level directive....817

Preparing the environment setup for streaming replication....818

Managing streaming replication....820

Basic concepts of streaming replication....821

Asynchronous replication environment....823

The wal_keep_segments option....824

The slot way....826

The pg_basebackup command....827

Asynchronous replication....828

Replica monitoring....831

Synchronous replication....833

PostgreSQL settings....833

Cascading replication....836

Delayed replication....840

Promoting a replica server to a primary....841

Summary....842

Verify your knowledge....843

References....844

Learn more on Discord....845

Logical Replication....846

Technical requirements....846

Understanding the basic concepts of logical replication....847

Comparing logical replication and physical replication....851

Exploring a logical replication setup and new logical replication features on PostgreSQL 16....853

Logical replication environment settings....853

The replica role....854

Primary server – postgresql.conf....855

Replica server – postgresql.conf....856

The pg_hba.conf file....857

Logical replication setup....858

Monitoring logical replication....861

Read-only versus write-allowed....863

DDL commands....871

Disabling logical replication....874

Making a logical replication using a physical replication instance....875

Summary....882

Verify your knowledge....882

References....883

Learn more on Discord....884

Useful Tools and Extensions....885

Technical requirements....886

Exploring the pg_trgm extension....886

Using foreign data wrappers and the postgres_fdw extension....890

Disaster recovery with pgbackrest....894

Basic concepts....895

Environment set up....896

The exchange of public keys....896

Installing pgbackrest....899

Configuring pgbackrest....899

The repository configuration....900

Using pgbackrest with object store support....904

The PostgreSQL server configuration....905

The postgresql.conf file....905

The pgbackrest.conf file....906

Creating and managing continuous backups....907

Creating the stanza....907

Checking the stanza....908

Managing base backups....909

Managing PITR....912

Migrating from MySQL/MariaDB to PostgreSQL using pgloader....915

Summary....920

Verify your knowledge....920

References....921

Other Books You May Enjoy....923

Index....927

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.