SET file_copy_method = clone;

-- Terminate all active connections to the template library (Required)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'source_db' AND pid != pg_backend_pid();

-- Perform an instant clone
CREATE DATABASE clone_db TEMPLATE source_db STRATEGY FILE_COPY;

-- Connect to the clone library
\\c clone_db

-- Verify data integrity (Example: Check the table row count)
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 10;

-- ============================================
-- Environment Preparation: Verify Parameter Configuration
-- ============================================
SHOW file_copy_method;
-- Expected output: clone

SHOW server_version;
-- Expected output: 18.x

-- ============================================
-- Step 1: Create a sample source database and write test data
-- ============================================
CREATE DATABASE ai_production;
\\c ai_production

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    embedding vector(3),
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO documents (title, content, embedding)
SELECT
    'Document ' || i,
    'Content for document ' || i,
    ('[' || (random())::text || ',' || (random())::text || ',' || (random())::text || ']')::vector
FROM generate_series(1, 100000) AS i;

-- Confirm the data volume
SELECT count(*) FROM documents;
-- Expected output: 100000

SELECT pg_size_pretty(pg_database_size('ai_production'));
-- Check the database size

-- ============================================
-- Step 2: Instant clone the sandbox
-- ============================================
\\c postgres

-- Record the start time
\\timing on

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'ai_production' AND pid != pg_backend_pid();

CREATE DATABASE ai_sandbox TEMPLATE ai_production STRATEGY FILE_COPY;
-- Expected output: CREATE DATABASE
-- Expected time: < 200 ms (regardless of the database size)

\\timing off

-- ============================================
-- Step 3: Freely operate in the sandbox (without affecting the source database)
-- ============================================
\\c ai_sandbox

-- The AI Agent can safely perform any operation
DELETE FROM documents WHERE id > 50000;
ALTER TABLE documents ADD COLUMN ai_score FLOAT DEFAULT 0;
UPDATE documents SET ai_score = random() WHERE id <= 1000;

-- Verify the sandbox modifications
SELECT count(*) FROM documents;
-- Expected output: 50000 (half of the data has been deleted)

-- ============================================
-- Step 4: Verify the integrity of the source database data (unaffected)
-- ============================================
\\c ai_production

SELECT count(*) FROM documents;
-- Expected output: -100000 (the source database data remains intact)

-- Confirm that the source database does not have an ai_score column
SELECT column_name FROM information_schema.columns
WHERE table_name = 'documents' ORDER BY ordinal_position;
-- Does not contain ai_score

-- ============================================
-- Step 5: Clean up the sandbox
-- ============================================
\\c postgres
DROP DATABASE ai_sandbox;

-- 1. View the databases that already exist in the current instance, and select one as a template
\\l

-- 2. Check the size of the template database
SELECT pg_size_pretty(pg_database_size('your_db_name')) AS db_size;

-- 3. Test the clone mode (file_copy_method = clone)
\\timing on
SET file_copy_method = clone;
SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
 WHERE datname = 'your_db_name' AND pid != pg_backend_pid();
CREATE DATABASE test_clone TEMPLATE your_db_name STRATEGY FILE_COPY;
\\timing off

-- 4. Test the traditional copy mode (file_copy_method = copy)
\\timing on
SET file_copy_method = copy;
SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
 WHERE datname = 'your_db_name' AND pid != pg_backend_pid();
CREATE DATABASE test_copy TEMPLATE your_db_name STRATEGY FILE_COPY;
\\timing off

-- 5. Clean up
DROP DATABASE test_clone;
DROP DATABASE test_copy;