Introduction

PostgreSQL is a robust database management system (DBMS) with a strong emphasis on extensibility and SQL compliance. Boasting 20 years of open-source development, it supports both SQL (relational) and JSON (non-relational) querying. Widely used in web, mobile, and analytics applications, PostgreSQL is a powerful choice for managing your data.

Install PostgreSQL

Update package information before installation:

sudo apt update

Install PostgreSQL along with necessary extensions:

sudo apt install postgresql postgresql-contrib

Verify PostgreSQL status:

service postgresql status

The output should confirm that the PostgreSQL daemon is active.

Start Using PostgreSQL Command Line Tool

Log in as the default admin user "postgres":

sudo -u postgres psql

This also connects you to the default database named "postgres."

Check the connection details:

\conninfo

You should see that you are connected to the "postgres" database as user "postgres."

Check PostgreSQL Version & Available Databases

To check the PostgreSQL version:

postgres --version

or

postgres -V

View a list of available databases:

\l

If you want to get more information, you can use the \l+ command:

\l+

Set Password & Create Users

Set a password for the default "postgres" user:

\password postgres

Create users with specific roles and permissions:

sudo -u postgres createuser -e project_udev
sudo -u postgres createuser -d -r -e project_utest
sudo -u postgres createuser --superuser project_ulive

Alternatively, use the CREATE USER PSQL statement:

CREATE USER project_ulive SUPERUSER;

List all users and roles:

\du

Use PSQL to create a user with a password:

CREATE USER project_ulive WITH PASSWORD 'Live@Project123';

If the user already exists, add the password by using ALTER USER:

ALTER USER project_udev WITH PASSWORD 'Dev@Project123';

Create & Populate Databases

Create databases:

CREATE DATABASE project_dev;
CREATE DATABASE project_test;
CREATE DATABASE project_prod;

Grant permissions:

GRANT CONNECT ON DATABASE project_dev TO project_udev;
GRANT ALL PRIVILEGES ON DATABASE project_dev to project_udev;

Connect to PostgreSQL Database Remotely

Allow remote access in the PostgreSQL configuration files:

Edit postgresql.conf:

vim /etc/postgresql/14/main/postgresql.conf

Uncomment and edit the listen_addresses attribute to allow all IP addresses.

Edit pg_hba.conf:

vim /etc/postgresql/14/main/pg_hba.conf

Append a new connection policy.

Restart PostgreSQL:

systemctl restart postgresql

Confirm listening on port 5432:

ss -nlt | grep 5432

Connect remotely using psql:

psql -U ghealth_udev -p 5432 -h 10.10.18.231

This refined version maintains the structure of your guide while making it more readable and reducing the likelihood of errors.