Lab 12: Backup, Recovery & Neon Branching¶

SET search_path = fsu;

-- Record the count before the disaster
SELECT 'Before disaster:' AS event, COUNT(*) AS students FROM students
UNION ALL
SELECT 'Enrollments:', COUNT(*) FROM enrollments;

-- THE DISASTER: someone accidentally deletes all CS department students
BEGIN;
DELETE FROM enrollments
WHERE student_id IN (SELECT student_id FROM students WHERE dept_id = 1);

DELETE FROM students WHERE dept_id = 1;

-- "Oops" — how many rows deleted?
SELECT 'After disaster:' AS event, COUNT(*) AS students FROM students
UNION ALL
SELECT 'Enrollments:', COUNT(*) FROM enrollments;

-- DO NOT COMMIT — we'll recover two ways
ROLLBACK;  -- first recovery: transaction rollback

SELECT 'After rollback:' AS event, COUNT(*) AS students FROM students;
-- Should be back to original count

-- This time, commit the deletion — simulating a real production incident
BEGIN;
DELETE FROM enrollments WHERE student_id IN (
    SELECT student_id FROM students WHERE dept_id = 1 LIMIT 10
);
DELETE FROM students WHERE dept_id = 1 AND student_id IN (
    SELECT student_id FROM students WHERE dept_id = 1 LIMIT 10
);
COMMIT;  -- data is gone — need to recover from backup

SELECT 'After committed delete:', COUNT(*) FROM students WHERE dept_id=1;

-- Verify recovery
SELECT 'After restore:', COUNT(*) FROM fsu.students WHERE dept_id = 1;

-- On lab-12 branch: simulate more data changes
UPDATE fsu.students SET gpa = 0.00 WHERE student_id <= 20;
DELETE FROM fsu.enrollments WHERE semester = '2023SP';

SELECT 'Corrupted students:', COUNT(*) FROM fsu.students WHERE gpa = 0;
SELECT '2023SP enrollments:', COUNT(*) FROM fsu.enrollments WHERE semester = '2023SP';

-- Run this on the CORRUPTED branch (lab-12)
-- Compare with the output from the CHECKPOINT branch

SELECT
    'lab-12 (corrupted)' AS branch,
    COUNT(*) AS total_students,
    COUNT(CASE WHEN gpa = 0 THEN 1 END) AS zero_gpa_students,
    (SELECT COUNT(*) FROM enrollments WHERE semester='2023SP') AS sp23_enrollments
FROM fsu.students;

SET search_path = fsu;

CREATE OR REPLACE FUNCTION verify_lab12()
RETURNS TABLE(check_id TEXT, description TEXT, result TEXT, points INT) AS $$
BEGIN
    RETURN QUERY SELECT '01', 'Students table has 500+ rows (recovery successful)',
        CASE WHEN (SELECT COUNT(*) FROM students) >= 500 THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '02', 'CS dept students exist (dept_id=1)',
        CASE WHEN (SELECT COUNT(*) FROM students WHERE dept_id=1) > 0
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '03', 'No students have GPA=0 after recovery',
        CASE WHEN (SELECT COUNT(*) FROM students WHERE gpa = 0) = 0
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '04', 'Enrollments table has 1000+ rows',
        CASE WHEN (SELECT COUNT(*) FROM enrollments) >= 1000 THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '05', '2024FA enrollments still exist',
        CASE WHEN (SELECT COUNT(*) FROM enrollments WHERE semester='2024FA') > 0
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '06', 'All departments have at least 1 student',
        CASE WHEN NOT EXISTS(
            SELECT dept_id FROM departments d
            WHERE NOT EXISTS(SELECT 1 FROM students s WHERE s.dept_id=d.dept_id)
        ) THEN 'PASS' ELSE 'NEEDS_RECOVERY' END, 10;

    RETURN QUERY SELECT '07', 'fsu schema intact (5 base tables)',
        CASE WHEN (SELECT COUNT(*) FROM information_schema.tables
                   WHERE table_schema='fsu' AND table_type='BASE TABLE') >= 5
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '08', 'grade_points view exists',
        CASE WHEN EXISTS(SELECT 1 FROM information_schema.views
                         WHERE table_schema='fsu' AND table_name='grade_points')
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '09', 'Indexes still present after recovery',
        CASE WHEN (SELECT COUNT(*) FROM pg_indexes WHERE schemaname='fsu'
                   AND indexname NOT LIKE '%pkey%') >= 3
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '10', 'schema_migrations table intact',
        CASE WHEN EXISTS(SELECT 1 FROM information_schema.tables
                         WHERE table_schema='fsu' AND table_name='schema_migrations')
             THEN 'PASS' ELSE 'FAIL' END, 10;
END;
$$ LANGUAGE plpgsql;

SELECT check_id, description, result,
       CASE WHEN result = 'PASS' THEN points ELSE 0 END AS earned
FROM verify_lab12()
ORDER BY check_id;