PostgreSQL 11 Administration Cookbook

Over 175 recipes for database administrators to manage enterprise databases

Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala

• Verlag: Packt Publishing
• Jahr: 2019
• ISBN:
  9781789536225
• Auflage: 1
• Thema: Informatica
• Thema: Archiviazione di dati

A practical guide to administer, monitor and replicate your PostgreSQL 11 database

Key Features

• Study and apply the newly introduced features in PostgreSQL 11
• Tackle any problem in PostgreSQL 11 administration and management
• Catch up on expert techniques for monitoring, fine-tuning, and securing your database

Book Description

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.

What you will learn

• Troubleshoot open source PostgreSQL version 11 on various platforms
• Deploy best practices for planning and designing live databases
• Select and implement robust backup and recovery techniques in PostgreSQL 11
• Use pgAdmin or OmniDB to perform database administrator (DBA) tasks
• Adopt efficient replication and high availability techniques in PostgreSQL
• Improve the performance of your PostgreSQL solution

Who this book is for

This book is designed for database administrators, data architects, database developers, or anyone with an interest in planning and running live production databases using PostgreSQL 11. It is also ideal if you're looking for hands-on solutions to any problem associated with PostgreSQL 11 administration. Some experience with handling PostgreSQL databases will be beneficial

Database Administration

In Chapter 5, Tables and Data, we looked at the contents of tables and various complexities. Now we'll turn our attention to larger administration tasks that we need to perform from time to time, such as creating things, moving things around, storing things neatly, and removing them when they're no longer required.

The most sensible way to perform major administrative tasks is to write a script to do what you think is required. If you're unsure, you can always run the script on a system test server, and then run it again on the production server once you're happy. Manically typing commands against production database servers isn't wise. Worse, using an admin tool can lead to serious issues if that tool doesn't show you the SQL you're about to execute. If you haven't dropped your first live table yet, don't worry; you will. Perhaps you might want to read Chapter 11, Backup and Recovery, first, eh? Back it up using scripts.

Scripts are great because you can automate common tasks, and there's no need to sit there with a mouse, working your way through hundreds of changes. If you're drawn to the discussion about the command line versus GUI, then my thoughts and reasons are completely orthogonal to that. I want to encourage you to avoid errors and save time by performing repetitive and automatic execution of small administration programs or scripts. If it were safe or easy to do the equivalent of mouse movements in a script, then that would be an option, but it's definitely not. The only viable way to write a repeatable script is by writing text SQL commands. Which scripting tool to use is a more interesting debate. We consider psql here because if you've got PostgreSQL, then you've certainly got it, without needing to install additional software. So, we're on solid ground to provide examples that way.

Let's move on to the recipes! First, we'll start by looking at some scripting techniques that are valuable in PostgreSQL.

In this chapter, we will cover the following recipes:

• 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
• Performing actions on many tables
• Using pgAdmin for DBA tasks
• Using OmniDB for DBA tasks
• 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

Writing a script that either succeeds entirely or fails entirely

Database administration often involves applying a coordinated set of changes to the database. One of PostgreSQL's great strengths is its transaction system, wherein almost all actions can be executed inside a transaction. This allows us to build a script with many actions that will either all succeed or all fail. This means that if any of these actions fail, then all the other actions in the script are rolled back and never become visible to any other user, which can be critically important on a production system. This property is referred to as atomicity in the sense that the script is intended as a single unit that cannot be split. This is the meaning of the A in the ACID properties of database transactions.

Transactions definitely apply to Data Definition Language (DDL), which refers to the set of SQL commands used to define, modify, and delete database objects. The term DDL goes back many years, but it persists because that subset is a useful short name for the commands that most administrators need to execute: CREATE, ALTER, DROP, and so on.

Although most commands in PostgreSQL are transactional, there are a few that cannot be. The most common example is of commands that use sequences. They cannot be transactional because when a new sequence number is allocated, the effect of having consumed that number must become visible immediately, without waiting for that transaction to be committed. Otherwise, the same number will be given to another transaction, which is contrary to what sequences are supposed to do.

How to do it…

The basic way to ensure that we get all commands successful or none at all is to literally wrap our script into a transaction, as follows:

BEGIN;
command 1;
command 2;
command 3;
COMMIT;

Writing a transaction control command involves editing the script, which you may not want to do or even have access to. There are, however, other ways as well.

From psql, you can do this by simply using the -1 or --single-transaction command-line options, as follows:

bash $ psql -1 -f myscript.sql
bash $ psql --single-transaction -f myscript.sql

The -1 option is short, but I recommend using --single-transaction, as it's much clearer which option is being selected.

How it works…

The entire script will fail if, at any point, one of the commands gives an error (or higher) message. Almost all of the SQL used to define objects (DDL) provides a way to avoid throwing errors. More precisely, commands that begin with the DROP keyword have an IF EXISTS option. This allows you to execute the DROP keyword, regardless of whether or not the object already exists.

Thus, by the end of the command, that object will not exist:

DROP VIEW IF EXISTS cust_view;

Similarly, most commands that begin with the CREATE keyword have the optional OR REPLACE suffix. This allows the CREATE statement to overwrite the definition if one already exists, or add the new object if it doesn't exist yet, like this:

CREATE OR REPLACE VIEW cust_view AS SELECT * FROM cust;

In the cases where both the DROP IF EXISTS and CREATE OR REPLACE options exist, you might think that CREATE OR REPLACE is usually sufficient. However, if you change the output definition of a function or a view, then using OR REPLACE is not sufficient. In that case, you must use DROP and recreate it, as shown in the following example:

postgres=# CREATE OR REPLACE VIEW cust_view AS 
SELECT col as title1 FROM cust;
CREATE VIEW
postgres=# CREATE OR R...