Database Cluster in PostgreSQL

1. What is a Database Cluster
2. Advantages of a Database Cluster
3. How Does a Database Cluster Work in PostgreSQL
4. Conclusion

This article describes a database cluster and how to create one in PostgreSQL. Often, people confuse this term and cannot grasp its concept.

This article is formulated to expand upon the concept of a database cluster.

Commands to create and set up a database cluster are included in this article to help you get started with it right away. Follow the commands mentioned in the article exactly.

Note: The commands mentioned in the article have been tested on Ubuntu and might not work properly on any other operating system.

What is a Database Cluster

Let us understand the term “database cluster” in simple terms. Several servers or instances connecting to a single database are called database clustering.

Note: An instance is a group containing physical files to store the data. It can be defined as a set of memory and processes that interact with a database.

In other words, a database cluster can be defined as a group of several databases that are managed by only a single instance of a running database server.

Each database cluster has a separate configuration and operates on a different port number. For instance, you might have a database cluster that uses PostgreSQL v9.4 and has three associated databases.

All the databases in the cluster would use the same configuration provided by the cluster, for example, the connection pool size, buffer size, number of connections allowed, etc.

Similarly, numerous other clusters operate with a different PostgreSQL version and have different configurations.

Advantages of a Database Cluster

There are several advantages of database clustering. Some of the main advantages have been listed below.

1. Load balancing or database scalability
2. Increased database availability
3. Data redundancy that allows data backup
4. Monitoring and automation of the database

How Does a Database Cluster Work in PostgreSQL

To start with the process of creating a cluster in PostgreSQL, you need to initialize a storage area for the database on the disk.

After the initialization, a database named postgres would be set in the cluster, which acts as a default database used by third-party applications, users, and utilities.

In addition to the default database, another database is created within each cluster with the name template1. This database is not used for actual work; instead, it is used as a template for databases created later in the cluster.

A database cluster acts as a single directory to store all the data, known as the data directory or the data area.

Create or Initialize the Database Cluster

You can initialize the database cluster at any location of your choice using the following commands.

$	initdb -D /usr/local/pgsql/data

Note: The command must be executed after logging into the PostgreSQL user account.

The second command also helps in creating (initializing) a database cluster.

$	pg_ctl -D /usr/local/pgsql/data initdb

Both of these commands create a directory at the desired location.

Number of Active Database Clusters

After creating a cluster, you might want to count the active number of clusters on your OS. For this matter, you can use the following command.

$ pg_lsclusters

The command returns a list of active clusters, their status, port numbers, names, directory locations, etc. The command also informs if each cluster is active or offline.

This is an essential piece of information required when connecting to a cluster. You can only connect to active clusters instead of offline ones.

Connect to Another Database Cluster

You might feel the need to connect to a different database cluster to balance the load on one database server or to enhance the performance of the existing database server.

Whatever the reason for connecting the clusters, you can use the following command.

$ psql -U postgres -p 5436-h localhost

It is essential to use the correct port number for the cluster that you are connecting to because each cluster has a different port number on which it is available.

Note: You can check the port number of each cluster using the command pg_lsclusters that returns the list of active clusters along with their information.

Edit the Status of the Database Cluster

If you want to edit the status of the database cluster from start to stop, or vice versa, you can run the following command.

$  pg_ctlcluster 12 main stop

The command allows you to start or stop a database cluster. In the command, 12 is the version number of the PostgreSQL software, and main is the name of the cluster you edit.

Database Creating in a Cluster

You can create new databases in existing clusters with the help of one command only. However, ensuring that the cluster is active is essential because you need to connect to it before creating a new database within it.

Note: Use the command mentioned above, pg_lsclusters, to check if the cluster you want to create a database in is active or not.

Once you are sure the cluster is active, use the following command.

$ CREATE DATABASE mynewdb

The command creates a new database within the cluster that you connect with.

Conclusion

All the commands required for creating and executing a database cluster in PostgreSQL have been mentioned in the article with a detailed explanation. Following the discussion in the article would help you execute the process more quickly.