PostgreSQL 10 administration cookbook : over 165 effective recipes for database management and maintenance in PostgreSQL 10 / Gianni Ciolli, Simon Riggs.

Format:

Book

Author/Creator:

Ciolli, Gianni, author.

Riggs, Simon, author.

Language:

English

Subjects (All):

PostgreSQL.

Database management.

Physical Description:

1 online resource (576 pages)

Edition:

1st edition

Other Title:

Postgre Structure Query Language ten administration cookbook

Place of Publication:

Birmingham ; Mumbai : Packt Publishing, 2018.

System Details:

text file

Summary:

A practical guide to administer, monitor and replicate your PostgreSQL 10 database About This Book Get to grips with the capabilities of PostgreSQL 10 to administer your database more efficiently Monitor, tune, secure and protect your database for optimal performance A step-by-step, recipe-based guide to help you tackle any problem in PostgreSQL 10 administration with ease Who This Book Is For This book is for database administrators, data architects, developers, or anyone with an interest in planning for, or running, live production databases using PostgreSQL. It is most suited to those looking for hands-on solutions to any problem associated with PostgreSQL administration. What You Will Learn Get to grips with the newly released PostgreSQL 10 features to improve database performance and reliability Manage open source PostgreSQL versions 10 on various platforms. Explore best practices for planning and designing live databases Select and implement robust backup and recovery techniques in PostgreSQL 10 Explore concise and clear guidance on replication and high availability Discover advanced technical tips for experienced users In Detail 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 10 allows users to scale up their PostgreSQL infrastructure. This book takes a step-by-step, recipe-based approach to effective PostgreSQL administration. Throughout this book, you will be introduced to these new features such as logical replication, native table partitioning, additional query parallelism, and much more. You will learn how to tackle a variety of problems that are basically the pain points for any database administrator - from creating tables to managing views, from improving performance to securing your database. More importantly, the book pays special attention to topics such as monitoring roles, backup, and recovery of your PostgreSQL 10 database, ensuring high availability, concurrency, and replication. By the end of this book, you will know everything you need to know to be the go-to PostgreSQL expert in your organization. Style and approach The book is a step by step guide with example-driven recipes, focused on the new features of the latest PostgreSQL version10. This book will serve as a specific guide to understand and leverage useful PostgreSQL functionalities to create better and more efficient databases. Down...

Contents:

Cover

Title Page

Copyright and Credits

Packt Upsell

Contributors

Table of Contents

Preface

Chapter 1: First Steps

Introduction

Introducing PostgreSQL 10

What makes PostgreSQL different?

Robustness

Security

Ease of use

Extensibility

Performance and concurrency

Scalability

SQL and NoSQL

Popularity

Commercial support

Research and development funding

Getting PostgreSQL

How to do it...

How it works...

There's more…

Connecting to the PostgreSQL server

Getting ready

How to do it…

How it works…

See also

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

Chapter 2: Exploring the Database

What version is the server?

What is the server uptime?

Locating the database server files

Locating the database server's message log

There's more...

Locating the database's system identifier

Listing databases on this database server

How it works.

There's more...

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

Function 1 - Estimating the number of rows

Function 2 - Computing the size of a table without locks

Listing extensions in this database

Understanding object dependencies

Chapter 3: 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

Installing modules using a software installer

Installing modules from PGXN

Installing modules from a manually downloaded package.

Installing modules from source code

Using an installed module

Managing installed extensions

Chapter 4: Server Control

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

Chapter 5: 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

Getting ready.

How to do it…

Preventing duplicate rows

Duplicate indexes

Uniqueness without indexes

Real-world example - IP address range allocation

Real-world example - range of time

Real-world example - prefix ranges

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

Chapter 6: Security

Typical user role

The PostgreSQL superuser

Other superuser-like attributes

Attributes are never inherited

Revoking user access to a table

Database creation scripts

Default search path

Securing views

Granting user access to a table

Access to the schema

Granting access to a table through a group role

Granting access to all objects in a schema

Granting user access to specific columns

Granting user access to specific rows

Creating a new user

Temporarily preventing a user from connecting

There's more….

Limiting the number of concurrent connections by a user

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

Writing a debugging_info function for developers

Auditing database access

Auditing SQL

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 SSL

Getting the SSL key and certificate

Setting up a client to use SSL

Checking server authenticity

Using SSL certificates to authenticate

Avoiding duplicate SSL connection attempts

Using multiple client certificates

Using the client certificate to select the database user

Mapping external usernames to database roles

Encrypting sensitive data

For really sensitive data

For really, really, really sensitive data!

Chapter 7: Database Administration

Writing a script that either succeeds entirely or fails entirely

Writing a psql script that exits on the first error.

Notes:

Description based on print version record.

OCLC:

1038475454