DEV Community

Otobong Edoho
Otobong Edoho

Posted on

Setting Up Postgresql Database for Servers

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

Top comments (0)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.