PostgreSQL two databases on the same server.

Colleagues hello to all.

In today's article, I'll show you how to create two PostgreSQL databases on the same server. You can create as many databases as you want on the server. It is possible to create two databases in cases where you want to have one production and one test database.

For you to understand this article well, you need to see the article on how I install the PostgreSQL DBMS from the source files.

Download and install Postgresql 14 on Linux CentOS/RHEL 8/7 TAR

Article content:

1. Creating environment variable files.
2. Create directories for databases.
3. Initializing databases.
4. Editing configuration files.
5. Launching the databases.
6. Check.

1. Creating environment variable files.

At the very beginning, let's create a  environment file for each database. The files will be called db_prod.env and db_test.env

$. vim /home/postgres/db_prod.env

$. vim /home/postgres/db_test.env

In db_prod.env file we write:

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

In db_test.env file we write:

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

You will end up with two files.

The difference in these files is that our databases will be in separate directories and will work on different ports.

2. Create directories for databases.

Now it's time to create directories for our databases.

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

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

3. Initializing databases.

Now we need to initialize the databases. Initialization is done using the initdb utility.

Initialize db_prod database:

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

Initialize db_test database:

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

4. Editing configuration files.

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

On db_prod change the port from 5432 to 5777

On db_test change the port from 5432 to 5888

5. Launching the databases.

After all that we have done, we can now start our databases. We use our own environment for each database and start the database through the pg_ctl utility.

. db_prod.env
pg_ctl start

. db_test.env
pg_ctl start

As you can see from the screenshots, each database works under its own port.

6. Check.

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

$. ps aux | grep postgres

Thank you all, I hope that my article helped you in some way.