PostgreSQL 16 Administration Cookbook : Solve Real-World Database Administration Challenges with 180+ Practical Recipes and Best Practices / Gianni Ciolli [and four others].

Format:

Book

Author/Creator:

Ciolli, Gianni, author.

Series:

Expert insight.

Expert insight

Language:

English

Subjects (All):

PostgreSQL.

Database management.

SQL (Computer program language).

Physical Description:

1 online resource (637 pages)

Edition:

First edition.

Place of Publication:

Birmingham, England : Packt Publishing, Limited, [2023]

Biography/History:

Ciolli Gianni: Gianni Ciolli is Vice President and Field CTO at EDB; he was Global Head of Professional Services at 2ndQuadrant until it was acquired by EDB. Gianni has been a PostgreSQL consultant, trainer, and speaker at many PostgreSQL conferences in Europe and abroad over more than 10 years. He has a PhD in Mathematics from the University of Florence. He has worked with free and Open-Source software since the 1990s and is active in the community. He lives between Frankfurt and London and plays the piano in his spare timeMejias Boriss: Boriss Mejias is a Senior Solutions Architect at EDB, building on his experience as PostgreSQL consultant and trainer at 2ndQuadrant. He has been working with open source software since the beginning of the century contributing to several projects both with code and community work. He has a PhD in Computer Science from the Universite catholique de Louvain, and an Engineering degree from Universidad de Chile. Complementary to his role as Solutions Architect, he gives PostgreSQL training and is a regular speaker at PostgreSQL conferences. He loves spending time with his family and playing air guitarAngelakos Jimmy: Jimmy Angelakos is a Systems and Database Architect and recognized PostgreSQL expert. He studied Computer Science at the University of Aberdeen, has worked with Open-Source tools for 25+ years, is an active member of PostgreSQL Europe and occasional contributor to the PostgreSQL project. He also speaks frequently at database and Free & Open-Source Software conferencesKumar Vibhor: Vibhor Kumar, Global VP at EDB, is a pioneering data tech leader. He manages a global team of engineers, optimizing clients' Postgres databases for peak performance and scalability. He advises Fortune 500 clients, including many Financial Institutes, in innovating and transforming their data platforms. His past experience spans IBM, BMC Software, and CMC Ltd. He holds a BSc in Computer Science from the University of Lucknow and a Master's from the Army Institute of Management. As a certified expert in numerous technologies, he often shares his insights on DevOps, cloud, and database optimization through blogging and speaking at eventsRiggs Simon: Simon Riggs is a Major Developer of PostgreSQL since 2004. Formerly, Simon was the Founder and CEO of 2ndQuadrant, acquired by EDB in 2020. Simon has contributed widely to PostgreSQL, initiating new projects, contributing ideas, committing many important features as well and working directly with database architects and users on advanced solutions

Summary:

Written by a team of well-known PostgreSQL experts, this new edition will cover all the latest updates of PostgreSQL 16 including 12+ new and improved recipes on logging, monitoring, security and high-performance Purchase of the print or Kindle book includes a free PDF eBook Key Features Skill-up as a database administrator by achieving improved query performance, backup, and recovery management, setting up replication and so on Get to grips with the essentials of database management with a recipe-based approach using the latest features of PostgreSQL 16 New and updated recipes on crucial PostgreSQL topics like Monitoring, Logging, Scalability and so on Book Description PostgreSQL has seen a huge increase in its customer base in the past few years and is becoming one of the go-to solutions for anyone who has a database-specific challenge. This PostgreSQL book touches on all the fundamentals of Database Administration in a problem-solution format. It is intended to be the perfect desk reference guide. This new edition focuses on recipes based on the new PostgreSQL 16 release. The additions include handling complex batch loading scenarios with the SQL MERGE statement, security improvements, running Postgres on Kubernetes or with TPA and Ansible, and more. This edition also focuses on certain performance gains, such as query optimization, and the acceleration of specific operations, such as sort. It will help you understand roles, ensuring high availability, concurrency, and replication. It also draws your attention to aspects like validating backups, recovery, monitoring, and scaling aspects. This book will act as a one-stop solution to all your real-world database administration challenges. By the end of this book, you will be able to manage, monitor, and replicate your PostgreSQL 16 database for efficient administration and maintenance with the best practices from experts. What you will learn Discover how to improve batch data loading with the SQL MERGE statement Use logical replication to apply large transactions in parallel Improve your back up and recovery performance with server-side compression Tackle basic to high-end and real-world PostgreSQL challenges with practical recipes Monitor and fine-tune your database with ease Learn to navigate the newly introduced features of PostgreSQL 16 Efficiently secure your PostgreSQL database with new and updated features Who this book is for This Postgres book is for database administrators, data architects, database developers, and anyone with an interest in planning and running live production databases using PostgreSQL 14. Those looking for hands-on solutions to any problem associated with PostgreSQL 14 administration will also find this book useful. Some experience with handling PostgreSQL databases will help you to make the most out of this book, however, it is a useful resource even if you are just beginning your Postgres journey.

