🐘 Azure PostgreSQL Flexible Server

CLI + psql Full Command Guide

---

🔹 Service

Azure Database for PostgreSQL

---

1️⃣ CREATE

1.1 Create Resource Group

az group create \
  --name azure-solution-architect-labs \
  --location centralindia

---

1.2 Create PostgreSQL Flexible Server

az postgres flexible-server create \
  --resource-group azure-solution-architect-labs \
  --name postgresql-testing \
  --location centralindia \
  --version 18 \
  --tier Burstable \
  --sku-name Standard_B2s \
  --storage-size 32 \
  --admin-user admin123 \
  --admin-password 'Password@123' \
  --backup-retention 7 \
  --high-availability Disabled \
  --geo-redundant-backup Disabled \
  --yes

✅ Creates PostgreSQL ✅ Enables backups ✅ SSL enforced by default

---

2️⃣ SHOW / VIEW (Azure CLI)

2.1 Show Server Details

az postgres flexible-server show \
  --resource-group azure-solution-architect-labs \
  --name postgresql-testing

---

2.2 List All PostgreSQL Servers

az postgres flexible-server list \
  --resource-group azure-solution-architect-labs \
  --output table

---

2.4 View Backup Settings

az postgres flexible-server show \
  --resource-group azure-solution-architect-labs \
  --name postgresql-testing \
  --query backup

---

3️⃣ PSQL (Database Operations)

3.1 Connect to PostgreSQL

psql "host=postgresql-testing.postgres.database.azure.com \
port=5432 \
dbname=postgres \
user=admin123 \
sslmode=require"

---

🔹 DATABASE (View / Create / Delete)

View Databases

\l

Create Database

CREATE DATABASE appdb;

Switch Database

\c appdb

Delete Database

DROP DATABASE appdb;

---

🔹 USERS (View / Create / Delete)

View Users

\du

Create User

CREATE USER appuser WITH PASSWORD 'AppUser@123';

Grant Permissions

GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

Delete User

DROP USER appuser;

---

🔹 TABLES (View / Create / Edit / Delete)

View Tables

\dt

Create Table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Describe Table

\d users

Alter Table (Edit)

ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

Delete Table

DROP TABLE users;

---

🔹 DATA (View / Insert / Update / Delete)

Insert Data

INSERT INTO users (name, email)
VALUES ('Yuva', 'yuva@example.com');

View Data

SELECT * FROM users;

Update Data

UPDATE users
SET name = 'Yuva Raj'
WHERE id = 1;

Delete Data

DELETE FROM users WHERE id = 1;

---

🔹 INDEXES

View Indexes

\di

Create Index

CREATE INDEX idx_users_email ON users(email);

Delete Index

DROP INDEX idx_users_email;

---

🔹 TRANSACTIONS

BEGIN;
UPDATE users SET name='Test';
ROLLBACK;

or

COMMIT;

---

4️⃣ DELETE (Azure Resources)

4.1 Delete PostgreSQL Server

az postgres flexible-server delete \
  --resource-group azure-solution-architect-labs \
  --name postgresql-testing \
  --yes

---

4.2 Delete Resource Group (Deletes EVERYTHING)

az group delete \
  --name azure-solution-architect-labs \
  --yes --no-wait

---

🧠 FINAL QUICK FLOW (MEMORIZE THIS)

CREATE
 → SHOW (Azure CLI)
 → CONNECT (psql)
 → DB / TABLE / DATA OPS
 → DELETE

---

✅ You now know:

✔ Azure PostgreSQL lifecycle ✔ CLI management ✔ Full psql CRUD ✔ Production-ready workflow

---