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

$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:

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.

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

4.
Scroll down to this section:

and make the following changes:

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

6.
Change the password:

psql$\password postgres

psql$\q

restart the server.

sudo service postgreSQL restart 5433

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

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

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

ps -aux

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

sudo pg_ctlcluster 12 main2 start

• To stop service

sudo pg_ctlcluster 12 main2 stop

• To restart service

sudo pg_ctlcluster 12 main2 restart

Thank you!

Comments

[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

[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