Beginners To Experts

The site is under development.

PostgreSQL Tutorial

1.1 What is PostgreSQL?
PostgreSQL is an advanced open-source relational database management system.
SELECT version(); -- Check PostgreSQL version

1.2 Features of PostgreSQL
Includes ACID compliance, extensibility, and support for advanced data types.
SELECT current_date, current_time; -- Display current date and time

1.3 Installing PostgreSQL
Use installers or package managers based on your OS.
sudo apt update && sudo apt install postgresql postgresql-contrib

1.4 Connecting to PostgreSQL
Use `psql` command-line tool or GUI tools like pgAdmin.
psql -U postgres

1.5 Creating a Database
Basic command to create a new database.
CREATE DATABASE testdb;

1.6 Creating a User
PostgreSQL allows user creation with specific privileges.
CREATE USER testuser WITH PASSWORD 'password';

1.7 Granting Privileges
Assign permissions to users.
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;

1.8 Logging into a Database
Log into a specific database.
psql -d testdb -U testuser

1.9 Basic SQL Syntax
PostgreSQL follows standard SQL syntax.
SELECT 'Hello, PostgreSQL!';

1.10 Exiting PostgreSQL
Exit the `psql` interface.
\q

2.1 Numeric Types
PostgreSQL supports integer, bigint, real, and numeric.
CREATE TABLE test (id SERIAL, amount NUMERIC);

2.2 Character Types
Types include `char`, `varchar`, and `text`.
CREATE TABLE names (first_name VARCHAR(50));

2.3 Date and Time Types
Store timestamps, intervals, and dates.
CREATE TABLE events (event_date DATE, event_time TIME);

2.4 Boolean Type
For true/false values.
CREATE TABLE flags (is_active BOOLEAN);

2.5 Array Types
PostgreSQL allows columns to store arrays.
CREATE TABLE sensors (readings INTEGER[]);

2.6 JSON Types
Store and query JSON data.
CREATE TABLE documents (data JSON);

2.7 UUID Type
Store universally unique identifiers.
CREATE TABLE items (id UUID DEFAULT gen_random_uuid());

2.8 Operators Overview
Arithmetic, comparison, logical operators.
SELECT 10 + 5, 20 > 10, true AND false;

2.9 String Operators
Concatenation and pattern matching.
SELECT 'Hello' || ' World', 'Postgres' LIKE 'Post%';

2.10 Type Casting
Convert data types.
SELECT '123'::INTEGER;

3.1 Creating a New Database
CREATE DATABASE mydb;

3.2 Renaming a Database
ALTER DATABASE mydb RENAME TO newdb;

3.3 Dropping a Database
DROP DATABASE mydb;

3.4 Creating Tables
CREATE TABLE employees (id SERIAL, name VARCHAR, age INT);

3.5 Dropping Tables
DROP TABLE employees;

3.6 Altering Tables
ALTER TABLE employees ADD email VARCHAR(100);

3.7 Viewing Tables
\dt

3.8 Viewing Table Schema
\d employees

3.9 Copying Tables
CREATE TABLE new_employees AS SELECT * FROM employees;

3.10 Truncating Tables
TRUNCATE TABLE employees;

4.1 Insert Data
INSERT INTO employees (name, age) VALUES ('Alice', 30);

4.2 Insert Multiple Rows
INSERT INTO employees (name, age) VALUES ('Bob', 25), ('Carol', 28);

4.3 Read Data
SELECT * FROM employees;

4.4 Filtering Data
SELECT * FROM employees WHERE age > 25;

4.5 Updating Data
UPDATE employees SET age = 35 WHERE name = 'Alice';

4.6 Deleting Data
DELETE FROM employees WHERE name = 'Bob';

4.7 Returning Data from INSERT
INSERT INTO employees (name, age) VALUES ('Eve', 22) RETURNING id;

4.8 Using NULL
SELECT * FROM employees WHERE email IS NULL;

4.9 ORDER BY Clause
SELECT * FROM employees ORDER BY age DESC;

4.10 LIMIT Clause
SELECT * FROM employees LIMIT 5;

5.1 NOT NULL Constraint
CREATE TABLE users (id SERIAL, name VARCHAR NOT NULL);

5.2 UNIQUE Constraint
CREATE TABLE users (email VARCHAR UNIQUE);

5.3 PRIMARY KEY
CREATE TABLE users (id SERIAL PRIMARY KEY);

5.4 FOREIGN KEY
CREATE TABLE orders (id SERIAL, user_id INT REFERENCES users(id));

5.5 CHECK Constraint
CREATE TABLE accounts (balance NUMERIC CHECK (balance >= 0));

5.6 DEFAULT Value
CREATE TABLE settings (theme VARCHAR DEFAULT 'light');

