Configuration and Monitoring | Learn PostgreSQL

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

Learn PostgreSQL

By : Luca Ferrari, Enrico Pirozzi

3.4 (5)

Learn PostgreSQL

3.4 (5)

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.

Preface

Preface

Who this book is for

What this book covers

To get the most out of this book

Get in touch

Section 1: Getting Started

Section 1: Getting Started

Free Chapter

Introduction to PostgreSQL

Introduction to PostgreSQL

Technical requirements

PostgreSQL at a glance

Exploring PostgreSQL terminology

Installing PostgreSQL 12 or higher

Summary

References

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

Managing Users and Connections

Managing Users and Connections

Introduction to users and groups

Managing roles

Managing incoming connections at the role level

Summary

References

Section 2: Interacting with the Database

Section 2: Interacting with the Database

Basic Statements

Basic Statements

Technical requirements

Setting up our developing environment

Creating and managing databases

Managing tables

Understanding basic table manipulation statements

Summary

References

Advanced Statements

Advanced Statements

Exploring the SELECT statement

Using UPSERT

Exploring CTEs

Summary

References

Window Functions

Window Functions

Using basic statement window functions

Using advanced statement window functions

Summary

References

Server-Side Programming

Server-Side Programming

Exploring data types

Exploring functions and languages

Summary

References

Triggers and Rules

Triggers and Rules

Exploring rules in PostgreSQL

Managing triggers in PostgreSQL

Event triggers

Summary

References

Partitioning

Partitioning

Basic concepts

Exploring partitioning using inheritance

Exploring declarative partitioning

Summary

References

Section 3: Administering the Cluster

Section 3: Administering the Cluster

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

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

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

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

Logging and Auditing

Logging and Auditing

Technical requirements

Introduction to logging

Extracting information from logs – PgBadger

Implementing auditing

Summary

References

Backup and Restore

Backup and Restore

Technical requirements

Introducing various types of backups and restores

Exploring logical backups

Exploring physical backups

Summary

Further reading

Configuration and Monitoring

Configuration and Monitoring

Technical requirements

Cluster configuration

Monitoring the cluster

Advanced statistics with pg_stat_statements

Summary

Further Reading

Section 4: Replication

Section 4: Replication

Physical Replication

Physical Replication

Exploring basic concepts

Learning WAL archiving and PITR

Managing streaming replication

Summary

References

Logical Replication

Logical Replication

Understanding basic concepts

Exploring logical replication setup

Summary

References

Section 5: The PostegreSQL Ecosystem

Section 5: The PostegreSQL Ecosystem

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

Toward PostgreSQL 13

Toward PostgreSQL 13

Introducing PostgreSQL 13's new features

Upgrading to PostgreSQL 13

Summary

References

Other Books You May Enjoy

Other Books You May Enjoy

Leave a review - let other readers know what you think

Configuration and Monitoring

One of the duties of a database administrator is to configure the cluster so that it behaves well for the current workload and context. The configuration is not static: most of the time, you will find yourself making changes to the configuration, so it is important that you feel comfortable with inspecting and changing the cluster's configuration.

Another important task, partially related to configuration, is monitoring the cluster in order to understand how the system is actually behaving, and whether there are bottlenecks and problems to be solved. Such problems can sometimes be solved by making changes to the configuration of the cluster, by using different hardware (for example, increasing the available memory), and sometimes by fixing the applications that could be causing the bottleneck.

This chapter will show you how to manage and inspect...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Learn PostgreSQL

Search

Your notes and bookmarks