DEV Community

Cover image for How to Set Up Multiple PostgreSQL Instances on a Single Server
Tito
Tito

Posted on

How to Set Up Multiple PostgreSQL Instances on a Single Server

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


Enter fullscreen mode Exit fullscreen mode

4.
Scroll down to this section:
Image description
and make the following changes:
Image description

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:



$sudo su postgres
$psql -U postgres -h localhost -p 5433 -W


Enter fullscreen mode Exit fullscreen mode

6.
Change the password:



psql$\password postgres


Enter fullscreen mode Exit fullscreen mode


psql$\q


Enter fullscreen mode Exit fullscreen mode

restart the server.



sudo service postgreSQL restart 5433


Enter fullscreen mode Exit fullscreen mode

And update the Postgres configuration file back to the original settings.



sudo nano /etc/postgresql/12/main3/pg_hba.conf


Enter fullscreen mode Exit fullscreen mode

Image description

to check the number of postgres instance running, use the following command:



ps -aux


Enter fullscreen mode Exit fullscreen mode

Image description
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


Enter fullscreen mode Exit fullscreen mode
  • To restart service ```bash

sudo pg_ctlcluster 12 main2 restart

Thank you!

Enter fullscreen mode Exit fullscreen mode

Top comments (2)

Collapse
 
brandonirizarry profile image
BrandonIrizarry

For setting the password for 'main1' (running on GNU/Linux), I was able to first switch to my postgres user (named 'postgres' in my case) :

$ su - postgres

From there, I entered into the psql repl using the appropriate port number:

$ psql -p 5433

Inside this repl, I issued the '\password' command:


postgres=# \password
Enter new password for user "postgres":
Enter it again:
postgres=# quit

From there, I was able to register the server (main1) with pgAdmin. No editing of config files needed. HTH

Collapse
 
whoami_b2499e27c68a9b3027 profile image
whoami

psql -U postgres -h localhost -p 5433 -W

This command is asking for password, tried "postgres" but didn't work. How do I reset this. I have update the trust keyword in correct conf file. Help me