5.7 Composite Keys
CREATE TABLE enrollment (student_id INT, course_id INT, PRIMARY KEY (student_id, course_id));

5.8 Creating Indexes
CREATE INDEX idx_name ON users(name);

5.9 Unique Index
CREATE UNIQUE INDEX idx_email ON users(email);

5.10 Dropping Index
DROP INDEX idx_name;

6.1 INNER JOIN
SELECT * FROM employees INNER JOIN departments ON employees.dept_id = departments.id;

6.2 LEFT JOIN
SELECT * FROM employees LEFT JOIN departments ON employees.dept_id = departments.id;

6.3 RIGHT JOIN
SELECT * FROM employees RIGHT JOIN departments ON employees.dept_id = departments.id;

6.4 FULL OUTER JOIN
SELECT * FROM employees FULL OUTER JOIN departments ON employees.dept_id = departments.id;

6.5 CROSS JOIN
SELECT * FROM products CROSS JOIN categories;

6.6 Self Join
SELECT a.name, b.name FROM employees a JOIN employees b ON a.manager_id = b.id;

6.7 USING Clause
SELECT * FROM orders JOIN customers USING(customer_id);

6.8 NATURAL JOIN
SELECT * FROM orders NATURAL JOIN customers;

6.9 Joining Multiple Tables
SELECT * FROM a JOIN b ON a.id = b.a_id JOIN c ON b.id = c.b_id;

6.10 Aggregate with JOIN
SELECT d.name, COUNT(e.id) FROM departments d JOIN employees e ON d.id = e.dept_id GROUP BY d.name;

7.1 Creating a View
CREATE VIEW emp_view AS SELECT name, age FROM employees;

7.2 Querying a View
SELECT * FROM emp_view;

7.3 Updating a View
CREATE OR REPLACE VIEW emp_view AS SELECT name FROM employees;

7.4 Dropping a View
DROP VIEW emp_view;

7.5 Creating Materialized View
CREATE MATERIALIZED VIEW emp_mview AS SELECT * FROM employees;

7.6 Refreshing Materialized View
REFRESH MATERIALIZED VIEW emp_mview;

7.7 Dropping Materialized View
DROP MATERIALIZED VIEW emp_mview;

7.8 Using Views in Joins
SELECT * FROM emp_view JOIN departments ON emp_view.dept_id = departments.id;

7.9 Indexed Materialized View
CREATE INDEX ON emp_mview (name);

7.10 Advantages of Views
Simplifies complex queries and improves security.

8.1 Creating a Function
CREATE FUNCTION add(a INT, b INT) RETURNS INT AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE plpgsql;

8.2 Calling a Function
SELECT add(2, 3);

8.3 Dropping a Function
DROP FUNCTION add(INT, INT);

8.4 Function with Logic
CREATE FUNCTION is_adult(age INT) RETURNS BOOLEAN AS $$ BEGIN RETURN age >= 18; END; $$ LANGUAGE plpgsql;

8.5 Creating Procedure
CREATE PROCEDURE log_activity(msg TEXT) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO logs(message) VALUES (msg); END; $$;

8.6 Calling Procedure
CALL log_activity('User login');

8.7 Dropping Procedure
DROP PROCEDURE log_activity(TEXT);

8.8 Function with RETURNS TABLE
CREATE FUNCTION get_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, name FROM users; END; $$ LANGUAGE plpgsql;

8.9 Recursive Function
CREATE FUNCTION factorial(n INT) RETURNS INT AS $$ BEGIN IF n = 0 THEN RETURN 1; ELSE RETURN n * factorial(n - 1); END IF; END; $$ LANGUAGE plpgsql;

8.10 Set Returning Function
CREATE FUNCTION range_series(n INT) RETURNS SETOF INT AS $$ BEGIN FOR i IN 1..n LOOP RETURN NEXT i; END LOOP; RETURN; END; $$ LANGUAGE plpgsql;

9.1 Creating a Trigger Function
CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$ BEGIN INSERT INTO logs(action) VALUES ('Data changed'); RETURN NEW; END; $$ LANGUAGE plpgsql;

9.2 BEFORE INSERT Trigger
CREATE TRIGGER before_insert_trigger BEFORE INSERT ON employees FOR EACH ROW EXECUTE FUNCTION log_changes();

9.3 AFTER INSERT Trigger
CREATE TRIGGER after_insert_trigger AFTER INSERT ON employees FOR EACH ROW EXECUTE FUNCTION log_changes();

9.4 BEFORE UPDATE Trigger
CREATE TRIGGER before_update_trigger BEFORE UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_changes();

9.5 AFTER UPDATE Trigger
CREATE TRIGGER after_update_trigger AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_changes();

9.6 BEFORE DELETE Trigger
CREATE TRIGGER before_delete_trigger BEFORE DELETE ON employees FOR EACH ROW EXECUTE FUNCTION log_changes();

