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