PostgreSQL two databases on the same server.

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

postgresql_two_database

 

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

postgresql_two_database

 

You will end up with two files.

postgresql_two_database

 

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

postgresql_two_database


 

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

postgresql_two_database

 

Initialize db_test database:

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

postgresql_two_database


 

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

postgresql_two_database

 

On db_test change the port from 5432 to 5888

postgresql_two_database


 

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

postgresql_two_database

 

. db_test.env
pg_ctl start

postgresql_two_database

 

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

postgresql_two_database


 

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

SIMILAR ARTICLES

PostgreSQL - How to determine which files on disk correspond to tables in a database
PostgreSQL - How to determine which files on disk correspond to tables in a database
POSTGRESQL
Read more
PostgreSQL - How to find out the size of tables in a database
PostgreSQL - How to find out the size of tables in a database
POSTGRESQL
Read more
PostgreSQL - How to check user privileges
PostgreSQL - How to check user privileges
POSTGRESQL
Read more