PostgreSQL is an open source object-relational database management system (ORDBMS), distributed under a very permissive license and developed by an active community.

There are a number of PostgreSQL-related services and software (https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases), either open source or not, that are provided by other software companies. Here, we discuss how to recognize which one you are using.

It is not so easy to detect the variant of PostgreSQL from the name; many of the products and services involving PostgreSQL include the word Postgres or PostgreSQL.

However, if you need to check the documentation, or to buy services such as support and consulting, you need to find out exactly what type your server is, as the available options will vary.

If you are paying a license fee or a cloud service subscription, you will already know the name of the company you are paying, and the specific variant of PostgreSQL you...

PostgreSQL has internal version numbers for the data file format, database catalog layout, and crash recovery format. Each of these is checked as the server runs to ensure that the data doesn’t become corrupt. PostgreSQL doesn’t change these internal formats for a single minor release; they only change across major releases.

From a user’s perspective, each release differs in terms of the way the server behaves. If you know your application well, then it should be possible to assess the differences simply by reading the release notes for each version. In many cases, a retest of the application is the safest thing to do.

If you experience any general problems related to setup and configuration with your database, then you’ll need to double-check which version of the server you have. This will help you to report a fault or to consult the correct version of the manual.

How to do it…

We will find out the version...

You may be wondering, how long has it been since the server started?

For instance, you might want to verify that there was no server crash if your server is not monitored, or to see when the server was last restarted, for instance, to change the configuration. We will find this out by asking the database server.

How to do it…

Issue the following SQL from any interface:

postgres=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;

You should get the following output:

     uptime
--------------------------------------
2 days 02:48:04

How it works…

Postgres stores the server start time, so we can access it directly, as follows:

postgres=# SELECT pg_postmaster_start_time();
pg_postmaster_start_time
----------------------------------------------
2023-09-26 09:24:44.292012+02

Then, we can write a SQL query to get the uptime, like this:

postgres=# SELECT current_timestamp...

Database server files are initially stored in a location referred to as the data directory. Additional data files may also be stored in tablespaces if any exist.

In this recipe, you will learn how to find the location of these directories on a given database server.

Getting ready

You’ll need to get OS access to the database system, which is what we call the platform on which the database runs.

How to do it...

If you can connect using psql, then you can use this command:

postgres=# SHOW data_directory;
    data_directory
-----------------------
/opt/postgres/data/

If not, the following are the system’s default data directory locations:

• Debian or Ubuntu systems: /var/lib/postgresql/MAJOR_RELEASE/main
• Red Hat RHEL, CentOS, and Fedora: /var/lib/pgsql/data/
• Systems deployed with Trusted Postgres Architect (TPA): /opt/postgres/data
• Windows: C:\Program Files\PostgreSQL\MAJOR_RELEASE...

The database server’s message log is a record of all messages recorded by the database server. This is the first place to look if you have server problems and is a good place to check regularly.

This log will include messages that look something like the following:

2023-09-01 19:37:41 GMT [2507-1] LOG:  database system was shut down at 2021-09-01 19:37:38 GMT
2023-09-01 19:37:41 GMT [2506-1] LOG:  database system is ready to accept connections

We’ll explain some more about these logs once we’ve located the files.

Getting ready

You’ll need to get operating system access to the database system, which is what we call the platform on which the database runs.

The server log can be in a few different places, so let’s list all of them first so that we can locate the log or decide where we want it to be placed:

• It may be in a directory beneath the data directory.
...

Each database server has a system identifier assigned when the database is initialized (created). The server identifier remains the same if the server is backed up, cloned, and so on.

Many actions on the server are keyed to the system identifier, and you may be asked to provide this information when you report a fault.

In this recipe, you will learn how to display the system identifier.

Getting ready

You need to connect as the Postgres OS user, or another user with execute privileges on the server software.

How to do it…

In order to display the system identifier, we just need to launch the following command:

pg_controldata <data-directory> | grep "system identifier"
Database system identifier:            7015545877453537036

Note that the preceding syntax will not work on Debian or Ubuntu systems, because of the choice of offering a multi-version directory structure. That choice...

When we connect to PostgreSQL, we always connect to just one specific database on any database server. If there are many databases on a single server, it can get confusing, so sometimes you may just want to find out which databases are parts of the database server.

This is also confusing because we can use the word database in two different, but related, contexts. Initially, we start off by thinking that PostgreSQL is a database in which we put data, referring to the whole database server by just the word database. In PostgreSQL, a database server (also known as a cluster) is potentially split into multiple, individual databases, so as you get more used to working with PostgreSQL, you’ll start to separate the two concepts.

How to do it…

If you have access to psql, you can type the following command:

$ psql -l
                                                       List of databases
   Name    |  Owner   | Encoding...

The number of tables in a relational database is a good measure of the complexity of a database, so it is a simple way to get to know any database. But what kind of complexity exactly? Well, a database may be complex because it has been designed to be deliberately flexible, in order to cover a variety of business situations. It could also be that a complex business process may have a limited portion of its details covered in the database. So a large number of tables might reveal a complex business process or just a complex piece of software.

