Postgresql (pgadmin)

In this section, we will guide you through the installation and setup of PostgreSQL, a crucial component of our backend environment. PostgreSQL is a powerful open-source relational database management system (RDBMS) that plays a key role in our system’s data storage and management. It supports advanced data types, indexing, and complex queries, making it an excellent choice for data-intensive applications like ours. It also works well with Dbeaver, a database management tool we use that supports it.

Installation

To get started, you’ll need to install PostgreSQL. Follow these steps:

Installation Link: Visit the PostgreSQL download page and select the appropriate version for your operating system. Follow the installation instructions provided for your platform.

Setup and Configuration

Creating a PostgreSQL User

  1. Password Creation: During installation, PostgreSQL will prompt you to create a password for the default PostgreSQL user (postgres). This password is essential for administrative tasks.

  2. PostgreSQL User: To manage PostgreSQL, you’ll use the postgres user account. It is the default superuser for the PostgreSQL database.

pgAdmin

pgAdmin is a user-friendly tool that complements PostgreSQL by providing a graphical interface for database management. It comes along with the PostgreSQL installation. The lateset version of it is pgadmin 4. You can use pgAdmin to:

  • View and Edit Database Objects: Easily browse and modify database objects such as tables, views, and functions.

  • Run Queries: Write and execute SQL queries using a user-friendly interface.

  • Manage User Roles: Create, edit, and delete user roles and permissions.

  • Backup and Restore: Perform database backups and restoration tasks.

  • Monitor and Analyze: Access real-time performance monitoring and analysis tools.

Akaun Super User Role

To interact with our backend database effectively, we will create an "akaun" super user role. This user role can be used for all database connections and management. Super users have elevated privileges, allowing them to perform administrative tasks and manage their databases efficiently.

Connect to PostgreSQL: Connect to your PostgreSQL server using the credentials you set during installation.

Creating the Akaun Super User Role through pgAdmin (EASY)

  • Open pgAdmin, the graphical user interface for PostgreSQ (Currently the application is pgAdmin 4).

  • In the Object Explorer section on the right there is a Servers drop down. Click on it. You will be prompted for a password that you setup earlier.

Pgadmin password prompt
Figure 1. PgAdmin 4 password prompt

  • After keying the password, you will see everything under the servers dropdown.

  • Right click the Login/Group Roles. Select "Create" and then "Login/Group Role…​".

Pgadmin create role
Figure 2. Role creation

  • In the "Name" field, enter "akaun" (without quotes).

Pgadmin name
Figure 3. Role name fill up

  • In the "Definition" tab, set the password field for the akaun user as "akaun" (without quotes).

Pgadmin role password
Figure 4. Role password fill up

  • In the "Privileges" tab, grant the "Can login?", "Superuser?", "Create roles?", "Create databases?", "Inherit rights from the parent roles?" privileges to the akaun user by selecting them.

  • Click "Save" to create the akaun super user role.

Pgadmin roles and save
Figure 5. Role privileges fill up and Save

Creating the Akaun Super User Role through CLI (HARD alternative)

Creating the akaun super user role through the command-line interface (CLI) is a more advanced method. You can use the createuser and psql commands to achieve this. If commands are not recognized on your windows terminal you will need to add PostgreSQL as environment variable. Please follow this link for that. Some commands might be different for windows so do try to look up the equivalent commands. It is recommended to use git bash.

  1. First Go to the Terminal and write the following command:

    • sudo su - postgres

  2. Then You can now run commands as the PostgreSQL superuser. To create a user, type the following command:

    • createuser --interactive --pwprompt

  3. Give the following credentials like below:

    • Enter name of role to add: akaun

    • Enter password for new role: akaun

    • Enter it again: akaun

    • Shall the new role be a superuser?: y

    • Add User - PostgreSQL

  4. Then check if the user has been added or not by following the steps below:

    • write psql

    • Then, write \du;

    • Check if “akaun” is present there.

    • Check User - PostgreSQL

Now with PostgreSQL and pgAdmin set up, you’re now equipped to interact with our backend database effectively.