Book Image

Learn PostgreSQL

By : Luca Ferrari, Enrico Pirozzi
Book Image

Learn PostgreSQL

By: Luca Ferrari, Enrico Pirozzi

Overview of this book

PostgreSQL is one of the fastest-growing open source object-relational database management systems (DBMS) in the world. As well as being easy to use, it’s scalable and highly efficient. In this book, you’ll explore PostgreSQL 12 and 13 and learn how to build database solutions using it. Complete with hands-on tutorials, this guide will teach you how to achieve the right database design required for a reliable environment. You'll learn how to install and configure a PostgreSQL server and even manage users and connections. The book then progresses to key concepts of relational databases, before taking you through the Data Definition Language (DDL) and commonly used DDL commands. To build on your skills, you’ll understand how to interact with the live cluster, create database objects, and use tools to connect to the live cluster. You’ll then get to grips with creating tables, building indexes, and designing your database schema. Later, you'll explore the Data Manipulation Language (DML) and server-side programming capabilities of PostgreSQL using PL/pgSQL, before learning how to monitor, test, and troubleshoot your database application to ensure high-performance and reliability. By the end of this book, you'll be well-versed with the Postgres database and be able to set up your own PostgreSQL instance and use it to build robust solutions.

Related Content you might be interested in

Current Title:

Small book image
Learn PostgreSQL
Luca Ferrari | Enrico Pirozzi
Oct, 2020 | 650 pages
Small book image
PostgreSQL 11 Server Side Programming Quick Start Guide
Luca Ferrari
Nov, 2018 | 260 pages
Small book image
Mastering PostgreSQL 15
HansJrgen Schönig
Jan, 2023 | 522 pages
Small book image
Learning PostgreSQL 11
Salahaldin Juba | Andrey Volkov
Jan, 2019 | 556 pages
Table of Contents (27 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Get in touch
1
Section 1: Getting Started
Section 1: Getting Started
Free Chapter
2
Introduction to PostgreSQL
Introduction to PostgreSQL
Technical requirements
PostgreSQL at a glance
Exploring PostgreSQL terminology
Installing PostgreSQL 12 or higher
Summary
References
3
Getting to Know Your Cluster
Getting to Know Your Cluster
Technical requirements
Managing your cluster
Connecting to the cluster
Exploring the disk layout of PGDATA
Exploring configuration files and parameters
Summary
References
4
Managing Users and Connections
Managing Users and Connections
Introduction to users and groups
Managing roles
Managing incoming connections at the role level
Summary
References
5
Section 2: Interacting with the Database
Section 2: Interacting with the Database
6
Basic Statements
Basic Statements
Technical requirements
Setting up our developing environment
Creating and managing databases
Managing tables
Understanding basic table manipulation statements
Summary
References
7
Advanced Statements
Advanced Statements
Exploring the SELECT statement
Using UPSERT
Exploring CTEs
Summary
References
8
Window Functions
Window Functions
Using basic statement window functions
Using advanced statement window functions
Summary
References
9
Server-Side Programming
Server-Side Programming
Exploring data types
Exploring functions and languages
Summary
References
10
Triggers and Rules
Triggers and Rules
Exploring rules in PostgreSQL
Managing triggers in PostgreSQL
Event triggers
Summary
References
11
Partitioning
Partitioning
Basic concepts
Exploring partitioning using inheritance
Exploring declarative partitioning
Summary
References
12
Section 3: Administering the Cluster
Section 3: Administering the Cluster
13
Users, Roles, and Database Security
Users, Roles, and Database Security
Understanding roles
Access control lists
Granting and revoking permissions
Row-level security
Role password encryption
SSL connections
Summary
References
14
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
References
15
Extending the Database - the Extension Ecosystem
Extending the Database - the Extension Ecosystem
Introducing extensions
Managing extensions
Exploring the PGXN client
Installing extensions
Creating your own extension
Summary
References
16
Indexes and Performance Optimization
Indexes and Performance Optimization
Technical requirements
Execution of a statement
Indexes
The EXPLAIN statement
An example of query tuning
ANALYZE and how to update statistics
Auto-explain
Summary
References
17
Logging and Auditing
Logging and Auditing
Technical requirements
Introduction to logging
Extracting information from logs – PgBadger
Implementing auditing
Summary
References
18
Backup and Restore
Backup and Restore
Technical requirements
Introducing various types of backups and restores
Exploring logical backups
Exploring physical backups
Summary
Further reading
19
Configuration and Monitoring
Configuration and Monitoring
Technical requirements
Cluster configuration
Monitoring the cluster
Advanced statistics with pg_stat_statements
Summary
Further Reading
20
Section 4: Replication
Section 4: Replication
21
Physical Replication
Physical Replication
Exploring basic concepts
Learning WAL archiving and PITR
Managing streaming replication
Summary
References
22
Logical Replication
Logical Replication
Understanding basic concepts
Exploring logical replication setup
Summary
References
23
Section 5: The PostegreSQL Ecosystem
Section 5: The PostegreSQL Ecosystem
24
Useful Tools and Extensions
Useful Tools and Extensions
Exploring the pg_trgm extension
Using foreign data wrappers and the postgres_fdw extension
Exploring the btree_gin extension
Managing the pgbackrest tool
Summary
References
25
Toward PostgreSQL 13
Toward PostgreSQL 13
Introducing PostgreSQL 13's new features
Upgrading to PostgreSQL 13
Summary
References
26
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Managing incoming connections at the role level

When a new connection is established to a cluster, PostgreSQL validates the incoming request at the role level. The fact that the role has the LOGIN property is not enough for it to open a new connection to any database within the cluster. This is because PostgreSQL checks the incoming connection request against a kind of firewall table, formerly know as host-based access, that is defined within the pg_hba.conf file.

If the table states that the role can open the connection to the specified database, the connection is granted (assuming it has the LOGIN property); otherwise, it is rejected.

Every time you modify the pg_hba.conf file, you need to instruct the cluster to reload the new rules via a HUP signal or by means of a reload command in pg_ctl. Therefore, the usual workflow when dealing with pg_hba.conf is similar to the following:

$ $EDITOR $PGDATA/pg_hba.conf
... modify the file as you wish ...

$ sudo -u postgres pg_ctl reload -D $PGDATA...
Previous Section
End of Section 4
Next Section