Two PostgreSQL clusters on one server: step-by-step guide

Running two PostgreSQL clusters on a single physical or virtual server can be useful for a variety of tasks, such as testing database updates, isolating development and production environments, or managing multiple clusters with different configurations. However, this approach requires careful configuration to avoid conflicts with ports, data directories, and network settings.

In this article, we will walk through the steps to successfully run two PostgreSQL clusters on a single server. The first cluster will be responsible for production databases, and the second cluster will be responsible for test databases.

The article on the page is based on my many years of experience with this technology and is presented to you as a short instruction, but if you want to get acquainted with it in more detail, then I recommend that you go to the official documentation on PostgreSQL.

 

Contents of the article:

1. Creating environment variable files.
2. Creating directories for clusters.
3. Initialization of the crusters.
4. We edit configuration files.
5. Launching clusters.
6. Checking the result.

 

1. Creating environment variable files.

The first thing we'll do is create environment variable files for two PostgreSQL clusters. The files will be called db_prod.env and db_test.env.

Create an environment variable file db_prod.env. This file will be responsible for production databases in the cluster.

$. vim /home/postgres/db_prod.env

Contents of db_prod.env file.

export PGPASSWORD='Qwerty1'
export PGHOME=/app/postgresql
export LD_LIBRARY_PATH=/app/postgresql/lib
export PGDATA=/app/postgresql/pgdatabase/db_prod
export PGPORT=5777
export PATH=$PGHOME/bin:$PGDATA:$PATH

db_prod.env

Create a file of environment variables db_test.env. This file will be responsible for test databases in the cluster.

$. vim /home/postgres/db_test.env

Contents of db_test.env file.

export PGPASSWORD='Qwerty1'
export PGHOME=/app/postgresql
export LD_LIBRARY_PATH=/app/postgresql/lib
export PGDATA=/app/postgresql/pgdatabase/db_test
export PGPORT=5888
export PATH=$PGHOME/bin:$PGDATA:$PATH

db_test.env

As a result, you will have two environment variable files, each file responsible for a separate cluster.

The difference between the clusters is that they will be launched under different ports. The prod cluster will be launched on port 5777, and the test cluster on 5888, and the data of the clusters themselves will be stored in different directories.

---

 

2. Creating directories for clusters.

In this step we must create two directories for clusters, each of the directories will be responsible for its own cluster. To create directories, use the command:

$. mkdir -p /app/postgresql/pgdatabase/db_test

$. mkdir -p /app/postgresql/pgdatabase/db_prod

---

 

3. Initialization of the crusters.

At this step we initialize the clusters. We do the initialization using the built-in utility initdb.

Initialize the db_prod cluster:

$. /app/postgresql/bin/initdb -D /app/postgresql/pgdatabase/db_prod -U postgres -W

Initializing a production (db_prod) cluster

Initialize the db_test cluster:

$. /app/postgresql/bin/initdb -D /app/postgresql/pgdatabase/db_test -U postgres -W

Initializing a test (db_test) cluster

---

 

4. We edit configuration files.

At this step we need to set the correct values ​​of the port parameter in the main configuration file postgresql.conf on each cluster. Do not forget that each cluster will be launched under its own port. Launching two clusters from one port will not work.

On db_test and db_prod we edit the configuration file postgresql.conf.

On the db_prod database cluster, change the port from 5432 to 5777

Production (db_prod) cluster

On the db_test database cluster, we change the port from 5432 to 5888

Test (db_test) cluster

---

 

5. Launching clusters.

After all that we have done, we can now launch the clusters. We apply our own environment for each cluster and launch the cluster via the pg_ctl utility.

. db_prod.env
pg_ctl start

db_prod.env

. db_test.env
pg_ctl start

db_test.env

As we can see from the screenshots, each cluster is launched under its own port.

---

 

6. Checking the result.

To check, we can check which processes are running, for this we will use the command:

$. ps aux | grep postgres

As a result, we get that separate DBMS processes are launched for each cluster.

---

 

In the end, running two PostgreSQL DBMS clusters on one server is a real and effective task if configured correctly. The key points are a clear separation of ports, data directories and configuration parameters. Following the configuration recommendations allows you to avoid conflicts and ensure stable operation of the clusters.

This approach opens up a wide range of possibilities: from testing new versions to managing different development environments on a single physical server. It is important to remember about monitoring the load and possible resource limitations of the physical server to avoid performance degradation. Following the steps in this article, you can customize PostgreSQL to your needs while maintaining stability and performance of the entire system.