9.7 AFTER DELETE Trigger
CREATE TRIGGER after_delete_trigger AFTER DELETE ON employees FOR EACH ROW EXECUTE FUNCTION log_changes();

9.8 Conditional Trigger
CREATE TRIGGER update_salary BEFORE UPDATE ON employees FOR EACH ROW WHEN (OLD.salary <> NEW.salary) EXECUTE FUNCTION log_changes();

9.9 DISABLE Trigger
ALTER TABLE employees DISABLE TRIGGER after_insert_trigger;

9.10 DROP Trigger
DROP TRIGGER after_insert_trigger ON employees;

10.1 BEGIN Transaction
BEGIN;

10.2 COMMIT Transaction
COMMIT;

10.3 ROLLBACK Transaction
ROLLBACK;

10.4 SAVEPOINT
SAVEPOINT sp1;

10.5 ROLLBACK TO SAVEPOINT
ROLLBACK TO SAVEPOINT sp1;

10.6 RELEASE SAVEPOINT
RELEASE SAVEPOINT sp1;

10.7 SERIALIZABLE Isolation
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

10.8 READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

10.9 READ ONLY Transaction
SET TRANSACTION READ ONLY;

10.10 Concurrent Updates Example
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- In another session, try updating same row

11.1 Backup using pg_dump
pg_dump testdb > testdb.sql

11.2 Backup with Compression
pg_dump -Fc testdb > testdb.dump

11.3 Restoring from SQL
psql testdb < testdb.sql

11.4 Restoring from Custom Format
pg_restore -d testdb testdb.dump

11.5 Full Cluster Backup
pg_basebackup -D /backup -Fp -Xs -P -U postgres

11.6 Create Backup Role
CREATE ROLE backup WITH LOGIN PASSWORD 'backup123';

11.7 Grant Backup Permissions
GRANT CONNECT ON DATABASE testdb TO backup;

11.8 Logical Backup of Table
pg_dump -t employees testdb > employees.sql

11.9 Restore Specific Table
psql testdb < employees.sql

11.10 Schedule Backup
Use cron to automate: 
0 2 * * * /usr/bin/pg_dump testdb > /backups/testdb.sql

12.1 EXPLAIN Query
EXPLAIN SELECT * FROM employees WHERE age > 30;

12.2 EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM employees;

12.3 VACUUM
VACUUM employees;

12.4 VACUUM FULL
VACUUM FULL employees;

12.5 ANALYZE
ANALYZE employees;

12.6 REINDEX
REINDEX TABLE employees;

12.7 CLUSTER Table
CLUSTER employees USING idx_name;

12.8 pg_stat_statements
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

12.9 Work Mem Tuning
SET work_mem = '64MB';

12.10 Parallel Queries
SET max_parallel_workers_per_gather = 4;

13.1 Creating Roles
CREATE ROLE readonly;

13.2 Assigning Roles
GRANT readonly TO testuser;

13.3 Grant SELECT Permission
GRANT SELECT ON employees TO readonly;

13.4 Revoke Permission
REVOKE SELECT ON employees FROM readonly;

13.5 Password Authentication
ALTER ROLE testuser WITH PASSWORD 'securepass';

13.6 Connection Limit
ALTER ROLE testuser CONNECTION LIMIT 5;

13.7 Role Inheritance
CREATE ROLE dev INHERIT;

13.8 Disable Role
ALTER ROLE testuser NOLOGIN;

13.9 Role Attributes
ALTER ROLE testuser CREATEROLE CREATEDB;

13.10 pg_hba.conf Example
Host-based authentication config sample:
host all all 192.168.1.0/24 md5

14.1 List Installed Extensions
SELECT * FROM pg_extension;

14.2 Install Extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

14.3 Drop Extension
DROP EXTENSION "uuid-ossp";

14.4 PostGIS Extension
CREATE EXTENSION postgis;

14.5 citext Extension
CREATE EXTENSION citext;

14.6 hstore Extension
CREATE EXTENSION hstore;

14.7 pgcrypto Extension
CREATE EXTENSION pgcrypto;

14.8 Using gen_random_uuid
SELECT gen_random_uuid();

14.9 Add Comment to Extension
COMMENT ON EXTENSION "uuid-ossp" IS 'UUID functions';

14.10 Dependent Objects
SELECT objid::regprocedure FROM pg_depend WHERE refobjid = 'uuid-ossp'::regnamespace;

15.1 Create JSON Column
CREATE TABLE products (data JSON);

15.2 Create JSONB Column
CREATE TABLE products_b (data JSONB);

15.3 Insert JSON
INSERT INTO products VALUES ('{"name": "Pen", "price": 1.2}');

