How to Set Up Multiple PostgreSQL Instances on a Single Server
Tags: postgres, ubuntu, production, postgressql
Published: 2024-04-10 20:00:14

 
#Introduction
PostgreSQL has gained so much popularity in recent years due to its robust features. These features include the storage of advanced data structures (e.g., JSON, arrays), scalability for high-traffic environments, and a rich set of functionalities specifically designed for data handling. Additionally, PostgreSQL offers many other benefits.

In this short article, I will briefly discuss a simple way to create multiple PostgreSQL instances on a single machine. This is a very common scenario, depending on your usage. In my case, I wanted to test two services that will coexist in different environments.

#Requirements
- Installed PostgreSQL (Any version)
- Ubuntu (any version)

We'll assume you already have a running PostgreSQL server on port 5432.

##Steps of creating a second instance

1. 
Open the bash and run the following command
```bash
$sudo pg_createcluster 12 main1 --port=5433 --start
```
Use name of your choice for "main1" folder and the port number.Use any port number but not 5432 as By default, PostgreSQL is configured to run on port 5432. If you want to run a second instance, you'll need to configure it to use a different port. 
This command creates another PostgreSQL cluster named main1 and 
starts it on port 5433.

2.
Running this command will result to this:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2lsy5whpinzr59k5gsot.png)

3.
Modify Postgres configuration file to allow change of password:
When you try to access the new service, you will be denied entry. To ease the process of changing your password, navigate to this folder and open the pg_hba.conf file with your favorite text editor.  Please note that the version of PostgreSQL may differ.
```bash
$sudo nano /etc/postgresql/12/main3/pg_hba.conf
```
4.
Scroll down to this section:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/juc9ci8tk8bp54hep9gx.png)
and make the following changes:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cf48nj0s7gwwe0a5fuuy.png)
####NB:
Remember to revert these changes back to the original configuration after resetting your passwords.  These changes may pose a threat to your database security.
5.
Accessing the Instances:
```bash
$sudo su postgres
$psql -U postgres -h localhost -p 5433 -W
```
6.
Change the password:
```bash
psql$\password postgres
```
```bash
psql$\q
```
restart the server.
```bash
sudo service postgreSQL restart 5433
```
And update the Postgres configuration file back to the original settings.
```bash
sudo nano /etc/postgresql/12/main3/pg_hba.conf
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kmr66ysul8k0a40q9ck9.png)

to check the number of postgres instance running, use the following command:
```bash
ps -aux
```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2p95zson5dixvs4w33vs.png)
for my case you can see I have 3 instances running

###Useful commands to manage your instances
for this example, we shall assume the service is in folder main2
- To start service
```bash
sudo pg_ctlcluster 12 main2 start
```
- To stop service
```bash
sudo pg_ctlcluster 12 main2 stop
```
- To restart service
```bash
sudo pg_ctlcluster 12 main2 restart
```
Thank you!