PostgreSQL: From Installation to Database Management

This guide covers essential PostgreSQL commands and operations for beginners to get started with PostgreSQL on a Unix-based system.

---

📦 Installation

Ubuntu/Debian

sudo apt update
sudo apt install postgresql postgresql-contrib

Fedora

sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl enable postgresql
sudo systemctl start postgresql

🧑‍🔧 Managing PostgreSQL Service

Start PostgreSQL

sudo systemctl start postgresql

Stop PostgreSQL

sudo systemctl stop postgresql

Restart PostgreSQL

sudo systemctl restart postgresql

Check status

sudo systemctl status postgresql

👤 PostgreSQL User and Role Management

Switch to the postgres user

sudo -i -u postgres

Access PostgreSQL shell

psql

or

Direct Access PostgreSQL shell

sudo -u postgres psql

Create a new role/user

CREATE ROLE your_username WITH LOGIN PASSWORD 'your_password';

Grant privileges to the user

ALTER ROLE your_username CREATEDB;

🏗️ Database Operations

Create a new database

CREATE DATABASE your_database_name;

Connect to a database

psql -d your_database_name

Or within psql shell:

\c your_database_name

List databases

\l

Drop a database

DROP DATABASE your_database_name;

📄 Table Management

Create a table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert data

INSERT INTO users (username, email) VALUES ('santosh', 'santosh@example.com');

View data

SELECT * FROM users;

Update data

UPDATE users SET email = 'new@example.com' WHERE username = 'santosh';

Delete data

DELETE FROM users WHERE username = 'santosh';

🔐 Role and Permission Management

List roles

\du

Grant permissions on a database

GRANT ALL PRIVILEGES ON DATABASE your_database TO your_username;

Revoke permissions

REVOKE ALL PRIVILEGES ON DATABASE your_database FROM your_username;

🛠️ Useful psql Commands

\q                      -- Quit psql
\dt                     -- List tables
\d table_name           -- Describe table structure
\du                     -- List roles
\l                      -- List databases
\c db_name              -- Connect to database

📤 Backup and Restore

Backup a database

pg_dump your_database_name > backup.sql

Restore from a backup

psql your_database_name < backup.sql

Export to CSV

COPY users TO '/path/to/file.csv' DELIMITER ',' CSV HEADER;

Import from CSV

COPY users FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;

🔄 Reset PostgreSQL Password

sudo -u postgres psql

ALTER USER postgres WITH PASSWORD 'new_password';

🔁 Stored Procedure

Create Stored Procedure

CREATE OR REPLACE PROCEDURE increase_salary(emp_id INT, increment NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary + increment
    WHERE id = emp_id;
END;
$$;

Call Stored Procedure

CALL increase_salary(1, 1000.00);

🔔 Trigger

Step 1: Create Audit Table

CREATE TABLE employee_audit (
    id SERIAL PRIMARY KEY,
    employee_id INT,
    old_salary NUMERIC,
    new_salary NUMERIC,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Trigger Function

CREATE OR REPLACE FUNCTION log_salary_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO employee_audit (employee_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 3: Create Trigger

CREATE TRIGGER trigger_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_changes();

🧪 Verify Trigger

UPDATE employees
SET salary = salary + 2000
WHERE id = 1;

SELECT * FROM employee_audit;

📚 References

PostgreSQL Documentation