15.4 Query JSON Field
SELECT data->'name' FROM products;

15.5 Filter JSON Value
SELECT * FROM products WHERE data->>'name' = 'Pen';

15.6 JSONB Operators
SELECT data @> '{"price":1.2}' FROM products_b;

15.7 Update JSONB
UPDATE products_b SET data = jsonb_set(data, '{price}', '2.5');

15.8 Delete Key from JSONB
UPDATE products_b SET data = data - 'price';

15.9 JSON Functions
SELECT jsonb_array_elements('[1,2,3]'::jsonb);

15.10 Indexing JSONB
CREATE INDEX idx_jsonb_price ON products_b USING gin(data);

16.1 Check Server Status
SELECT now(), pg_postmaster_start_time();

16.2 View Active Sessions
SELECT * FROM pg_stat_activity;

16.3 Check Locks
SELECT * FROM pg_locks;

16.4 Check Disk Usage
SELECT pg_size_pretty(pg_database_size('testdb'));

16.5 Query Performance
SELECT * FROM pg_stat_user_tables ORDER BY seq_scan DESC;

16.6 Enable Logging
postgresql.conf:
logging_collector = on

16.7 Log Settings
log_min_duration_statement = 500

16.8 Log Destination
log_destination = 'stderr'

16.9 Log File Check
tail -f /var/log/postgresql/postgresql.log

16.10 pg_stat_bgwriter
SELECT * FROM pg_stat_bgwriter;

17.1 Install psycopg2
pip install psycopg2

17.2 Connect to Database
import psycopg2
conn = psycopg2.connect("dbname=testdb user=postgres password=secret")

17.3 Create Cursor
cur = conn.cursor()

17.4 Execute SQL
cur.execute("SELECT * FROM employees")

17.5 Fetch Results
rows = cur.fetchall()
for row in rows:
    print(row)

17.6 Insert Record
cur.execute("INSERT INTO employees(name) VALUES (%s)", ("John",))

17.7 Update Record
cur.execute("UPDATE employees SET age = %s WHERE id = %s", (30, 1))

17.8 Delete Record
cur.execute("DELETE FROM employees WHERE id = %s", (1,))

17.9 Commit & Close
conn.commit()
cur.close()
conn.close()

17.10 Handle Exceptions
try:
    # connection logic
except psycopg2.Error as e:
    print("Error:", e)

18.1 Connect to DB
$conn = pg_connect("host=localhost dbname=testdb user=postgres password=secret");

18.2 Check Connection
if(!$conn) {
  echo "Connection failed.";
} else {
  echo "Connected successfully.";
}

18.3 Select Query
$result = pg_query($conn, "SELECT * FROM employees");
while ($row = pg_fetch_assoc($result)) {
  echo $row['name'];
}

18.4 Insert Record
pg_query($conn, "INSERT INTO employees(name) VALUES('Alice')");

18.5 Update Record
pg_query($conn, "UPDATE employees SET age = 28 WHERE name = 'Alice'");

18.6 Delete Record
pg_query($conn, "DELETE FROM employees WHERE name = 'Alice'");

18.7 Prepared Statements
pg_prepare($conn, "my_query", 'SELECT * FROM employees WHERE age > $1');
pg_execute($conn, "my_query", array(25));

18.8 Fetch Row
$row = pg_fetch_row($result);
echo $row[0];

18.9 Close Connection
pg_close($conn);

18.10 Handle Error
echo pg_last_error($conn);

19.1 Install pg module
npm install pg

19.2 Import pg
const { Client } = require('pg');

19.3 Connect to DB
const client = new Client({
  user: 'postgres',
  host: 'localhost',
  database: 'testdb',
  password: 'secret',
  port: 5432,
});
client.connect();

19.4 Select Query
client.query('SELECT * FROM employees', (err, res) => {
  console.log(res.rows);
  client.end();
});

19.5 Insert Query
await client.query("INSERT INTO employees(name) VALUES($1)", ['Eve']);

19.6 Update Query
await client.query("UPDATE employees SET age = $1 WHERE name = $2", [32, 'Eve']);

19.7 Delete Query
await client.query("DELETE FROM employees WHERE name = $1", ['Eve']);

19.8 Error Handling
try {
  await client.query(...);
} catch (err) {
  console.error(err);
}

19.9 Disconnect
client.end();

19.10 Query with Params
await client.query('SELECT * FROM employees WHERE age > $1', [30]);

20.1 Add JDBC Dependency
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.2.5</version>
</dependency>

20.2 Import JDBC Classes
import java.sql.*;

20.3 Connect to DB
Connection conn = DriverManager.getConnection(
  "jdbc:postgresql://localhost:5432/testdb", "postgres", "secret"
);

20.4 Create Statement
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