In this recipe, we will show you how to compute the number of tables.

How to do it...

From any interface, type the following SQL command:

SELECT count(*) FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','pg_catalog');

You can also look at the list of tables directly and judge whether the list is small or large.

In psql, you can see...

It is very important to allocate sufficient disk space for your database. If the disk gets full, it will not corrupt the data, but it might lead to database server panic and then consequent shutdown.

For planning or space monitoring, we often need to know how big a database currently is, so that we get a hint of how fast it grows and are not caught by surprise later on.

How to do it...

We can do this in the following ways:

• Look at the size of the files that make up the database server.
• Run a SQL request to confirm the database size. If you look at the size of the actual files, you’ll need to make sure that you include the data directory and all subdirectories, as well as all other directories that contain tablespaces. This can be tricky, and it is also difficult to break down all the different pieces.

The easiest way is to ask the database a simple query, like this:

SELECT pg_database_size(current_database...

This is one of those questions that are quick and simple when asked, but not so much when being answered.

PostgreSQL uses memory in a dynamic way across its databases, which is why the question includes the word “currently.” By this, we mean that the shared buffers managed by a database server are used concurrently by all its databases. This is based on demand, so if a database is unused, then the amount of memory it uses will decrease, because the same memory will gradually be reused to cache data files from busier databases.

How to do it…

The largest amount of memory is usually consumed for the shared buffers. This is an area that is shared among all the sessions; e.g., if multiple sessions are working on the same table at the same time, there is only one copy of each table block in the shared buffers.

The pg_buffercache extension provides a view that has one row for each buffer page. It can be installed...

The maximum supported table size in the default configuration is 32 TB, and it does not require large file support from the operating system. The filesystem size limits do not impact the large tables, as they are stored in multiple 1 GB files.

Large tables can suffer performance issues. Indexes can take much longer to update, and query performance can degrade. In this recipe, we will see how to measure the size of a table.

How to do it…

We can see the size of a table by using this command:

cookbook=# select pg_relation_size('pgbench_accounts');

The output of this command is as follows:

pg_relation_size
------------------
         13434880
(1 row)

We can also see the total size of a table, including indexes and other related spaces, as follows:

cookbook=# select pg_total_relation_size('pgbench_accounts');

The output is as follows:

pg_total_relation_size
---------------------...

We’ve looked at getting the size of a specific table, so now it’s time to widen the problem to related areas. Rather than having an absolute value for a specific table, let’s look at the relative sizes.

How to do it...

The following basic query will tell us the 10 biggest tables:

SELECT quote_ident(table_schema)||'.'||quote_ident(table_name) as name
      ,pg_relation_size(quote_ident(table_schema)
              || '.' || quote_ident(table_name)) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

The tables are shown in descending order of size, with at the most 10 rows displayed, as in the following example:

          name           |   size   
-------------------------+----------
 public.pgbench_accounts | 13434880
 public.pgbench_branches |     8192
 public.pgbench_tellers  |     8192
 public...

There is no limit on the number of rows in a table, but the table is limited to available disk space and memory/swap space. If you are storing rows that exceed an aggregated data size of 2 KB, then the maximum number of rows may be limited to 4 billion or fewer.

Counting is one of the easiest SQL statements, so it is also many people’s first experience of a PostgreSQL query.

How to do it…

From any interface, the SQL command used to count rows is as follows:

SELECT count(*) FROM table;

This will return a single integer value as the result.

In psql, the command looks like the following:

cookbook=# select count(*) from orders;
count
-------
   345
(1 row)

How it works...

PostgreSQL can choose between two techniques available to compute the SQL count(*) function. Both are available in all the currently supported versions:

• The first is called sequential scan. We access every data block in...

We don’t always need an accurate count of rows, especially on a large table that may take a long time to execute. Administrators often need to estimate how big a table is so that they can estimate how long other operations may take.

How to do it…

The Postgres optimizer can provide a quick estimate of the number of rows in a table, simply by using its statistics:

EXPLAIN SELECT * FROM mytable;
                          QUERY PLAN                            
----------------------------------------------------------------
Seq Scan on mytable  (cost=0.00..2640.00 rows=100000 width=97)
(1 row)

You can see that the optimizer estimates 100,000 rows in output. We can directly compute a similar number using roughly the same calculation:

SELECT (CASE WHEN reltuples > 0 THEN pg_relation_size(oid)*reltuples/(8192*relpages)
ELSE 0
END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = 'mytable&apos...

Every PostgreSQL database contains some objects that are automatically brought in when the database is created. Every user will find a pg_database system catalog that lists databases, as shown in the Listing databases on the database server recipe. There is little point in checking whether these objects exist because even superusers are not allowed to drop them.

On the other hand, PostgreSQL comes with tens of collections of optional objects, called modules, or equivalently extensions. The database administrator can install or uninstall these objects, depending on the requirements. They are not automatically included in a newly created database because they might not be required by every use case. Users will install only the extensions they actually need, when they need them; an extension can be installed while a database is up and running.

In this recipe, we will explain how to list extensions that have been installed on the current database...

In most databases, there will be dependencies between objects in a 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 the dependencies:

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...