SET file_copy_method = clone;

-- 终止模板库的所有活动连接（必须）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'source_db' AND pid != pg_backend_pid();

-- 执行瞬间克隆
CREATE DATABASE clone_db TEMPLATE source_db STRATEGY FILE_COPY;

-- 连接到克隆库
\\c clone_db

-- 验证数据完整性（示例：检查表行数）
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 10;

-- ============================================
-- 环境准备：确认参数设置
-- ============================================
SHOW file_copy_method;
-- 预期输出: clone

SHOW server_version;
-- 预期输出: 18.x

-- ============================================
-- 步骤 1：创建示例源数据库并写入测试数据
-- ============================================
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;

-- 确认数据量
SELECT count(*) FROM documents;
-- 预期输出: 100000

SELECT pg_size_pretty(pg_database_size('ai_production'));
-- 查看数据库大小

-- ============================================
-- 步骤 2：瞬间克隆沙盒
-- ============================================
\\c postgres

-- 记录开始时间
\\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;
-- 预期输出: CREATE DATABASE
-- 预期耗时: < 200ms（无论数据库多大）

\\timing off

-- ============================================
-- 步骤 3：在沙盒中自由操作（不影响源库）
-- ============================================
\\c ai_sandbox

-- AI Agent 可以安全地执行任意操作
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;

-- 验证沙盒修改
SELECT count(*) FROM documents;
-- 预期输出: 50000（已删除一半）

-- ============================================
-- 步骤 4：验证源库数据完整性（未受影响）
-- ============================================
\\c ai_production

SELECT count(*) FROM documents;
-- 预期输出: 100000（源库数据完好无损）

-- 确认源库没有 ai_score 列
SELECT column_name FROM information_schema.columns
WHERE table_name = 'documents' ORDER BY ordinal_position;
-- 不包含 ai_score

-- ============================================
-- 步骤 5：清理沙盒
-- ============================================
\\c postgres
DROP DATABASE ai_sandbox;

-- 1. 查看当前实例已有哪些库，选一个作为模板
\\l

-- 2. 查看模板库大小
SELECT pg_size_pretty(pg_database_size('your_db_name')) AS db_size;

-- 3. 测试克隆模式（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. 测试传统复制（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. 清理
DROP DATABASE test_clone;
DROP DATABASE test_copy;