# PostgreSQL Learning

# Beginner Guide

**Target System:** Dedicated Linux VM on Proxmox VE  
**Skill Level:** Complete Beginner  
**Goal:** Install PostgreSQL, configure basic security, connect via local client & GUI, and build your very first database project step-by-step.

## 1\. Overview & Prerequisites

PostgreSQL (often called "Postgres") is an enterprise-grade, open-source object-relational database management system.

### What You Need:

*   A freshly installed **Debian** or **Ubuntu Server** VM.
    
*   SSH access or access via the Proxmox Console.
    
*   Basic knowledge of terminal commands (`sudo`, `nano`, `systemctl`).
    

* * *

## Step 1: System Preparation & PostgreSQL Installation

In this step, we will add the official PostgreSQL Apt Repository to ensure you install the latest stable build rather than outdated distribution defaults.

### 1.1 Update Your OS Packages

Log in to your PostgreSQL VM as your admin user and run:

```bash
sudo apt update && sudo apt upgrade -y
```

![Postgre SQL-1787976123371](https://cdn.hashnode.com/uploads/covers/6a2d385da5fd3fa75afcb460/89ecec99-725c-419d-b481-93ee9bc6ffd3.webp align="center")

### 1.2 Add the Official PostgreSQL Repository

Install required helper packages:

```bash
sudo apt install -y curl ca-certificates gnupg lsb-release
```

Import the official PostgreSQL signing key:

```bash
sudo install -d /etc/apt/keyrings
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/keyrings/postgresql.gpg
```

Add the repository entry:

```bash
echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
```

### 1.3 Install PostgreSQL

Update apt repositories and install PostgreSQL along with its contrib package (extra features):

```bash
sudo apt update
sudo apt install -y postgresql postgresql-contrib
```

* * *

## Step 2: Service Verification & Core Architecture

When PostgreSQL installs, it automatically creates:

1.  A Linux system user named `postgres`.
    
2.  A default database cluster managed by the systemd service `postgresql`.
    
3.  A default database superuser named `postgres`.
    

### 2.1 Check Service Status

Verify PostgreSQL is active and running:

```bash
sudo systemctl status postgresql
```

*Expected output should show* `active (running)`*.*

![Postgre SQL-1787981389212](https://cdn.hashnode.com/uploads/covers/6a2d385da5fd3fa75afcb460/29485ccb-b883-47b2-baf2-8ff2e002654f.webp align="center")

Ensure it starts automatically on VM boot:

```bash
sudo systemctl enable postgresql
```

* * *

## Step 3: Initial Database Configuration & Security

By default, PostgreSQL only accepts local connections from the VM itself and requires administrative privilege escalation.

### 3.1 Access the `psql` Interactive Terminal

Switch to the `postgres` system user and open the Postgres shell (`psql`):

```bash
sudo -i -u postgres psql
```

Your prompt will change to:

```text
postgres=#
```

### 3.2 Set a Secure Password for the `postgres` Superuser

Inside the `psql` shell, execute:

```sql
ALTER USER postgres WITH PASSWORD 'YourStrongPassword123!';
```

*(Replace* `YourStrongPassword123!` *with your preferred password).*

### 3.3 Useful `psql` Meta-Commands to Know

Try running these inside `psql`:

| Command | Description |
| --- | --- |
| `\l` | List all databases |
| `\du` | List all database users/roles |
| `\c database_name` | Connect to a specific database |
| `\dt` | List all tables in current database |
| `\q` | Quit/exit `psql` prompt |

To exit `psql` for now, type:

```sql
\q
```

* * *

## Step 4: Network Access & GUI Setup (pgAdmin / DBeaver)

To connect from your workstation or another VM (like your CasaOS VM or management PC), PostgreSQL must be configured to listen on network interfaces.

### 4.1 Update `postgresql.conf` (Listen Address)

Locate and edit the main configuration file. *(Note: Replace* `16` *in the path with your installed Postgres version if different, e.g.,* `17`*)*:

```bash
sudo nano /etc/postgresql/16/main/postgresql.conf
```

Find the line:

```text
#listen_addresses = 'localhost'
```

Uncomment it (remove `#`) and change it to:

```text
listen_addresses = '*'
```

*Save and exit (*`Ctrl+O`*,* `Enter`*,* `Ctrl+X`*).*

### 4.2 Update `pg_hba.conf` (Authentication Rules)

`pg_hba.conf` manages Client Authentication. Edit the file:

```bash
sudo nano /etc/postgresql/16/main/pg_hba.conf
```

Scroll to the bottom under `IPv4 local connections` and add an entry for your local subnet:

```text
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.0/24          scram-sha-256
```

*(Replace* `192.168.1.0/24` *with your network's IP subnet).*

### 4.3 Configure UFW Firewall (if enabled)

If Ubuntu/Debian Firewall is enabled, allow TCP port 5432:

```bash
sudo ufw allow 5432/tcp
```

### 4.4 Restart PostgreSQL Service

Apply all configuration changes:

```bash
sudo systemctl restart postgresql
```

* * *

## Step 5: Your First Hands-On Lab — Simple Asset Inventory

Now let's build a clean, simple project to learn basic SQL concepts: **Database Creation**, **Tables**, **Data Types**, **CRUD Operations (Create, Read, Update, Delete)**, and **Simple Queries**.

### Lab Scenario: Network Device Inventory

You are building a database to track IT hardware on your network.

* * *

### 5.1 Step-by-Step SQL Guide

#### A. Connect to Postgres & Create a New Database

Log back into `psql`:

```bash
sudo -i -u postgres psql
```

Create a new database named `lab_inventory`:

```sql
CREATE DATABASE lab_inventory;
```

Connect to your new database:

```sql
\c lab_inventory
```

*Prompt changes to* `lab_inventory=#`*.*

* * *

#### B. Create Your First Table

We will create a table named `devices` with appropriate Postgres data types:

```sql
CREATE TABLE devices (
    device_id SERIAL PRIMARY KEY,
    hostname VARCHAR(50) UNIQUE NOT NULL,
    ip_address INET NOT NULL,
    device_type VARCHAR(30) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

**Concept Breakdown:**

*   `SERIAL PRIMARY KEY`: Auto-incrementing unique identifier.
    
*   `VARCHAR(50)`: Text string up to 50 characters.
    
*   `INET`: Native PostgreSQL type specially built for IPv4/IPv6 addresses.
    
*   `BOOLEAN`: Stores `true` or `false`.
    
*   `TIMESTAMP`: Automatically records the current system date and time.
    

Verify table creation:

```sql
\dt
```

* * *

#### C. Insert Data (CREATE)

Insert 4 initial records into the `devices` table:

```sql
INSERT INTO devices (hostname, ip_address, device_type, is_active) VALUES
('pfsense-gateway', '192.168.1.1', 'Firewall', true),
('proxmox-host', '192.168.1.10', 'Hypervisor', true),
('casaos-vm', '192.168.1.105', 'Server', true),
('legacy-switch', '192.168.1.250', 'Switch', false);
```

* * *

#### D. Query Data (READ)

Select all rows from the table:

```sql
SELECT * FROM devices;
```

Filter rows using a `WHERE` clause:

```sql
SELECT hostname, ip_address FROM devices WHERE device_type = 'Server';
```

Filter active devices using Boolean logic:

```sql
SELECT hostname, ip_address FROM devices WHERE is_active = true;
```

* * *

#### E. Update Data (UPDATE)

Suppose `legacy-switch` was upgraded and brought back online with a new IP:

```sql
UPDATE devices 
SET ip_address = '192.168.1.20', is_active = true 
WHERE hostname = 'legacy-switch';
```

Verify the change:

```sql
SELECT * FROM devices WHERE hostname = 'legacy-switch';
```

![Postgre SQL-1787982172111](https://cdn.hashnode.com/uploads/covers/6a2d385da5fd3fa75afcb460/02b0e308-57db-4f61-b2c2-68e444ad4079.webp align="center")

#### F. Delete Data (DELETE)

Delete a record from the database:

```sql
DELETE FROM devices WHERE hostname = 'legacy-switch';
```

![Postgre SQL-1787982245505](https://cdn.hashnode.com/uploads/covers/6a2d385da5fd3fa75afcb460/a36a8da5-d69c-4942-86c6-28e90694544f.webp align="center")

#### G. Basic Aggregation & Sorting

Count how many active devices exist per device type:

```sql
SELECT device_type, COUNT(*) AS total_devices
FROM devices
WHERE is_active = true
GROUP BY device_type
ORDER BY total_devices DESC;
```

* * *

## Step 6: Basic Administration & Backup Commands

Every database administrator needs to know how to back up and restore data.

### 6.1 Creating a Database Backup (`pg_dump`)

Exit `psql` back to your normal Linux terminal:

```sql
\q
```

Run `pg_dump` to export your database to a SQL file:

```bash
sudo -u postgres pg_dump lab_inventory > ~/lab_inventory_backup.sql
```

Inspect the backup file:

```bash
head -n 20 ~/lab_inventory_backup.sql
```

### 6.2 Restoring a Database Backup

To restore the backup into a new database:

```bash
# 1. Create target database
sudo -u postgres createdb lab_inventory_restored

# 2. Restore SQL dump
sudo -u postgres psql lab_inventory_restored < ~/lab_inventory_backup.sql
```