Home/ Questions/Q 994

Querify Question Shop: Explore Expert Solutions and Unique Q&A Merchandise Latest Questions

Author
  • 62k
Asked: 2024-11-25T04:01:09+00:00

Setting Up Postgresql Database for Servers

  • 62k

posgresql image

Introduction

PostgreSQL is an advanced object relational database sytem that uses and also extends the SQL language. The good thing about postgreSQL that it is Open source.

Developers and Database administrators alike use postgreSQL because of it's high data consistencey and data integrity which proves to be more reliable than other SQL databases.

This is a stage 4 task that I worked on as HNG11 intern, It was a team based task, the team consisted of 6 members.

This post will cover

  • Installation of postgreSQL

  • Creation of the Database and user

  • Configuring PostgreSQL

Prerequisites

  1. List of required tools and software (e.g., PostgreSQL, SSH client)

  2. Basic knowledge required (e.g., familiarity with terminal commands, server access)

Install PostgreSQL along with its additional features 

sudo apt install postgresql postgresql-contrib
Enter fullscreen mode Exit fullscreen mode

Switch to the PostgreSQL user to perform the needed administrative tasks 

sudo -i -u postgres  # opening  the sql prompt psql
Enter fullscreen mode Exit fullscreen mode

Within the PostgreSQL prompt, create the required databases for the production, staging and development environments 

CREATE DATABASE langlearnai_be_staging_db; CREATE DATABASE langlearnai_be_main_db; CREATE DATABASE langlearnai_be_dev_db;
Enter fullscreen mode Exit fullscreen mode

Create Users and assign passwords for each enviroment 

CREATE USER langlearnai_be_staging_user WITH ENCRYPTED PASSWORD 'staging_password'; CREATE USER langlearnai_be_main_user WITH ENCRYPTED PASSWORD 'main_password'; CREATE USER langlearnai_be_dev_user WITH ENCRYPTED PASSWORD 'dev_password';
Enter fullscreen mode Exit fullscreen mode

Grant the necessary Privileges to Users 

GRANT ALL PRIVILEGES ON DATABASE langlearnai_be_staging_db TO langlearnai_be_staging_user; GRANT ALL PRIVILEGES ON DATABASE langlearnai_be_main_db TO langlearnai_be_main_user; GRANT ALL PRIVILEGES ON DATABASE langlearnai_be_dev_db TO langlearnai_be_dev_user; GRANT ALL PRIVILEGES ON SCHEMA public TO langlearnai_be_staging_user; GRANT ALL PRIVILEGES ON SCHEMA public TO langlearnai_be_main_user; GRANT ALL PRIVILEGES ON SCHEMA public TO langlearnai_be_dev_user;
Enter fullscreen mode Exit fullscreen mode

Exit the PostgreSQL prompt: 

q
Enter fullscreen mode Exit fullscreen mode

Configure and modified the Postgres configuration files to allow PostgreSQL to Listen on External IP 

sudo vim /etc/postgresql/13/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Locate the listen_addresses line and change to: 

listen_addresses = "*"
Enter fullscreen mode Exit fullscreen mode

Open the pg_hba.conf file 

sudo vim /etc/postgresql/13/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Add the following lines under IPV4 local connections to allow external access. i.e These lines configure PostgreSQL to accept connections from the specified IP address 

# IPv4 local connections host    all             all             0.0.0.0/0               md5 host    postgres        postgres        <server-ip-address>/32      md5 host    langlearnai_be_dev_db  langlearnai_be_dev_user  <server-ip-address>/32    md5 host    langlearnai_be_main_db  langlearnai_be_main_user  <server-ip-address>/32    md5 host    langlearnai_be_staging_db  langlearnai_be_staging_user  <server-ip-address>/32    md5
Enter fullscreen mode Exit fullscreen mode

Restart to apply changes and enable the PostgreSQL service to start on system boot 

sudo systemctl restart postgresql sudo systemctl enable postgresql
Enter fullscreen mode Exit fullscreen mode

Allow connections on port through firewall 

sudo ufw allow 5432/tcp
Enter fullscreen mode Exit fullscreen mode

Accessing the Database

To connect to the PostgreSQL database remotely, use the following command 

psql -h your_server_ip -U your_database_username -d your_database_name
Enter fullscreen mode Exit fullscreen mode

Happy Reading and Learning

backenddatabasedevopswebdev
Share

Leave an answer

You must login to add an answer.

Need An Account,