PostgreSQL 11 Administration Cookbook

PostgreSQL 11 Administration Cookbook

By : Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala

Buy this Book

PostgreSQL 11 Administration Cookbook

By: Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala

Buy this Book

Overview of this book

PostgreSQL is a powerful, open source database management system with an enviable reputation for high performance and stability. With many new features in its arsenal, PostgreSQL 11 allows you to scale up your PostgreSQL infrastructure. This book takes a step-by-step, recipe-based approach to effective PostgreSQL administration. The book will introduce you to new features such as logical replication, native table partitioning, additional query parallelism, and much more to help you to understand and control, crash recovery and plan backups. You will learn how to tackle a variety of problems and pain points for any database administrator such as creating tables, managing views, improving performance, and securing your database. As you make steady progress, the book will draw attention to important topics such as monitoring roles, backup, and recovery of your PostgreSQL 11 database to help you understand roles and produce a summary of log files, ensuring high availability, concurrency, and replication. By the end of this book, you will have the necessary knowledge to manage your PostgreSQL 11 database efficiently.

Title Page

About Packt

Contributors

Preface

Free Chapter

First Steps

Introducing PostgreSQL 11

Getting PostgreSQL

Connecting to the PostgreSQL server

Enabling access for network/remote users

Using graphical administration tools

OmniDB

Using the psql query and scripting tool

Changing your password securely

Avoiding hardcoding your password

Using a connection service file

Troubleshooting a failed connection

Exploring the Database

What type of server is this?

What version is the server?

What is the server uptime?

Locating the database server files

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 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

Configuration

Reading the fine manual

Planning a new database

Changing parameters in your programs

Finding the current configuration settings

Which parameters are at non-default settings?

Updating the parameter file

Setting parameters for particular groups of users

The basic server configuration checklist

Adding an external module to PostgreSQL

Using an installed module

Managing installed extensions

Server Control

Introduction

Starting the database server manually

Stopping the server safely and quickly

Stopping the server in an emergency

Reloading the 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 database

Running multiple servers on one system

Setting up a connection pool

Accessing multiple servers using the same host and port

Tables and Data

Choosing good names for database objects

Handling objects with quoted names

Enforcing the same name and definition for columns

Identifying and removing duplicates

Preventing duplicate rows

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

Security

Introduction

The PostgreSQL superuser

Revoking user access to a table

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

Removing a user without dropping their data

Checking whether all users have a secure password

Giving limited superuser powers to specific users

Auditing database access

Always knowing which user is logged in

Integrating with LDAP

Connecting using SSL

Using SSL certificates to authenticate

Mapping external usernames to database roles

Encrypting sensitive data

Database Administration

Writing a script that either succeeds entirely or fails entirely

Writing a psql script that exits on the first error

Using psql variables

Placing query output into psql variables

Writing a conditional psql script

Investigating a psql error

Using pgAdmin for DBA tasks

Using OmniDB for DBA tasks

Performing actions on many tables

Adding/removing columns on a table

Changing the data type of a column

Changing the definition of a data type

Adding/removing schemas

Moving objects between schemas

Adding/removing tablespaces

Moving objects between tablespaces

Accessing objects in other PostgreSQL databases

Accessing objects in other foreign databases

Updatable views

Using materialized views

Monitoring and Diagnosis

Introduction

Providing PostgreSQL information to monitoring tools

Real-time viewing using pgAdmin or OmniDB

Checking whether a user is connected

Checking whether a computer is connected

Repeatedly executing a query in psql

Checking which queries are running

Checking which queries are active or blocked

Knowing who is blocking a query

Killing a specific session

Detecting an in-doubt prepared transaction

Knowing whether anybody is using a specific table

Knowing when a table was last used

Usage of disk space by temporary data

Understanding why queries slow down

Investigating and reporting a bug

Producing a daily summary of log file errors

Analyzing the real-time performance of your queries

Regular Maintenance

Controlling automatic database maintenance

Avoiding auto-freezing and page corruptions

Removing issues that cause bloat

Removing old prepared transactions

Actions for heavy users of temporary tables

Identifying and fixing bloated tables and indexes

Monitoring and tuning a vacuum

Maintaining indexes

Adding a constraint without checking existing rows

Finding unused indexes

Carefully removing unwanted indexes

Planning maintenance

Performance and Concurrency

Finding slow SQL statements

Finding out what makes SQL slow

Collect regular statistics from pg_stat* views

Reducing the number of rows returned

Simplifying complex SQL queries

Speeding up queries without rewriting them

Discovering why a query is not using an index

Forcing a query to use an index

Using parallel query

Creating time series tables

Using optimistic locking

Reporting performance problems

Backup and Recovery

Understanding and controlling crash recovery

Planning backups

Hot logical backups of one database

Hot logical backups of all databases

Backups of database object definitions

Standalone hot physical database backup

Hot physical backup and continuous archiving

Recovery of all databases

Recovery to a point in time

Recovery of a dropped/damaged table

Recovery of a dropped/damaged database

Improving performance of backup/recovery

Incremental/differential backup and restore

Hot physical backups with Barman

Recovery with Barman

Validating backups

Replication and Upgrades

Replication concepts

Replication best practices

Setting up file-based replication – deprecated

Setting up streaming replication

Setting up streaming replication security

Hot standby and read scalability

Managing streaming replication

Using repmgr

Using replication slots

Monitoring replication

Performance and synchronous replication

Delaying, pausing, and synchronizing replication

Logical replication

Bidirectional replication

Archiving transaction log data

Upgrading minor releases

Major upgrades in-place

Major upgrades online

Other Books You May Enjoy

Leave a review - let other readers know what you think

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Using parallel query

PostgreSQL now has an increasingly effective parallel query feature.

Response times from long-running queries can be improved by the use of parallel processing. The concept is that we divide a large task up into multiple smaller pieces. We get the answer faster, but we use more resources to do that.

Very short queries won't get faster by using parallel query, so if you have lots of those you'll gain more by thinking about better indexing strategies. Parallel query is aimed at making very large tasks faster, so it is useful for reporting and business intelligence queries.

How to do it…

Take a query that needs to do a big chunk of work, such as the following:

\timing
SELECT count(*) FROM accounts;
count
---------
1000000
(1 row)
Time: 261.652 ms
SET max_parallel_workers_per_gather = 8;
SELECT count(*) FROM accounts;
count
---------
1000000
(1 row)
Time: 180.513 ms

By setting the max_parallel_workers_per_gather parameter, we've improved performance using parallel query. Note...

PostgreSQL 11 Administration Cookbook

By : Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala

PostgreSQL 11 Administration Cookbook

By: Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala

Overview of this book

Related Content you might be interested in

Current Title:

PostgreSQL 11 Administration Cookbook

Learning PostgreSQL 11

PostgreSQL 10 High Performance

Learning PostgreSQL 10

Using parallel query

How to do it…