20.5 Insert Record
stmt.executeUpdate("INSERT INTO employees(name) VALUES('Jane')");

20.6 Update Record
stmt.executeUpdate("UPDATE employees SET age = 26 WHERE name = 'Jane'");

20.7 Delete Record
stmt.executeUpdate("DELETE FROM employees WHERE name = 'Jane'");

20.8 PreparedStatement
PreparedStatement ps = conn.prepareStatement("SELECT * FROM employees WHERE age > ?");
ps.setInt(1, 30);
ResultSet rs = ps.executeQuery();

20.9 Loop Results
while(rs.next()) {
  System.out.println(rs.getString("name"));
}

20.10 Close Resources
rs.close();
stmt.close();
conn.close();

21.1 Add Npgsql Package
Install-Package Npgsql

21.2 Import Npgsql
using Npgsql;

21.3 Connect to DB
var conn = new NpgsqlConnection("Host=localhost;Username=postgres;Password=secret;Database=testdb");
conn.Open();

21.4 Select Query
var cmd = new NpgsqlCommand("SELECT * FROM employees", conn);
var reader = cmd.ExecuteReader();

21.5 Insert Record
cmd = new NpgsqlCommand("INSERT INTO employees(name) VALUES('Liam')", conn);
cmd.ExecuteNonQuery();

21.6 Update Record
cmd = new NpgsqlCommand("UPDATE employees SET age = 35 WHERE name = 'Liam'", conn);
cmd.ExecuteNonQuery();

21.7 Delete Record
cmd = new NpgsqlCommand("DELETE FROM employees WHERE name = 'Liam'", conn);
cmd.ExecuteNonQuery();

21.8 Read Data
while (reader.Read()) {
  Console.WriteLine(reader["name"]);
}

21.9 Use Parameters
cmd = new NpgsqlCommand("SELECT * FROM employees WHERE age > @age", conn);
cmd.Parameters.AddWithValue("@age", 30);
reader = cmd.ExecuteReader();

21.10 Close Connection
reader.Close();
conn.Close();

22.1 Install pg gem
gem install pg

22.2 Require Library
require 'pg'

22.3 Connect to DB
conn = PG.connect(dbname: 'testdb', user: 'postgres', password: 'secret')

22.4 Execute Query
res = conn.exec("SELECT * FROM employees")
res.each do |row|
  puts row["name"]
end

22.5 Insert Record
conn.exec("INSERT INTO employees(name) VALUES('RubyUser')")

22.6 Update Record
conn.exec("UPDATE employees SET age = 40 WHERE name = 'RubyUser'")

22.7 Delete Record
conn.exec("DELETE FROM employees WHERE name = 'RubyUser'")

22.8 Use Parameters
conn.exec_params("SELECT * FROM employees WHERE age > $1", [30])

22.9 Error Handling
begin
  conn.exec("INVALID SQL")
rescue PG::Error => e
  puts e.message
end

22.10 Close Connection
conn.close

23.1 Install pq driver
go get github.com/lib/pq

23.2 Import Packages
import (
  "database/sql"
  _ "github.com/lib/pq"
)

23.3 Open Connection
db, err := sql.Open("postgres", "user=postgres password=secret dbname=testdb sslmode=disable")

23.4 Select Query
rows, err := db.Query("SELECT name FROM employees")
for rows.Next() {
  var name string
  rows.Scan(&name)
  fmt.Println(name)
}

23.5 Insert Record
_, err := db.Exec("INSERT INTO employees(name) VALUES($1)", "GoUser")

23.6 Update Record
_, err := db.Exec("UPDATE employees SET age = $1 WHERE name = $2", 30, "GoUser")

23.7 Delete Record
_, err := db.Exec("DELETE FROM employees WHERE name = $1", "GoUser")

23.8 Prepare Statement
stmt, err := db.Prepare("SELECT name FROM employees WHERE age > $1")
rows, err := stmt.Query(30)

23.9 Error Handling
if err != nil {
  log.Fatal(err)
}

23.10 Close DB
defer db.Close()

24.1 Connect with psql
psql -U postgres -d testdb

24.2 Execute SQL in Script
#!/bin/bash
psql -U postgres -d testdb -c "SELECT * FROM employees"

24.3 Store Output
output=$(psql -U postgres -d testdb -t -c "SELECT COUNT(*) FROM employees")
echo $output

24.4 Insert Data
psql -U postgres -d testdb -c "INSERT INTO employees(name) VALUES('ShellUser')"

24.5 Use Variables
name="ShellUser"
psql -U postgres -d testdb -c "DELETE FROM employees WHERE name = '$name'"

24.6 Redirect SQL from File
psql -U postgres -d testdb -f script.sql

24.7 Backup Database
pg_dump -U postgres -d testdb -f backup.sql

