Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Learn PostgreSQL
  • Table Of Contents Toc
Learn PostgreSQL

Learn PostgreSQL - Second Edition

By : Ferrari, Enrico Pirozzi
4.4 (33)
Buy this Book
close
close
Learn PostgreSQL

Learn PostgreSQL

4.4 (33)
By: Ferrari, Enrico Pirozzi
Buy this Book

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.
Table of Contents (22 chapters)
close
close
close
Preface
Icon
Preface
Icon
Who this book is for
Icon
What this book covers
Icon
To get the most out of this book
Icon
Get in touch
1
Introduction to PostgreSQL
Icon
Introduction to PostgreSQL
Icon
Technical requirements
Icon
PostgreSQL at a glance
Icon
Exploring PostgreSQL terminology
Icon
Installing PostgreSQL
Icon
Summary
Icon
References
Icon
Learn more on Discord
Lock Free Chapter
2
Getting to Know Your Cluster
chevron up
Icon
Getting to Know Your Cluster
Icon
Technical requirements
Icon
Managing your cluster
Icon
Connecting to the cluster
Icon
Solving common connection problems
Icon
Exploring the disk layout of PGDATA
Icon
Exploring configuration files and parameters
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
3
Managing Users and Connections
Icon
Managing Users and Connections
Icon
Technical requirements
Icon
Introduction to users and groups
Icon
Managing roles
Icon
Managing incoming connections at the role level
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
4
Basic Statements
Icon
Basic Statements
Icon
Technical requirements
Icon
Creating and managing databases
Icon
Managing tables
Icon
Understanding basic table manipulation statements
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
5
Advanced Statements
Icon
Advanced Statements
Icon
Technical requirements
Icon
Exploring the SELECT statement
Icon
Using the like clause
Icon
Using ilike
Icon
Using distinct
Icon
Using limit and offset
Icon
Using subqueries
Icon
Learning about joins
Icon
Aggregate functions
Icon
Using UPSERT
Icon
Learning the RETURNING clause for INSERT
Icon
Exploring CTEs
Icon
CTE in PostgreSQL since version 12
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
6
Window Functions
Icon
Window Functions
Icon
Technical requirements
Icon
Using basic statement window functions
Icon
Using advanced statement window functions
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
7
Server-Side Programming
Icon
Server-Side Programming
Icon
Technical requirements
Icon
Exploring data types
Icon
The NoSQL data type
Icon
Exploring functions and languages
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
8
Triggers and Rules
Icon
Triggers and Rules
Icon
Technical requirements
Icon
Exploring rules in PostgreSQL
Icon
Managing triggers in PostgreSQL
Icon
Event triggers
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
9
Partitioning
Icon
Partitioning
Icon
Technical requirements
Icon
Basic concepts
Icon
Exploring declarative partitioning
Icon
The default partition
Icon
Partitioning and tablespaces
Icon
A simple case study
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
10
Users, Roles, and Database Security
Icon
Users, Roles, and Database Security
Icon
Technical requirements
Icon
Understanding roles
Icon
ACLs
Icon
Granting and revoking permissions
Icon
RLS
Icon
Role password encryption
Icon
SSL connections
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
11
Transactions, MVCC, WALs, and Checkpoints
Icon
Transactions, MVCC, WALs, and Checkpoints
Icon
Technical requirements
Icon
Introducing transactions
Icon
Transaction isolation levels
Icon
Explaining MVCC
Icon
Savepoints
Icon
Deadlocks
Icon
How PostgreSQL handles persistency and consistency: WALs
Icon
VACUUM
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
12
Extending the Database – the Extension Ecosystem
Icon
Extending the Database – the Extension Ecosystem
Icon
Technical requirements
Icon
Introducing extensions
Icon
Managing extensions
Icon
Exploring the PGXN client
Icon
Installing extensions
Icon
Creating your own extension
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
13
Query Tuning, Indexes, and Performance Optimization
Icon
Query Tuning, Indexes, and Performance Optimization
Icon
Technical requirements
Icon
Execution of a statement
Icon
Indexes
Icon
The EXPLAIN statement
Icon
Examples of query tuning
Icon
ANALYZE and how to update statistics
Icon
Auto-explain
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
14
Logging and Auditing
Icon
Logging and Auditing
Icon
Technical requirements
Icon
Introduction to logging
Icon
Extracting information from logs – pgBadger
Icon
Implementing auditing
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
15
Backup and Restore
Icon
Backup and Restore
Icon
Technical requirements
Icon
Introducing types of backups and restores
Icon
Exploring logical backups
Icon
Exploring physical backups
Icon
Basic concepts behind PITR
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
16
Configuration and Monitoring
Icon
Configuration and Monitoring
Icon
Technical requirements
Icon
Cluster configuration
Icon
Monitoring the cluster
Icon
Advanced statistics with pg_stat_statements
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
17
Physical Replication
Icon
Physical Replication
Icon
Technical requirements
Icon
Exploring basic replication concepts
Icon
Managing streaming replication
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
18
Logical Replication
Icon
Logical Replication
Icon
Technical requirements
Icon
Understanding the basic concepts of logical replication
Icon
Comparing logical replication and physical replication
Icon
Exploring a logical replication setup and new logical replication features on PostgreSQL 16
Icon
Summary
Icon
Verify your knowledge
Icon
References
Icon
Learn more on Discord
19
Useful Tools and Extensions
Icon
Useful Tools and Extensions
Icon
Technical requirements
Icon
Exploring the pg_trgm extension
Icon
Using foreign data wrappers and the postgres_fdw extension
Icon
Disaster recovery with pgbackrest
Icon
The postgresql.conf file
Icon
The pgbackrest.conf file
Icon
Migrating from MySQL/MariaDB to PostgreSQL using pgloader
Icon
Summary
Icon
Verify your knowledge
Icon
References
20
Other Books You May Enjoy
Icon
Other Books You May Enjoy
21
Index
Icon
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
CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
Learn PostgreSQL
End of Section 2
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist download Download options
font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon