Setting up a Simple PostgreSQL Database
Tags: postgres
Published: 2023-09-03 07:52:29


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 [](https://github.com/postgres/postgres)
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.