Book Image

PostgreSQL 11 Administration Cookbook

By : Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala
Book Image

PostgreSQL 11 Administration Cookbook

By: Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala

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.

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL 11 Administration Cookbook
Simon Riggs | Gianni Ciolli | Sudheer Kumar Meesala
May, 2019 | 600 pages
Small book image
Learning PostgreSQL 11
Salahaldin Juba | Andrey Volkov
Jan, 2019 | 556 pages
Small book image
PostgreSQL 10 High Performance
Enrico Pirozzi
Apr, 2018 | 508 pages
Small book image
Learning PostgreSQL 10
Salahaldin Juba | Andrey Volkov
Dec, 2017 | 488 pages
Table of Contents (19 chapters)
Title Page
Title Page
Copyright and Credits
Copyright and Credits
About Packt
About Packt
Contributors
Contributors
Preface
Preface
Free Chapter
1
First Steps
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
2
Exploring the Database
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
3
Configuration
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
4
Server Control
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
5
Tables and Data
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
6
Security
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
7
Database Administration
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
8
Monitoring and Diagnosis
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
9
Regular Maintenance
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
10
Performance and Concurrency
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
11
Backup and Recovery
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
12
Replication and Upgrades
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
Other Books You May Enjoy
Leave a review - let other readers know what you think
Index
Index

Granting user access to specific columns

A user can be given access to only some table columns.

Getting ready

We will continue the example from the previous recipe, so we assume that there is already a schema called someschema and a role called somerole with USAGE privileges on it. We create a new table on which we will grant column-level privileges:

CREATE TABLE someschema.sometable2(col1 int, col2 text);

How to do it…

  1. We want to grant somerole the ability to view existing data and insert new data; we also want to provide the ability to amend existing data, limited to column col2 only. We use the following self-evident statements:
GRANT SELECT, INSERT ON someschema.sometable2 TO somerole; 
GRANT UPDATE (col2) ON someschema.sometable2 TO somerole;
  1. Let's assume the identity of the somerolerole and test these privileges with the following commands:
SET ROLE TO somerole; 
INSERT INTO someschema.sometable2 VALUES (1, 'One'); 
SELECT * FROM someschema.sometable2 WHERE col1 = 1;
  1. As expected, we are able...
Previous Section
End of Section 6
Next Section