▶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
▶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
▶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
▶What this book covers
- Chapter 1, First Steps, introduces you PostgreSQL 11; it explains how to download and install PostgreSQL 11, connect to a PostgreSQL server, enable server access to the network or remote users, use graphical administration tools, use PSQL query and scripting tools, change your password securely, avoid hardcoding your password, use a connection service file, and troubleshoot a failed connection.
- Chapter 2, Exploring the Database, demonstrates how to identify the version of the database server you are using, as well as the server uptime. It helps you locate the database server files, the database server message log, and the database's system identifier. It explains how to list a database on the database server, and it contains recipes that let you know the number of tables in your database, how much disk space is used by the database and tables, what the the biggest tables are, how many rows a table has, how to estimate rows in a table, and how to understand object dependencies.
- Chapter 3, Configuration, explains topics such as Reading the Fine Manual (RTFM), how to plan a new database, how to change the parameters in your programs, the current configuration settings, the parameters that are at non-default settings, how to update the parameter file, how to set parameters for particular groups of users, the basic server configuration checklist, how to add an external module into the PostgreSQL server, and how to run the server in power-saving mode.
- Chapter 4, Server Control, provides information about starting the database server manually, stopping the server quickly and safely, stopping the server in an emergency, reloading the server configuration files, restarting the server quickly, preventing new connections, restricting users to just one session each, and pushing users off the system. It contains recipes that help you choose a design for multi-tenancy, as well as recipes that explain how to use multiple schemas, give users their own private database, run multiple database servers on one system, and set up a connection pool.
- Chapter 5, Tables and Data, guides you through the process of choosing good names for database objects. Additionally, it explains how to handle objects with quoted names, enforce the same name, maintain the same definition for columns, identify and remove duplicate rows, prevent duplicate rows, find a unique key for a set of data, generate test data, randomly sample data, load data from a spreadsheet, and load data from flat files.
- Chapter 6, Security, provides recipes on revoking user access to a table, granting user access to a table, 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 DDL changes, auditing data changes, integrating with LDAP, connecting using SSL, and encrypting sensitive data.
- Chapter 7, Database Administration, provides recipes on useful topics such as writing a script where all either succeed or fail, writing a PSQL script that exits on the first error, performing actions on many tables, adding and removing columns in tables, changing the data type of a column, adding and removing schemas, moving objects between schemas, adding and removing tablespaces, moving objects between tablespaces, accessing objects in other PostgreSQL databases, and enabling views to be updated.
- Chapter 8, Monitoring and Diagnosis, provides recipes that answer questions such as whether the user is connected, what they are running, whether they are active or blocked, who they are being blocked by, whether anybody is using a specific table, when the table it was last used, how much disk space is being used by temporary data, and why your queries could be slowing down. It also demonstrates how to investigate and report a bug, produce a daily summary report of log file errors, kill a specific session, and resolve an indoubt prepared transaction.
- Chapter 9, Regular Maintenance, provides useful recipes on how to control automatic database maintenance, avoid auto-freezing and page corruptions, avoid transaction wraparound, remove old prepared transactions, offer solutions for heavy users of temporary tables, identify and fix bloated tables and indexes, maintain indexes, find unused indexes, carefully remove unwanted indexes, and plan maintenance.
- Chapter 10, Performance and Concurrency, covers topics such as how to find slow SQL statements, collect regular statistics from pg_stat* views, discover what makes SQL slow, reduce the number of rows returned, simplify complex SQL, speed up queries without rewriting them, understand why some queries are not using an index, force a query to use an index, use optimistic locking, and report performance problems. And, of course, you'll learn about the new parallel query features.
- Chapter 11, Backup and Recovery, explains that backups are essential, although this topic is only covered very briefly. So, this chapter provides useful information about the backup and recovery of your PostgreSQL database through recipes on how to understand and control crash recovery and how to plan backups. Additionally, you will learn about the hot logical backup of one database, the hot logical backup of all databases, the hot logical backup of all tables in a tablespace, the backup of database object definitions, the standalone hot physical database backup, the hot physical backup, and continuous archiving. It also includes topics such as the recovery of all databases, recovery to a point in time, the recovery of a dropped or damaged table, the recovery of a dropped or damaged database, the recovery of a dropped or damaged tablespace, how to improve the performance of backup/recovery, and incremental/differential backup and restore.
- Chapter 12, Replication and Upgrades, explains that replication isn't magic, although it can be pretty cool. It's even cooler when it works, and that's what this chapter is all about. This chapter covers replication concepts, replication best practices, how to set up file-based log shipping replication, how to set up streaming log replication, how to manage log shipping replication, how to manage hot standby, synchronous replication, how to upgrade to a new minor release, in-place major upgrades, major upgrades online, and logical replication and Postgres-BDR.
