PostgreSQL administration cookbook : effective database management using PostgreSQL 9.5/9.6 / Simon Riggs, Gianni Ciolli, Gabriele Bartolini.

Format:

Book

Author/Creator:

Riggs, Simon, author.

Ciolli, Gianni, author.

Bartolini, Gabriele, author.

Language:

English

Subjects (All):

PostgreSQL.

SQL (Computer program language).

Query languages (Computer science).

Physical Description:

1 online resource (517 pages) : illustrations (some color)

Edition:

9.5/9.6 edition.

Other Title:

Effective database management using PostgreSQL 9.5/9.6

Place of Publication:

Birmingham, England ; Mumbai, [India] : Packt, 2017.

System Details:

text file

Summary:

Over 150 recipes to help you administer your PostgreSQL database more efficiently About This Book Get to grips with the capabilities of PostgreSQL 9.6 to administer your database more efficiently Monitor, tune, secure and protect your database A step-by-step, recipe-based guide to help you tackle any problem in PostgreSQL administration with ease Who This Book Is For This book is for system administrators, database administrators, data architects, developers, and anyone with an interest in planning for, or running, live production databases. This book is most suited to those who have some technical experience. What You Will Learn Implement PostgreSQL features for performance and reliability Harness the power of the latest PostgreSQL 9.6 features Manage open source PostgreSQL versions 9.5 and 9.6 on various platforms Discover advanced technical tips for experienced users Explore best practices for planning and designing live databases Select and implement robust backup and recovery techniques Explore concise and clear guidance on replication and high availability See the latest details on Logical Replication and Bi-Directional Replication In Detail PostgreSQL is a powerful opensource database management system; now recognized as the expert's choice for a wide range of applications, it has an enviable reputation for performance and stability. PostgreSQL provides an integrated feature set comprising relational database features, object-relational, text search, Geographical Info Systems, analytical tools for big data and JSON/XML document management. Starting with short and simple recipes, you will soon dive into core features, such as configuration, server control, tables, and data. You will tackle a variety of problems a database administrator usually encounters, from creating tables to managing views, from improving performance to securing your database, and from using monitoring tools to using storage engines. Recipes based on important topics such as high availability, concurrency, replication, backup and recovery, as well as diagnostics and troubleshooting are also given special importance. By the end of this book, you will have all the knowledge you need to run, manage, and maintain PostgreSQL efficiently. Style and approach This book takes a step-by-step, recipe-based approach, where each recipe focuses on a particular challenge faced by a PostgreSQL administrator while administering his/her database. Explained in a very easy to follow man...

Contents:

Cover

Copyright

Credits

About the Authors

About the Reviewer

www.PacktPub.com

Customer Feedback

Table of Contents

Preface

Chapter 1: First Steps

Introduction

Introducing PostgreSQL 9.6

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

See also

Enabling access for network/remote users

Using graphical administration tools

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

Locating the database's system identifier

How it works.

Listing databases on this 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

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

How to do it.

[Installing modules using a software installer]

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

[How to do it...]

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

There's more...

Handling objects with quoted names

Enforcing the same name and definition for columns

Identifying and removing duplicates

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

There's more.

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

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

Was the change committed?

Who made the change?

Can I find this information from the database?

You may still miss some DDL...

Auditing data changes

Collecting data changes from the server log

Collecting changes using triggers

Collecting changes using triggers and saving them in another database

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

How it works.

Notes:

Includes index.

Description based on online resource; title from PDF title page (ebrary, viewed July 14, 2017).

OCLC:

987331243