Book Image

Learn PostgreSQL - Second Edition

By : Luca Ferrari, Enrico Pirozzi
1 (2)
Book Image

Learn PostgreSQL - Second Edition

1 (2)
By: Luca Ferrari, Enrico Pirozzi

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
Learn PostgreSQL - Second Edition
Luca Ferrari | Enrico Pirozzi
Oct, 2023 | 744 pages
Small book image
PostgreSQL 11 Server Side Programming Quick Start Guide
Luca Ferrari
Nov, 2018 | 260 pages
Small book image
PostgreSQL 16 Administration Cookbook
Vibhor Kumar | Dimitrios Angelakos | Gianni Ciolli | Boris Mejias | Simon Riggs
Dec, 2023 | 636 pages
Small book image
Mastering PostgreSQL 15
HansJuumlrgen Schoumlnig
Jan, 2023 | 522 pages
Table of Contents (22 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Get in touch
1
Introduction to PostgreSQL
Introduction to PostgreSQL
Technical requirements
PostgreSQL at a glance
Exploring PostgreSQL terminology
Installing PostgreSQL
Summary
References
Learn more on Discord
Free Chapter
2
Getting to Know Your Cluster
Getting to Know Your Cluster
Technical requirements
Managing your cluster
Connecting to the cluster
Solving common connection problems
Exploring the disk layout of PGDATA
Exploring configuration files and parameters
Summary
Verify your knowledge
References
Learn more on Discord
3
Managing Users and Connections
Managing Users and Connections
Technical requirements
Introduction to users and groups
Managing roles
Managing incoming connections at the role level
Summary
Verify your knowledge
References
Learn more on Discord
4
Basic Statements
Basic Statements
Technical requirements
Creating and managing databases
Managing tables
Understanding basic table manipulation statements
Summary
Verify your knowledge
References
Learn more on Discord
5
Advanced Statements
Advanced Statements
Technical requirements
Exploring the SELECT statement
Using the like clause
Using ilike
Using distinct
Using limit and offset
Using subqueries
Learning about joins
Aggregate functions
Using UPSERT
Learning the RETURNING clause for INSERT
Exploring CTEs
CTE in PostgreSQL since version 12
Summary
Verify your knowledge
References
Learn more on Discord
6
Window Functions
Window Functions
Technical requirements
Using basic statement window functions
Using advanced statement window functions
Summary
Verify your knowledge
References
Learn more on Discord
7
Server-Side Programming
Server-Side Programming
Technical requirements
Exploring data types
The NoSQL data type
Exploring functions and languages
Summary
Verify your knowledge
References
Learn more on Discord
8
Triggers and Rules
Triggers and Rules
Technical requirements
Exploring rules in PostgreSQL
Managing triggers in PostgreSQL
Event triggers
Summary
Verify your knowledge
References
Learn more on Discord
9
Partitioning
Partitioning
Technical requirements
Basic concepts
Exploring declarative partitioning
The default partition
Partitioning and tablespaces
A simple case study
Summary
Verify your knowledge
References
Learn more on Discord
10
Users, Roles, and Database Security
Users, Roles, and Database Security
Technical requirements
Understanding roles
ACLs
Granting and revoking permissions
RLS
Role password encryption
SSL connections
Summary
Verify your knowledge
References
Learn more on Discord
11
Transactions, MVCC, WALs, and Checkpoints
Transactions, MVCC, WALs, and Checkpoints
Technical requirements
Introducing transactions
Transaction isolation levels
Explaining MVCC
Savepoints
Deadlocks
How PostgreSQL handles persistency and consistency: WALs
VACUUM
Summary
Verify your knowledge
References
Learn more on Discord
12
Extending the Database – the Extension Ecosystem
Extending the Database – the Extension Ecosystem
Technical requirements
Introducing extensions
Managing extensions
Exploring the PGXN client
Installing extensions
Creating your own extension
Summary
Verify your knowledge
References
Learn more on Discord
13
Query Tuning, Indexes, and Performance Optimization
Query Tuning, Indexes, and Performance Optimization
Technical requirements
Execution of a statement
Indexes
The EXPLAIN statement
Examples of query tuning
ANALYZE and how to update statistics
Auto-explain
Summary
Verify your knowledge
References
Learn more on Discord
14
Logging and Auditing
Logging and Auditing
Technical requirements
Introduction to logging
Extracting information from logs – pgBadger
Implementing auditing
Summary
Verify your knowledge
References
Learn more on Discord
15
Backup and Restore
Backup and Restore
Technical requirements
Introducing types of backups and restores
Exploring logical backups
Exploring physical backups
Basic concepts behind PITR
Summary
Verify your knowledge
References
Learn more on Discord
16
Configuration and Monitoring
Configuration and Monitoring
Technical requirements
Cluster configuration
Monitoring the cluster
Advanced statistics with pg_stat_statements
Summary
Verify your knowledge
References
Learn more on Discord
17
Physical Replication
Physical Replication
Technical requirements
Exploring basic replication concepts
Managing streaming replication
Summary
Verify your knowledge
References
Learn more on Discord
18
Logical Replication
Logical Replication
Technical requirements
Understanding the basic concepts of logical replication
Comparing logical replication and physical replication
Exploring a logical replication setup and new logical replication features on PostgreSQL 16
Summary
Verify your knowledge
References
Learn more on Discord
19
Useful Tools and Extensions
Useful Tools and Extensions
Technical requirements
Exploring the pg_trgm extension
Using foreign data wrappers and the postgres_fdw extension
Disaster recovery with pgbackrest
The postgresql.conf file
The pgbackrest.conf file
Migrating from MySQL/MariaDB to PostgreSQL using pgloader
Summary
Verify your knowledge
References
20
Other Books You May Enjoy
Other Books You May Enjoy
21
Index
Index

Getting to Know Your Cluster

To be a proficient user and administrator of a PostgreSQL cluster, you first must know and understand how PostgreSQL works. A database system is a very complex beast, and PostgreSQL, being an enterprise-level Database Management System (DBMS), is in no way a simple software system. However, thanks to very good design and implementation, once you understand the basic concepts and terminology of PostgreSQL, things will quickly become comprehensive and clear.

This chapter will continue from the foundation of the previous chapter and introduce you to some other PostgreSQL terminology and concepts, as well as teaching you how to interact with the cluster. You will also be introduced to the psql client, which ships with PostgreSQL and is the recommended way to connect to your database. You are free to use any SQL client that can connect to PostgreSQL, and all the code and examples shown in this chapter will run out of the box in any other client as well, but we recommend that you take some time to learn psql. Shipped with PostgreSQL, psql is guaranteed to work in any situation and is the default way to connect to a cluster. psql is a text-only client; if you are more comfortable using a graphical client, you can have a look at pgAdmin4, one of the most famous PostgreSQL graphical clients.

This chapter covers the following main topics:

  • Managing your cluster
  • Connecting to the cluster
  • Exploring the disk layout of PGDATA
  • Exploring configuration files and parameters
Previous Section
End of Section 1
Next Section