Getting to Know Your Cluster

In order to be a proficient user and administrator of a PostgreSQL cluster, you first have to know and understand how PostgreSQL works. A database system is a very complex beast, and PostgreSQL, being an enterprise-level Database Management System (DBMS), is in no way a simple software system. However, thanks to a very good design and implementation, once you understand the basic concepts and terminology of PostgreSQL, things will quickly become comprehensive and clear.

This chapter will introduce you to the main PostgreSQL terminology and concepts, as well as teach you how to connect to the cluster. This chapter will also introduce you to the psql client, which ships with PostgreSQL and is the recommended way to connect to your database. You can, of course, use any client that supports PostgreSQL to connect to the database, and the rules explained...

Technical requirements

What you need to know for this chapter is as follows:

• How to install binary packages on your Unix machine
• Basic Unix command-line usage
• Basic SQL statements

You can find the code for this chapter in the following GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL.

Managing your cluster

From an operating system point of view, PostgreSQL is a service that can be started, stopped, and, of course, monitored. As you saw in the previous chapter, usually when you install PostgreSQL, you also get a set of operating system-specific tools and scripts to integrate PostgreSQL with your operating system service management (for example, systemd service files).

In particular, PostgreSQL ships with a tool called pg_ctl that helps in managing the cluster and the related running processes. This section introduces you to the basic usage of pg_ctl and to the processes that you can encounter in a running cluster.

pg_ctl

The pg_ctl command-line utility is a tool that allows you to perform different actions on a cluster, mainly initialize it, start it, restart and stop it, and so on. pg_ctl accepts the command to execute as the first argument, followed by other specific arguments—the main commands are as follows:

• start, stop, and restart execute the corresponding...

Connecting to the cluster

Once PostgreSQL is running, it awaits incoming database connections to serve; as soon as a connection comes in, PostgreSQL serves it by connecting the client to the right database. This means that in order to interact with the cluster, you need to connect to it. However, you don't connect to the whole cluster; rather, you ask PostgreSQL to interact with one of the databases the cluster is serving. Therefore, when you connect to the cluster, you need to connect to a specific database. This also means that the cluster must have at least one database from the very beginning of its life. That is the role of the so-called template databases, which, among other duties, serve as a common database to which you can connect on a freshly installed cluster.

When you initialize the cluster with the initdb command, PostgreSQL builds the filesystem layout of the PGDATA directory and builds two template databases, named template0 and template1. The aim of these databases...

Exploring the disk layout of PGDATA

In the previous sections, you have seen how to install PostgreSQL and connect to it, but we have not looked at the storage part of a cluster. Since the aim of PostgreSQL, as well as the aim of any relational database, is to permanently store data, the cluster needs some sort of permanent storage. In particular, PostgreSQL exploits the underlying filesystem to store its own data. All of the PostgreSQL-related stuff is contained in a directory known as PGDATA.

The PGDATA directory acts as the disk container that stores all the data of the cluster, including the users' data and cluster configuration.

The following is an example of the content of PGDATA for a running PostgreSQL 12 cluster (it looks the same for a PostgreSQL 13 instance):

$ sudo ls -1 /postgres/12
PG_VERSION
base
global
pg_commit_ts
pg_dynshmem
pg_hba.conf
pg_ident.conf
pg_logical
pg_multixact
pg_notify
pg_replslot
pg_serial
pg_snapshots
pg_stat
pg_stat_tmp
pg_subtrans
pg_tblspc
pg_twophase
pg_wal
pg_xact...

Exploring configuration files and parameters

The main configuration file for PostgreSQL is postgresql.conf, a text-based file that drives the cluster when it starts.
Usually, when changing the configuration of the cluster, you have to edit the postgresql.conf file to write the new settings and, depending on the context of the settings you have edited, to issue a cluster SIGHUP signal (that is, reload the configuration) or restart it.

Every configuration parameter is associated with a context, and depending on the context, you can apply changes with or without a cluster restart. In particular, available contexts are the following:

• internal: A group of parameters that are set at compile-time and therefore cannot be changed at runtime.
• postmaster: All the parameters that require the cluster to be restarted (that is, to kill the postmaster process and start it again) to activate them.
• sighup: All the configuration parameters that can be applied with a SIGHUP signal sent to the postmaster...

Summary

PostgreSQL can handle several databases within a single cluster, served out of disk storage contained in a single directory named PGDATA. The cluster runs many different processes; one, in particular, is named postmaster and is in charge of spawning other processes, one per client connection, and keeping track of the status of maintenance processes.

The configuration of the cluster is managed via text-based configuration files, the main one being postgresql.conf. It is possible to drive the cluster, by means of postmaster, to recognize allowed user connections by means of rules placed in the pg_hba.conf text file.

You can interact with the cluster status by means of the pg_ctl tool or, depending on your operating system, by other provided programs, such as service.

This chapter has presented you with all of the preceding information so that you are able not only to install PostgreSQL but also to start and stop it regularly, integrate it with your operating system, connect to the...

References

• PostgreSQL PGDATA disk layout: https://www.postgresql.org/docs/12/storage-file-layout.html
• PostgreSQL initdb official documentation: https://www.postgresql.org/docs/12/app-initdb.html
• PostgreSQL pg_ctl official documentation: https://www.postgresql.org/docs/12/app-pg-ctl.html
• The pgAdmin4 graphical client for PostgreSQL: https://www.pgadmin.org/