PostgreSQL Cluster

A PostgreSQL cluster is a collection of several databases that all run under the very same PostgreSQL service or instance.

(Ferrari and Pirozzi 2023, 22)

Locating the database server files

Cluster files are initially stored in a location referred to as data directory:

SHOW data_directory;

$PGDATA is where the database files live. You can also use initdb to create the database directory:
```
    initdb -D <path>
```

Cluster Management with `pg_ctl`

Starting and Stopping the Cluster

  # wait for all connections...
  pg_ctl stop -m s[mart]
  # force disconnect
  pg_ctl stop -m f[ast]
  # SIGQUIT
  pg_ctl stop -m i[mmediate]

Restarts

Reload

The Console (`psql`)

Most commonly used options:

Options
`--host` or `-h`
`--port` or `-p`
`--username` or `-u`
`--dbname` or `-d`

The command \? shows all \ commands available. Some useful commands are:

  # lists all databases in the cluster
  \l
  # adds additional information
  \l+
  # switches to a different database in the cluster
  \c <dbname>

to gather information about database objects (in the current database), use different variations of \d:

  -- tables
  \dt
  -- sequences
  \ds
  -- indexes
  \di
  -- schemas
  \dn
  -- functions
  \df
  -- users/roles
  \du
  -- extensions installed
  \dx

References:

Ferrari, Luca, and Enrico Pirozzi. 2023. Learn Postgresql: Use, Manage, and Build Secure and Scalable Databases with Postgresql 16. 2nd ed. Packt Publishing Ltd.