24.8 Restore Database
psql -U postgres -d testdb -f backup.sql

24.9 Loop Execution
for i in {1..5}; do
  psql -U postgres -d testdb -c "SELECT $i"
done

24.10 Check Connection
pg_isready -h localhost -p 5432 -U postgres

25.1 Install PostgreSQL on Ubuntu
sudo apt update && sudo apt install postgresql

25.2 Enable Service
sudo systemctl enable postgresql

25.3 Start Service
sudo systemctl start postgresql

25.4 Create User
sudo -u postgres createuser myuser --interactive

25.5 Create Database
sudo -u postgres createdb mydb

25.6 Cloud Hosting Options
# Providers: Heroku, AWS RDS, DigitalOcean, Supabase

25.7 Deploy on Heroku
heroku create
heroku addons:create heroku-postgresql:hobby-dev

25.8 Connect Heroku DB
heroku pg:psql

25.9 Connect with psql
psql "postgres://user:password@host:port/dbname"

25.10 Backup and Restore
pg_dump -U postgres -d testdb > dump.sql
psql -U postgres -d newdb < dump.sql

26.1 Analyze Query
EXPLAIN ANALYZE SELECT * FROM employees WHERE age > 30;

26.2 Create Index
CREATE INDEX idx_age ON employees(age);

26.3 Vacuum
VACUUM ANALYZE employees;

26.4 Check Index Usage
EXPLAIN SELECT * FROM employees WHERE age > 30;

26.5 Adjust Work Mem
SET work_mem = '64MB';

26.6 Autovacuum Settings
ALTER TABLE employees SET (autovacuum_enabled = true);

26.7 Check Locks
SELECT * FROM pg_locks;

26.8 Use pg_stat_statements
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

26.9 Enable Parallel Query
SET max_parallel_workers_per_gather = 4;

26.10 Analyze Table
ANALYZE employees;

27.1 Create Role
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password';

27.2 Grant Permissions
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

27.3 Revoke Permissions
REVOKE DELETE ON employees FROM readonly_user;

27.4 Enable SSL
ALTER SYSTEM SET ssl = on;

27.5 Use pg_hba.conf
hostssl all all 0.0.0.0/0 md5

27.6 Change Password
ALTER USER postgres WITH PASSWORD 'newpassword';

27.7 Audit Logging
shared_preload_libraries = 'pgaudit'

27.8 Limit Connections
ALTER ROLE readonly_user CONNECTION LIMIT 5;

27.9 Remove Unused Roles
DROP ROLE IF EXISTS old_user;

27.10 Password Encryption
ALTER SYSTEM SET password_encryption = 'scram-sha-256';

28.1 Full Backup
pg_dump -U postgres -F c -b -v -f backup.dump testdb

28.2 Restore Backup
pg_restore -U postgres -d newdb -v backup.dump

28.3 Backup Specific Tables
pg_dump -U postgres -t employees testdb > employees.sql

28.4 Restore Specific Tables
psql -U postgres -d newdb -f employees.sql

28.5 Backup Globals
pg_dumpall -U postgres -g > globals.sql

28.6 Restore Globals
psql -U postgres -f globals.sql

28.7 Continuous Archiving
wal_level = replica

28.8 Point-in-Time Recovery
restore_command = 'cp /archive/%f %p'

28.9 Schedule Backup
cron job with pg_dump

28.10 Check Backup Integrity
pg_restore -l backup.dump

29.1 Enable WAL Archiving
wal_level = replica

29.2 Setup Primary Server
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'

29.3 Configure pg_hba.conf
host replication replica 192.168.1.0/24 md5

29.4 Setup Standby Server
pg_basebackup -h primary -D /var/lib/postgresql/standby -P -U replica

29.5 Start Standby
pg_ctl start -D /var/lib/postgresql/standby

29.6 Streaming Replication
primary_conninfo = 'host=primary port=5432 user=replica password=secret'

29.7 Monitor Replication
SELECT * FROM pg_stat_replication;

29.8 Promote Standby
pg_ctl promote -D /var/lib/postgresql/standby

29.9 Failover
Use repmgr or Patroni

29.10 Replication Slots
SELECT * FROM pg_replication_slots;

30.1 List Installed Extensions
SELECT * FROM pg_extension;

30.2 Install Extension
CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";

30.3 Use uuid-ossp
SELECT uuid_generate_v4();

30.4 Install PostGIS
CREATE EXTENSION postgis;

30.5 Use PostGIS Example
SELECT ST_Distance(ST_Point(0,0), ST_Point(3,4));

30.6 Remove Extension
DROP EXTENSION postgis;

30.7 Search Path
SET search_path TO public, extensions;

30.8 Custom Extensions
Create your own extension using C

30.9 Extension Updates
ALTER EXTENSION postgis UPDATE TO \"3.1.0\";

