Setting up a Simple PostgreSQL Database

PostgreSQL is a robust open-source relational database management system. Setting up a simple PostgreSQL database on your system allows you to interact with the system before before integrating extensions. We will cover some steps, starting with the download and installation of PostgreSQL version 12.15. We are going to do the installation from the source.
Step 1: Download PostgreSQL

1. Open a terminal in your system.
2. Clone the repository to your system: We are going to use the unofficial github repository of Postgres from In your terminal run git clone -b REL_12_STABLE https://github.com/postgres/postgres.git This will clone the specified version of PostgreSQL, version 12 from the repo.
3. Update your package list: sudo apt-get update Step 2: Install PostgreSQL Navigate to the the postgres installation folder Then run the following command to configure your files: ./configure Run this command to install PostgreSQL sudo make install Run the following command to make sure that PostgreSQL will work as expected by developers. make check Step 3: Start the PostgreSQL Service After the installation, PostgreSQL should automatically start. However, you can ensure it's running with this command: sudo systemctl start postgresql This command starts the PostgreSQL service.

Step 4: Create a Database User
By default, PostgreSQL uses the system username to authenticate. To create a PostgreSQL user with your system username and grant necessary privileges, follow these steps:

1. Access the PostgreSQL interactive terminal: sudo -u postgres psql
2. In the PostgreSQL terminal, create a user (replace your_username with your actual system username): CREATE USER your_username;
3. Set a password for the user (replace your_password with your desired password): ALTER USER your_username PASSWORD 'your_password';
4. Grant superuser privileges to the user: ALTER USER your_username WITH SUPERUSER;
5. Exit the PostgreSQL terminal by \q Step 5: Create a Database Now, create a database with your user's privileges. In the terminal, run: createdb testdb This command creates a database named "testdb." Step 6: Connect to the Database

Connect to your newly created database using the psql command. Replace your_username with your system username and testdb with the database name if necessary:
psql -U your_username -d testdb
Step 7: Create a Table and Insert Data
Once connected to the database, let’s create a table and insert data

CREATE TABLE employees (
    id serial PRIMARY KEY,
    first_name varchar(50),
    last_name varchar(50),
    salary numeric
);

INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 50000),
       ('Jane', 'Smith', 60000),
       ('Alice', 'Johnson', 55000);

Step 8: Query the Database

Let’s query the database to retrieve information from the "employees" table
SELECT * FROM employees;
The results of the above query is:

 id | first_name | last_name | salary 
----+------------+-----------+--------
  1 | John       | Doe       |  50000
  2 | Jane       | Smith     |  60000
  3 | Alice      | Johnson   |  55000
(3 rows)

That's it! You've successfully set up a PostgreSQL database, starting from downloading and installing PostgreSQL.