Contents:

Cover

Copyright

Contributors

Table of Contents

Preface

Chapter 1: First Steps

Introducing PostgreSQL 16

What makes PostgreSQL different?

Robustness

Security

Ease of use

Extensibility

Performance and concurrency

Scalability

SQL and NoSQL data models

Popularity

Commercial support

Research and development funding

How to get PostgreSQL

How to do it...

How it works...

There's more…

Connecting to the PostgreSQL server

Getting ready

How it works…

See also

Enabling access for network/remote users

How to do it…

Using the pgAdmin 4 GUI tool

Using the psql query and scripting tool

Changing your password securely

Avoiding hardcoding your password

Using a connection service file

There's more...

Troubleshooting a failed connection

PostgreSQL in the cloud

PostgreSQL with Kubernetes

PostgreSQL with TPA

There's more

Chapter 2: Exploring the Database

What type of server is this?

What version is the server?

What is the server uptime?

Locating the database server files

There's more….

Locating the database server's message log

Locating the database's system identifier

Listing databases on the database server

How many tables are there in a database?

How much disk space does a database use?

How much memory does a database currently use?

How much disk space does a table use?

Which are my biggest tables?

How many rows are there in a table?

Quickly estimating the number of rows in a table

Listing extensions in this database

Understanding object dependencies

Chapter 3: Server Configuration

Read the fine manual (RTFM)

Planning a new database

Setting the configuration parameters for the database server

Setting the configuration parameters in your programs

Finding the configuration settings for your session

Finding parameters with non-default settings

Setting parameters for particular groups of users

How it works….

A basic server configuration checklist

Adding an external module to PostgreSQL

Installing modules using a software installer

Installing modules from PGXN

Installing modules from source code

Using an installed module/extension

Managing installed extensions

Chapter 4: Server Control

An overview of controlling the database server

Starting the database server manually

Stopping the server safely and quickly

Stopping the server in an emergency

Reloading server configuration files

Restarting the server quickly

Preventing new connections

Restricting users to only one session each

Pushing users off the system

Deciding on a design for multitenancy

Using multiple schemas

Giving users their own private databases

Running multiple servers on one system

Setting up a connection pool

Accessing multiple servers using the same host and port

Running multiple PgBouncer on the same port to leverage multiple cores

How to do it….

How it works…

Chapter 5: Tables and Data

Choosing good names for database objects

Handling objects with quoted names

Identifying and removing duplicates

Preventing duplicate rows

Duplicate indexes

Uniqueness without indexes

A real-world example - IP address range allocation

A real-world example - a range of time

Finding a unique key for a set of data

Generating test data

Randomly sampling data

Loading data from a spreadsheet

Loading data from flat files

Making bulk data changes using server-side procedures with transactions

Dealing with large tables with table partitioning

Finding good candidates for partition keys

Consolidating data with MERGE

Deciding when to use JSON data types

Example: moving sparse columns to JSON

Example: expose JSON data using a view

Chapter 6: Security

An overview of PostgreSQL security

Typical user roles

The PostgreSQL superuser

Other superuser-like attributes

See also.

Revoking user access to tables

Database creation scripts

Default search path

Securing views

Granting user access to a table

Granting user access to specific columns

Granting user access to specific rows

Creating a new user

Temporarily preventing a user from connecting

Limiting the number of concurrent connections by a user

Revoking a user's database access

Forcing NOLOGIN users to disconnect

Removing a user without dropping their data

Checking whether all users have a secure password

Giving limited superuser powers to specific users

Assigning backup privileges to a user

Auditing database access

Auditing access

Auditing SQL statements

Auditing table access

Managing the audit log

Auditing data changes

Always knowing which user is logged in

Not inheriting user attributes

Integrating with LDAP

Setting up the client to use LDAP

Replacement for the User Name Map feature

Connecting using encryption (SSL / GSSAPI)

Getting the SSL key and certificate.

Setting up a client to use SSL.

Notes:

Description based upon print version of record.

How to do it...

Description based on print version record.

ISBN:

9781835468449

1835468446

OCLC:

1411306249