30.10 Extension Configuration
SELECT * FROM pg_extension_config_dump();

31.1 CTE (WITH Clause)
WITH older AS (SELECT * FROM employees WHERE age > 40) SELECT * FROM older;

31.2 Window Functions
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;

31.3 LATERAL Joins
SELECT e.name, d.dept FROM employees e, LATERAL (SELECT * FROM dept WHERE dept_id = e.dept_id) d;

31.4 JSON Queries
SELECT data->'name' FROM json_table WHERE id = 1;

31.5 Full Outer Join
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id;

31.6 EXISTS Clause
SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM dept WHERE dept.id = employees.dept_id);

31.7 CASE Statement
SELECT name, CASE WHEN age > 50 THEN 'Senior' ELSE 'Junior' END AS level FROM employees;

31.8 Recursive Query
WITH RECURSIVE t(n) AS (
  SELECT 1
  UNION ALL
  SELECT n+1 FROM t WHERE n < 5
) SELECT * FROM t;

31.9 COALESCE Function
SELECT name, COALESCE(email, 'not_provided') FROM users;

31.10 FILTER Clause
SELECT dept, COUNT(*) FILTER (WHERE gender = 'M') AS males FROM employees GROUP BY dept;

32.1 Create Trigger Function
CREATE FUNCTION log_update() RETURNS trigger AS $$
BEGIN
  INSERT INTO log_table(action, updated_at) VALUES('update', now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

32.2 Create Trigger
CREATE TRIGGER trg_update AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_update();

32.3 BEFORE INSERT Trigger
CREATE TRIGGER trg_before_insert BEFORE INSERT ON employees FOR EACH ROW EXECUTE FUNCTION check_insert();

32.4 DELETE Trigger
CREATE TRIGGER trg_delete AFTER DELETE ON employees FOR EACH ROW EXECUTE FUNCTION log_delete();

32.5 INSTEAD OF Trigger (View)
CREATE TRIGGER trg_instead INSTEAD OF INSERT ON my_view FOR EACH ROW EXECUTE FUNCTION handle_view_insert();

32.6 ENABLE/DISABLE Trigger
ALTER TABLE employees ENABLE TRIGGER trg_update;

32.7 DROP Trigger
DROP TRIGGER trg_update ON employees;

32.8 CREATE RULE
CREATE RULE replace_delete AS ON DELETE TO employees DO INSTEAD DELETE FROM archive WHERE id = OLD.id;

32.9 View Trigger Info
SELECT tgname FROM pg_trigger WHERE tgrelid = 'employees'::regclass;

32.10 Audit Example with Trigger
CREATE TRIGGER audit_trigger AFTER INSERT ON employees FOR EACH ROW EXECUTE FUNCTION audit_log();

33.1 Create Publication
CREATE PUBLICATION mypub FOR TABLE employees;

33.2 Create Subscription
CREATE SUBSCRIPTION mysub CONNECTION 'host=localhost dbname=src user=postgres' PUBLICATION mypub;

33.3 Check Publications
SELECT * FROM pg_publication;

33.4 Check Subscriptions
SELECT * FROM pg_subscription;

33.5 Add Table to Publication
ALTER PUBLICATION mypub ADD TABLE departments;

33.6 Remove Table from Publication
ALTER PUBLICATION mypub DROP TABLE departments;

33.7 Drop Subscription
DROP SUBSCRIPTION mysub;

33.8 Drop Publication
DROP PUBLICATION mypub;

33.9 Refresh Subscription
ALTER SUBSCRIPTION mysub REFRESH PUBLICATION;

33.10 Set Slot Name
CREATE SUBSCRIPTION mysub CONNECTION '...' PUBLICATION mypub WITH (slot_name = 'myslot');

34.1 Create tsvector column
ALTER TABLE documents ADD COLUMN content_vector tsvector;

34.2 Update tsvector
UPDATE documents SET content_vector = to_tsvector('english', content);

34.3 Basic Search
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('database');

34.4 Index tsvector
CREATE INDEX idx_fts_content ON documents USING GIN(content_vector);

34.5 Use plainto_tsquery
SELECT * FROM documents WHERE content_vector @@ plainto_tsquery('data structure');

34.6 Highlighting Results
SELECT ts_headline('english', content, to_tsquery('search')) FROM documents;

34.7 Phrase Search
SELECT * FROM documents WHERE content_vector @@ phraseto_tsquery('big data');

34.8 Ranking
SELECT title, ts_rank(content_vector, plainto_tsquery('database')) AS rank FROM documents ORDER BY rank DESC;

34.9 Search with Web Input
SELECT * FROM documents WHERE content_vector @@ websearch_to_tsquery('english', 'data AND index');

34.10 Store Search Config
ALTER TEXT SEARCH CONFIGURATION pg_catalog.english ALTER MAPPING FOR asciiword WITH english_stem;

35.1 Install Extension
CREATE EXTENSION postgres_fdw;

35.2 Create Server
CREATE SERVER foreign_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remote_host', dbname 'foreign_db');

35.3 Create User Mapping
CREATE USER MAPPING FOR local_user SERVER foreign_srv OPTIONS (user 'remote_user', password 'secret');

35.4 Import Schema
IMPORT FOREIGN SCHEMA public FROM SERVER foreign_srv INTO local_schema;

35.5 Manual Foreign Table
CREATE FOREIGN TABLE foreign_emps (id INT, name TEXT) SERVER foreign_srv OPTIONS (table_name 'employees');

35.6 Query Foreign Table
SELECT * FROM foreign_emps;

35.7 Join Local and Foreign
SELECT l.name, f.salary FROM local_emps l JOIN foreign_emps f ON l.id = f.id;

35.8 Drop Foreign Table
DROP FOREIGN TABLE foreign_emps;

35.9 Drop Server
DROP SERVER foreign_srv CASCADE;

35.10 Security Considerations
-- Use SSL and restrict permissions to avoid exposing sensitive data

36.1 List Installed Extensions
SELECT * FROM pg_extension;

36.2 Install hstore
CREATE EXTENSION hstore;

36.3 Install uuid-ossp
CREATE EXTENSION "uuid-ossp";

36.4 Generate UUID
SELECT uuid_generate_v4();

36.5 Use hstore
SELECT 'a=>1,b=>2'::hstore - 'a';

36.6 Install citext
CREATE EXTENSION citext;

36.7 Use citext
CREATE TABLE users (email CITEXT);

36.8 Drop Extension
DROP EXTENSION hstore;

36.9 Install intarray
CREATE EXTENSION intarray;

36.10 Full Text Config
CREATE TEXT SEARCH CONFIGURATION my_fts ( COPY = english );

37.1 View Settings
SHOW all;

37.2 Set Parameter
ALTER SYSTEM SET work_mem = '64MB';

37.3 Reload Config
SELECT pg_reload_conf();

37.4 Temp Config
SET work_mem TO '32MB';

37.5 Logging Settings
SHOW log_min_duration_statement;

37.6 Search Path
SET search_path TO myschema, public;

37.7 Autovacuum Settings
SHOW autovacuum;

37.8 Checkpoints
SHOW checkpoint_timeout;

37.9 Max Connections
SHOW max_connections;

37.10 WAL Settings
SHOW wal_level;

38.1 Check Version
SELECT version();

38.2 Install New Version
# OS level step: install postgresql-15

38.3 Initialize New Cluster
initdb -D /var/lib/postgresql/15/main

38.4 Use pg_upgrade
pg_upgrade -b old_bin -B new_bin -d old_data -D new_data

38.5 Check Compatibility
pg_upgrade --check

38.6 Analyze New Cluster
./analyze_new_cluster.sh

38.7 Delete Old Cluster
./delete_old_cluster.sh

38.8 pg_dump Upgrade
pg_dump dbname | psql -d newdb

38.9 Validate Upgrade
SELECT * FROM pg_stat_user_tables;

38.10 Review Logs
cat pg_upgrade_internal.log

39.1 Set wal_level
wal_level = replica

39.2 Enable Archive Mode
archive_mode = on

39.3 Setup Primary ConnInfo
primary_conninfo = 'host=master user=replicator password=secret'

39.4 Create Replication Slot
SELECT * FROM pg_create_physical_replication_slot('replica1');

39.5 Base Backup
pg_basebackup -h master -D /var/lib/postgresql/14/main -U replicator -Fp -Xs -P

39.6 Recovery Conf
restore_command = 'cp /var/lib/postgresql/wal/%f %p'

39.7 Monitor Replication
SELECT * FROM pg_stat_replication;

39.8 Failover
pg_ctl promote

39.9 Replication Delay
SELECT * FROM pg_stat_replication WHERE state = 'streaming';

39.10 Checkpoint Replication
SELECT * FROM pg_control_checkpoint();

40.1 Patroni
# Patroni setup for HA

40.2 repmgr
# repmgr setup for standby management

40.3 PgBouncer
# Lightweight connection pooling

40.4 Keepalived
# Virtual IP management for failover

40.5 HAProxy
# Load balancing PostgreSQL nodes

40.6 Systemd Units
# Manage PostgreSQL via systemctl

40.7 Custom Watchdog
# Cron-based failover check

40.8 Disaster Recovery
# Automated backup and restore

40.9 Heartbeat Monitor
# Network-based heartbeat setup

40.10 Tool Comparison
# Summary of HA tools: Patroni vs. repmgr vs. others