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
List of required tools and software (e.g., PostgreSQL, SSH client)
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
Top comments (0)
Some comments may only be visible to logged-in visitors. Sign in to view all comments.