What is a Database Cluster: PostgreSQL

In the last blog, we have installed PostgreSQL on different Linux platforms ( CentOs, Ubuntu). However, We have leftover to initialize the installation and connection to the database that we will discuss here.

Postgres provides more than one way to initialize a database cluster. We will use pg_createcluster in this blog. But before that, we should understand what is a cluster in PostgreSQL terms.

A cluster in Postgres is a collection of all databases which is managed by one single instance running on a database server. For example, if we want to set up a development environment database, we can create a database cluster on a DEV server and it will contain more than one database like employee, department etc. including few ones which are created by the Postgres itself. These are postgres, template0 & template1.

We should have to perform a master-slave setup if we want our cluster to work in replication mode or HA mode.

Let’s get back to the initialization task. To do this, we will explore pg_createcluster. The command we will use here is:

pg_createcluster <version> <clustername> — –data-checksums

Let’s explore syntax first. The first option we have used is <version> and this is the version of the DB we are using. Since we are using version 11.9, the value for the <version> would be 11 here.

The name of the cluster would be required in order to identify the newly created cluster. We may provide any name but for this blog, we will use pg01.

data-checksums is the one which helps detecting corruption in data pages by I/O blocks. Although, this may lead to some performance penalty, however, this is very useful feature in order to identify if any data pages are corrupted. Most importantly, this option can be set only during database initialization and can not be undone latter. Also, this is set on cluster level which means it will be used for all databases of that cluster.

postgres@sanjeeva/home/sanjeeva/postgres$ pg_createcluster 11 pg04 — –data-checksum
Creating new PostgreSQL cluster 11/pg04 …
/usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/pg04 –auth-local peer –auth-host md5 –data-checksum
The files belonging to this database system will be owned by user “postgres”.
This user must also own the server process.
The database cluster will be initialized with locale “C.UTF-8”.
The default database encoding has accordingly been set to “UTF8”.
The default text search configuration will be set to “english”.
Data page checksums are enabled.
fixing permissions on existing directory /var/lib/postgresql/11/pg04 … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default timezone … Asia/Calcutta
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
Success. You can now start the database server using:
pg_ctlcluster 11 pg04 start
Ver   Cluster   Port   Status     Owner     Data directory                             Log file
11     pg04     5432   down     postgres   /var/lib/postgresql/11/pg04     /var/log/postgresql/postgresql-11-pg04.log
postgres@sanjeeva/home/sanjeeva/postgres$

The newly created cluster will be listed here in red color because this cluster will not be in the start state. Once we will start this cluster, and if it is healthy it will be listed in green color. Let’s start the cluster using another cluster command.

postgres@sanjeeva:/home/sanjeeva/postgres$ pg_ctlcluster 11 pg04 start
postgres@sanjeeva:/home/sanjeeva/postgres$

This command will return silent. To validate if the cluster is started, use another cluster command

postgres@sanjeev4156:/home/sanjeeva/postgres$ pg_lsclusters
Ver   Cluster   Port   Status     Owner     Data directory                             Log file
11     pg04     5432   down     postgres   /var/lib/postgresql/11/pg04     /var/log/postgresql/postgresql-11-pg04.log

Now we can connect to database using psql or pgAdmin or any other PostgreSQL client. We will use psql here.

postgres@sanjeeva:/home/sanjeeva/postgres$ psql -p 5432
psql (11.9 (Ubuntu 11.9-1.pgdg18.04+1))
Type “help” for help.
postgres=#