Setting Up Postgresql Database for Servers

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

Switch to the PostgreSQL user to perform the needed administrative tasks

sudo -i -u postgres

# opening  the sql prompt
psql

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;

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';

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;

Exit the PostgreSQL prompt:

\q

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

sudo vim /etc/postgresql/13/main/postgresql.conf

Locate the listen_addresses line and change to:

listen_addresses = "*"

Open the pg_hba.conf file

sudo vim /etc/postgresql/13/main/pg_hba.conf

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

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

sudo systemctl restart postgresql
sudo systemctl enable postgresql 

Allow connections on port through firewall

sudo ufw allow 5432/tcp

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

Happy Reading and Learning