Book Image

PostgreSQL 14 Administration Cookbook

By : Simon Riggs, Gianni Ciolli
5 (1)
Book Image

PostgreSQL 14 Administration Cookbook

5 (1)
By: Simon Riggs, Gianni Ciolli

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 14 allows you to scale up your PostgreSQL infrastructure. With this book, you'll take a step-by-step, recipe-based approach to effective PostgreSQL administration. This book will get you up and running with all the latest features of PostgreSQL 14 while helping you explore the entire database ecosystem. You’ll learn how to tackle a variety of problems and pain points you may face as a database administrator such as creating tables, managing views, improving performance, and securing your database. As you make progress, the book will draw attention to important topics such as monitoring roles, validating backups, regular maintenance, and recovery of your PostgreSQL 14 database. This will help you understand roles, ensuring high availability, concurrency, and replication. Along with updated recipes, this book touches upon important areas like using generated columns, TOAST compression, PostgreSQL on the cloud, and much more. By the end of this PostgreSQL book, you’ll have gained the knowledge you need to manage your PostgreSQL 14 database efficiently, both in the cloud and on-premise.
Table of Contents (14 chapters)

Understanding object dependencies

In most databases, there will be dependencies between objects in the database. Sometimes, we need to understand these dependencies to figure out how to perform certain actions, such as modifying or deleting existing objects. Let's look at this in detail.

Getting ready

We'll use the following simple database to understand and investigate them:

  1. Create two tables as follows:
    CREATE TABLE orders (
     orderid integer PRIMARY KEY
    CREATE TABLE orderlines (
     orderid integer
    ,lineid smallint
    ,PRIMARY KEY (orderid, lineid)
  2. Now, we add a link between them to enforce what is known as referential integrity, as follows:
    ALTER TABLE orderlines ADD FOREIGN KEY (orderid)
    REFERENCES orders (orderid);
  3. If we try to drop the referenced table, we get the following message:
    DROP TABLE orders;
    ERROR: cannot drop table orders because other objects depend on it
    DETAIL: constraint orderlines_orderid_fkey on table orderlines depends on table orders
    HINT: Use DROP ... CASCADE to drop the dependent objects too.

Be very careful! If you follow the hint, you may accidentally remove all the objects that have any dependency on the orders table. You might think that this would be a great idea, but it is not the right thing to do. It might work, but we need to ensure that it will work.

Therefore, you need to know what dependencies are present on the orders table, and then review them. Then, you can decide whether it is okay to issue the CASCADE version of the command, or whether you should reconcile the situation manually.

How to do it…

You can use the following command from psql to display full information about a table, the constraints that are defined upon it, and the constraints that reference it:

\d+ orders

You can also get specific details of the constraints by using the following query:

SELECT * FROM pg_constraint
WHERE confrelid = 'orders'::regclass;

The aforementioned queries only covered constraints between tables. This is not the end of the story, so read the There's more... section.

How it works…

When we create a foreign key, we add a constraint to the catalog table, known as pg_constraint. Therefore, the query shows us how to find all the constraints that depend upon the orders table.

There's more…

With Postgres, there's always a little more when you look beneath the surface. In this case, there's a lot more, and it's important.

We didn't discuss dependencies with other kinds of objects. Two important types of objects that might have dependencies on tables are views and functions.

Consider the following command:

DROP TABLE orders;

If you issue this, the dependency on any of the views will prevent the table from being dropped. So, you need to remove those views and then drop the table.

The story with function dependencies is not as useful. Relationships between functions and tables are not recorded in the catalog, nor is the dependency information between functions. This is partly due to the fact that most PostgreSQL procedural languages allow dynamic query execution, so you wouldn't be able to tell which tables or functions a function would access until it executes. That's only partly the reason because most functions clearly reference other tables and functions, so it should be possible to identify and store those dependencies. However, right now, we don't do that. So, make a note that you need to record the dependency information for your functions manually so that you'll know if and when it's okay to remove or alter a table or other objects that the functions depend on.




This is the main table storage. Beneath this directory, each database has its own directory, within which the files for each database table or index are located.


Here are the tables that are shared across all databases, including the list of databases.


Here we store transaction commit timestamp data (from 9.5 onward).


This includes dynamic shared memory information (from 9.4 onward).


This includes logical decoding status data.


This includes files used for shared row-level locks.


This includes the LISTEN/NOTIFY status files.


This includes information about replication slots (from 9.4 onward).


This includes information on committed serializable transactions.


This includes exported snapshot files.


This includes permanent statistics data.


This includes transient statistics data.


This includes subtransaction status data.


This includes symbolic links to tablespace directories.


This includes state files for prepared transactions.


This includes the transaction log or Write-Ahead Log (WAL) (formerly pg_xlog).


This includes the transaction status files (formerly pg_clog).