Installing and setting up PostgreSQL on a Linux server

Step 1: Updating the system

You should update your server packages to ensure all existing packages are up to date. This can be done by running the commands:
Sudo apt update
Sudo apt upgrade -y

Step 2: Installing PostgreSQL

The following commands are run when installing PostgreSQL and its necessary packages:
Sudo apt install PostgreSQL PostgreSQL-contrib

Step 3: Verifying the installation.

The PostgreSQL service should start automatically after complete installation. The following commands are used to verify the installation.

Sudo systemctl status PostgreSQL

Step 4: Accessing the PostgreSQL shell
PostgreSQL creates a Linux user ‘postgres’ by default. To interact with this:

Sudo -I -u postgres

Step 5: Creating a new user and Database

To create a new user, the following command is used:
CREATE USER ‘user1’ WITH PASSWORD ‘password’;
Then create a database:
CREATE DATABASE ‘mydatabase’ WITH OWNER ‘user1’;

Step 6: Secure your installation with a firewall

Enable a firewall like ufw on ubuntu to restrict external access. You must first open the default PostgreSQL port, 5432 to allow remote connections.

Sudo ufw allow 5432/tcp
Sudo ufw reload

End.

You have now successfully installed and setup PostgreSQL on your Linux server. You have created a user and a database, and secured the connection.