Contents

PgClone Manual Testing Guide

Step-by-step manual test scenarios for validating all pgclone functionalities on real infrastructure with two PostgreSQL instances.

Environment

Replace connection details with your actual servers.

Parameter Server 1 (Source) Server 2 (Target)
IP Address 172.17.0.2 172.17.0.3
Port 5432 5433
Database db1 db2
User / Pass postgres / 123654 postgres / 123654
pgclone Installed + preloaded Installed + preloaded
Auth scram-sha-256 scram-sha-256

Connection string used throughout:

host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654

All SQL commands run on Server 2 (Target) unless stated otherwise.

Pre-Test: Seed Source Database (Server 1)

Connect to Server 1 and load the project seed file:

psql -h 172.17.0.2 -p 5432 -U postgres -d db1 -f test/fixtures/seed.sql

This creates test_schema with tables (customers, orders, order_items, employees), indexes, constraints, triggers, views, materialized views, functions, sequences, roles (test_reader, test_writer, test_admin), and sample data.

Pre-Test: Prepare Target (Server 2)

psql -h 172.17.0.3 -p 5433 -U postgres -d db2

CREATE EXTENSION IF NOT EXISTS pgclone;
SELECT pgclone.version();
-- Expected: pgclone 3.6.0

Test 1: Extension Installation & Version

SELECT pgclone.version();

Expected: pgclone 3.6.0

SELECT * FROM pg_extension WHERE extname = 'pgclone';

Expected: One row with extname = 'pgclone'

Test 2: Synchronous Table Cloning

2.1 Clone table with data

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true
);

Expected: OK: cloned test_schema.customers (10 rows)

SELECT COUNT(*) FROM test_schema.customers;

Expected: 10

2.2 Clone structure only (no data)

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'orders', false
);

SELECT COUNT(*) FROM test_schema.orders;

Expected: 0 (table exists but empty)

2.3 Clone with different target name

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'customers_backup'
);

SELECT COUNT(*) FROM test_schema.customers_backup;

Expected: 10

2.4 Selective column cloning

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'customers_lite',
    '{"columns": ["id", "name", "email"]}'
);

SELECT column_name FROM information_schema.columns
WHERE table_schema = 'test_schema' AND table_name = 'customers_lite'
ORDER BY ordinal_position;

Expected: Only 3 columns: id, name, email

2.5 Clone with WHERE filter

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'active_customers_copy',
    '{"where": "status = ''active''"}'
);

SELECT COUNT(*) FROM test_schema.active_customers_copy;
SELECT DISTINCT status FROM test_schema.active_customers_copy;

Expected: 7 rows, all with status = 'active'

2.6 Columns + WHERE combined

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'high_scorers',
    '{"columns": ["id", "name", "score"], "where": "score >= 80"}'
);

SELECT * FROM test_schema.high_scorers ORDER BY score DESC;

Expected: Only rows with score >= 80, only 3 columns

Test 3: Indexes, Constraints & Triggers Control

3.1 Clone without indexes

DROP TABLE IF EXISTS test_schema.customers CASCADE;

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'customers',
    '{"indexes": false}'
);

SELECT indexname FROM pg_indexes
WHERE schemaname = 'test_schema' AND tablename = 'customers';

Expected: Only PK index (customers_pkey). No idx_customers_status or idx_customers_name_lower.

3.2 Clone without constraints

DROP TABLE IF EXISTS test_schema.order_items CASCADE;
DROP TABLE IF EXISTS test_schema.orders CASCADE;

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'orders', true,
    'orders',
    '{"constraints": false}'
);

SELECT conname, contype FROM pg_constraint
WHERE conrelid = 'test_schema.orders'::regclass;

Expected: No FK or CHECK constraints (only PK remains)

3.3 Clone without triggers

DROP TABLE IF EXISTS test_schema.orders CASCADE;

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'orders', true,
    'orders',
    '{"triggers": false}'
);

SELECT tgname FROM pg_trigger
WHERE tgrelid = 'test_schema.orders'::regclass AND NOT tgisinternal;

Expected: No triggers

3.4 pgclone.table_ex() with boolean parameters

DROP TABLE IF EXISTS test_schema.orders CASCADE;

SELECT pgclone.table_ex(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'orders', true, 'orders',
    false,  -- skip indexes
    true,   -- include constraints
    false   -- skip triggers
);

SELECT conname FROM pg_constraint WHERE conrelid = 'test_schema.orders'::regclass;
SELECT indexname FROM pg_indexes WHERE schemaname = 'test_schema' AND tablename = 'orders';
SELECT tgname FROM pg_trigger WHERE tgrelid = 'test_schema.orders'::regclass AND NOT tgisinternal;

Expected: Constraints present (PK + FK). Only PK index. No triggers.

Test 4: Conflict Resolution Strategies

4.1 Default (error)

-- Table already exists from previous tests
SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true
);

Expected: ERROR — table already exists

4.2 Skip

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'customers',
    '{"conflict": "skip"}'
);

Expected: Message indicating table was skipped

4.3 Replace

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'customers',
    '{"conflict": "replace"}'
);

SELECT COUNT(*) FROM test_schema.customers;

Expected: 10 (fresh clone after drop + re-create)

4.4 Rename

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'customers',
    '{"conflict": "rename"}'
);

SELECT tablename FROM pg_tables
WHERE schemaname = 'test_schema' AND tablename LIKE 'customers%'
ORDER BY tablename;

Expected: Both customers (new) and customers_old (renamed original)

Test 5: Schema Cloning

5.1 Full schema clone with data

DROP SCHEMA IF EXISTS test_schema CASCADE;

SELECT pgclone.schema(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', true
);

Verify all object types:

-- Tables
SELECT tablename FROM pg_tables WHERE schemaname = 'test_schema' ORDER BY tablename;

-- Row counts
SELECT 'customers' AS tbl, COUNT(*) FROM test_schema.customers
UNION ALL SELECT 'orders', COUNT(*) FROM test_schema.orders
UNION ALL SELECT 'order_items', COUNT(*) FROM test_schema.order_items
UNION ALL SELECT 'employees', COUNT(*) FROM test_schema.employees;

-- Views
SELECT viewname FROM pg_views WHERE schemaname = 'test_schema';

-- Materialized views
SELECT matviewname FROM pg_matviews WHERE schemaname = 'test_schema';

-- Functions
SELECT routine_name FROM information_schema.routines
WHERE routine_schema = 'test_schema' ORDER BY routine_name;

-- Sequences
SELECT sequencename FROM pg_sequences WHERE schemaname = 'test_schema';

-- Triggers
SELECT tgname FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'test_schema' AND NOT t.tgisinternal;

-- Indexes
SELECT indexname FROM pg_indexes WHERE schemaname = 'test_schema' ORDER BY indexname;

Expected: All tables, views, materialized views, functions, sequences, triggers, and indexes present with correct data.

5.2 Schema clone structure only

DROP SCHEMA IF EXISTS test_schema CASCADE;

SELECT pgclone.schema(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', false
);

SELECT 'customers' AS tbl, COUNT(*) FROM test_schema.customers
UNION ALL SELECT 'orders', COUNT(*) FROM test_schema.orders;

Expected: All counts = 0

5.3 Schema clone with JSON options

DROP SCHEMA IF EXISTS test_schema CASCADE;

SELECT pgclone.schema(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', true,
    '{"triggers": false, "indexes": false}'
);

SELECT COUNT(*) FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'test_schema' AND NOT t.tgisinternal;

Expected: Trigger count = 0. Only PK indexes present.

5.4 pgclone.schema_ex() with boolean parameters

DROP SCHEMA IF EXISTS test_schema CASCADE;

SELECT pgclone.schema_ex(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', true,
    true,   -- include indexes
    false,  -- skip constraints
    true    -- include triggers
);

Expected: Indexes and triggers present. No FK/CHECK constraints (except PK).

Test 6: Function Cloning

DROP SCHEMA IF EXISTS test_schema CASCADE;
CREATE SCHEMA test_schema;

SELECT pgclone.functions(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema'
);

SELECT routine_name, routine_type FROM information_schema.routines
WHERE routine_schema = 'test_schema' ORDER BY routine_name;

Expected: Functions: get_customer_orders, log_order_change, update_timestamp

Test 7: Database Cloning

7.1 Clone into current database

DROP SCHEMA IF EXISTS test_schema CASCADE;
DROP TABLE IF EXISTS public.simple_test CASCADE;

SELECT pgclone.database(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    true
);

SELECT schemaname, COUNT(*) AS table_count
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
GROUP BY schemaname ORDER BY schemaname;

Expected: Both public and test_schema tables cloned with data.

7.2 Clone database with options

DROP SCHEMA IF EXISTS test_schema CASCADE;
DROP TABLE IF EXISTS public.simple_test CASCADE;

SELECT pgclone.database(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    true,
    '{"triggers": false}'
);

Expected: All data cloned, no triggers on any table.

7.3 Clone into a new database (pgclone.database_create)

Run from the postgres database on Server 2, not db2.

psql -h 172.17.0.3 -p 5433 -U postgres -d postgres

CREATE EXTENSION IF NOT EXISTS pgclone;

SELECT pgclone.database_create(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'db1_clone',
    true
);

\c db1_clone

SELECT schemaname, COUNT(*) AS table_count
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
GROUP BY schemaname;

Expected: New database db1_clone created with all schemas and data from source.

Test 8: Data Masking During Clone (v3.0.0)

Masking is applied server-side during COPY — source data never stored unmasked on target.

8.1 Email masking

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_email_masked',
    '{"mask": {"email": "email"}}'
);

SELECT id, full_name, email FROM test_schema.emp_email_masked ORDER BY id;

Expected: alice@example.com → a***@example.com

8.2 Name masking

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_name_masked',
    '{"mask": {"full_name": "name"}}'
);

SELECT id, full_name FROM test_schema.emp_name_masked ORDER BY id;

Expected: All full_name = XXXX

8.3 Phone masking

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_phone_masked',
    '{"mask": {"phone": "phone"}}'
);

SELECT id, phone FROM test_schema.emp_phone_masked ORDER BY id;

Expected: +1-555-123-4567 → ***-4567. Eve’s NULL phone stays NULL.

8.4 Hash masking (MD5)

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_hash_masked',
    '{"mask": {"email": "hash"}}'
);

SELECT id, email FROM test_schema.emp_hash_masked ORDER BY id;

Expected: Emails are 32-character hex MD5 hashes. Same input → same hash (deterministic).

8.5 Null masking

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_null_masked',
    '{"mask": {"ssn": "null"}}'
);

SELECT id, ssn FROM test_schema.emp_null_masked ORDER BY id;

Expected: All SSN values are NULL.

8.6 Partial masking

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_partial_masked',
    '{"mask": {"full_name": {"type": "partial", "prefix": 2, "suffix": 3}}}'
);

SELECT id, full_name FROM test_schema.emp_partial_masked ORDER BY id;

Expected: Alice Johnson → Al***son

8.7 Random integer masking

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_rand_masked',
    '{"mask": {"salary": {"type": "random_int", "min": 30000, "max": 150000}}}'
);

SELECT id, salary FROM test_schema.emp_rand_masked ORDER BY id;

Expected: Salaries are random integers between 30000 and 150000.

8.8 Constant masking

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_const_masked',
    '{"mask": {"notes": {"type": "constant", "value": "REDACTED"}}}'
);

SELECT id, notes FROM test_schema.emp_const_masked ORDER BY id;

Expected: All notes = REDACTED (including previously NULL values).

8.9 Multiple masks combined

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_full_masked',
    '{"mask": {"email": "email", "full_name": "name", "phone": "phone", "ssn": "null", "salary": {"type": "random_int", "min": 40000, "max": 200000}, "notes": {"type": "constant", "value": "REDACTED"}}}'
);

SELECT * FROM test_schema.emp_full_masked ORDER BY id;

Expected: All sensitive columns masked. id and created_at pass through unchanged.

Test 9: Auto-Discovery of Sensitive Data (v3.1.0)

SELECT pgclone.discover_sensitive(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema'
);

Expected: JSON output with detected sensitive columns grouped by table, e.g.:

{"employees": {"email": "email", "full_name": "name", "phone": "phone", "salary": "random_int", "ssn": "null"}}

The output can be used directly as the "mask" option in a clone call.

Test 10: Static Data Masking (v3.2.0)

Apply masking to an already-existing local table.

10.1 Clone without masking first

DROP TABLE IF EXISTS test_schema.emp_for_static_mask;

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_for_static_mask'
);

-- Verify unmasked data
SELECT full_name, email, ssn FROM test_schema.emp_for_static_mask ORDER BY id;

Expected: Original data visible.

10.2 Apply in-place masking

SELECT pgclone.mask_in_place(
    'test_schema', 'emp_for_static_mask',
    '{"email": "email", "full_name": "name", "ssn": "null"}'
);

Expected: OK: masked 5 rows in test_schema.emp_for_static_mask (3 columns)

SELECT full_name, email, ssn FROM test_schema.emp_for_static_mask ORDER BY id;

Expected: full_name = XXXX, email masked, ssn = NULL

Test 11: Dynamic Data Masking (v3.3.0)

Role-based masking policies that preserve original data.

11.1 Prepare

DROP TABLE IF EXISTS test_schema.employees CASCADE;

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true
);

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'data_admin') THEN
        CREATE ROLE data_admin WITH LOGIN PASSWORD 'admin123';
    END IF;
END $$;

11.2 Create masking policy

SELECT pgclone.create_masking_policy(
    'test_schema', 'employees',
    '{"email": "email", "full_name": "name", "ssn": "null", "salary": {"type": "random_int", "min": 40000, "max": 200000}}',
    'data_admin'
);

Expected: OK. Creates test_schema.employees_masked view.

11.3 Verify masked view

SELECT * FROM test_schema.employees_masked ORDER BY id;

Expected: Masked data — XXXX names, masked emails, NULL ssn, random salaries.

11.4 Verify original data intact

SELECT full_name, email, ssn, salary FROM test_schema.employees ORDER BY id;

Expected: Original data unchanged (Alice Johnson, alice@example.com, 123-45-6789, 95000).

11.5 Test role-based access

SET ROLE data_admin;
SELECT full_name, email, ssn FROM test_schema.employees ORDER BY id;
RESET ROLE;

Expected: data_admin can see original unmasked data via the base table.

11.6 Drop masking policy

SELECT pgclone.drop_masking_policy('test_schema', 'employees');

SELECT viewname FROM pg_views
WHERE schemaname = 'test_schema' AND viewname = 'employees_masked';

Expected: No rows — view dropped, base table access restored to PUBLIC.

Test 12: Clone Roles & Permissions (v3.4.0)

12.1 Clone all roles

SELECT pgclone.clone_roles(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654'
);

Expected: OK: N roles created, N roles updated, N grants applied

SELECT rolname, rolcanlogin, rolcreatedb FROM pg_roles
WHERE rolname IN ('test_reader', 'test_writer', 'test_admin')
ORDER BY rolname;

Expected: All three roles exist with correct attributes.

12.2 Clone specific roles

DROP ROLE IF EXISTS test_reader;
DROP ROLE IF EXISTS test_writer;

SELECT pgclone.clone_roles(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_reader, test_writer'
);

SELECT rolname FROM pg_roles WHERE rolname IN ('test_reader', 'test_writer') ORDER BY rolname;

Expected: test_reader and test_writer exist.

12.3 Verify permissions

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'test_reader' AND table_schema = 'test_schema'
ORDER BY table_name, privilege_type;

Expected: test_reader has SELECT on all test_schema tables.

12.4 Existing role update

SELECT pgclone.clone_roles(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_admin'
);

Expected: Returns with N roles updated. Attributes and password synced.

Test 13: Clone Verification (v3.5.0)

13.1 Prepare

DROP SCHEMA IF EXISTS test_schema CASCADE;

SELECT pgclone.schema(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', true
);

13.2 Verify specific schema

SELECT * FROM pgclone.verify(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema'
);

Expected: All tables show ✓ (matching row counts).

13.3 Verify all schemas

SELECT * FROM pgclone.verify(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654'
);

Expected: Rows for all tables across all user schemas.

13.4 Verify with intentional mismatch

DELETE FROM test_schema.customers WHERE id > 5;
ANALYZE test_schema.customers;

SELECT * FROM pgclone.verify(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema'
);

Expected: customers shows ✗ — source_rows=10, target_rows=5.

Test 14: GDPR/Compliance Masking Report (v3.6.0)

14.1 Report on unmasked schema

SELECT * FROM pgclone.masking_report('test_schema');

Expected: Lists sensitive columns with sensitivity categories (Email, PII - Name, Phone, Financial, National ID), mask_status = UNMASKED, and recommended strategies.

14.2 Apply policy then re-check

SELECT pgclone.create_masking_policy(
    'test_schema', 'employees',
    '{"email": "email", "full_name": "name", "ssn": "null"}',
    'data_admin'
);

SELECT * FROM pgclone.masking_report('test_schema');

Expected: Employee columns now show mask_status = MASKED (view).

-- Cleanup
SELECT pgclone.drop_masking_policy('test_schema', 'employees');

Test 15: Async Operations & Background Workers

Requires shared_preload_libraries = 'pgclone' (already configured).

15.1 Async table clone

DROP TABLE IF EXISTS test_schema.customers CASCADE;

SELECT pgclone.table_async(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true
);

Expected: Returns a job_id (integer).

15.2 Check progress

SELECT pgclone.progress(1);

SELECT * FROM pgclone.jobs_view;

Expected: Shows status (pending/running/completed), rows_copied, progress_bar, elapsed_time.

15.3 Async schema clone

DROP SCHEMA IF EXISTS test_schema CASCADE;

SELECT pgclone.schema_async(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', true
);

SELECT job_id, status, schema_name, progress_bar FROM pgclone.jobs_view;

Expected: Schema clone progress with table-level tracking.

15.4 List all jobs

SELECT pgclone.jobs();

Expected: JSON array of all jobs.

15.5 Async with conflict strategy

SELECT pgclone.table_async(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'customers',
    '{"conflict": "replace"}'
);

Expected: Job starts, replaces existing table.

15.6 Cancel a job

-- Start a clone and cancel it (use the returned job_id)
SELECT pgclone.schema_async(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', true,
    '{"conflict": "replace"}'
);

SELECT pgclone.cancel(3);  -- use actual job_id

SELECT status FROM pgclone.jobs_view WHERE job_id = 3;

Expected: status = 'cancelled'

15.7 Clear completed jobs

SELECT pgclone.clear_jobs();

SELECT * FROM pgclone.jobs_view;

Expected: Returns count of cleared jobs. Only running/pending jobs remain.

Test 16: Parallel Cloning (Worker Pool)

16.1 Parallel schema clone

DROP SCHEMA IF EXISTS test_schema CASCADE;

SELECT pgclone.schema_async(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', true,
    '{"parallel": 4}'
);

SELECT job_id, status, op_type, table_name, progress_bar
FROM pgclone.jobs_view ORDER BY job_id;

Expected: Parent job + up to 4 pool worker jobs visible.

-- After completion, verify
SELECT * FROM pgclone.verify(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema'
);

Expected: All tables match source row counts.

16.2 Parallel with combined options

DROP SCHEMA IF EXISTS test_schema CASCADE;

SELECT pgclone.schema_async(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', true,
    '{"parallel": 4, "conflict": "replace", "triggers": false}'
);

Expected: Parallel clone completes without triggers.

Test 17: Progress Tracking View

SELECT pgclone.schema_async(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', true,
    '{"conflict": "replace"}'
);

-- Detailed progress function
SELECT * FROM pgclone.progress_detail();

-- Convenience view
SELECT job_id, status, schema_name, table_name,
       pct_complete, progress_bar, elapsed_time
FROM pgclone.jobs_view;

-- Filter by status
SELECT job_id, status, elapsed_time FROM pgclone.jobs_view WHERE status = 'running';
SELECT job_id, error_message FROM pgclone.jobs_view WHERE status = 'failed';

Expected: All columns visible — job_id, status, op_type, schema_name, table_name, current_phase, tables_total, tables_completed, rows_copied, bytes_copied, elapsed_ms, start_time, end_time, pct_complete, progress_bar, elapsed_time.

Test 18: Edge Cases & Error Handling

18.1 Invalid connection string

SELECT pgclone.table(
    'host=192.168.99.99 dbname=nonexistent user=postgres password=wrong',
    'public', 'test', true
);

Expected: ERROR — connection failure.

18.2 Non-existent table

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'this_table_does_not_exist', true
);

Expected: ERROR — table not found.

18.3 Non-existent schema

SELECT pgclone.schema(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'nonexistent_schema', true
);

Expected: ERROR — schema not found.

18.4 SQL injection in WHERE clause

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'injection_test',
    '{"where": "1=1; DROP TABLE test_schema.customers; --"}'
);

Expected: ERROR — DDL/DML keywords or semicolons rejected.

18.5 Invalid mask strategy

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'employees', true,
    'emp_bad_mask',
    '{"mask": {"email": "nonexistent_strategy"}}'
);

Expected: ERROR — unknown mask strategy.

18.6 Invalid JSON options

SELECT pgclone.table(
    'host=172.17.0.2 port=5432 dbname=db1 user=postgres password=123654',
    'test_schema', 'customers', true,
    'json_test',
    '{"invalid json'
);

Expected: ERROR — invalid JSON.

Cleanup

-- On Server 2 (target), connected to db2
DROP SCHEMA IF EXISTS test_schema CASCADE;
DROP TABLE IF EXISTS public.simple_test CASCADE;

DROP ROLE IF EXISTS test_reader;
DROP ROLE IF EXISTS test_writer;
DROP ROLE IF EXISTS test_admin;
DROP ROLE IF EXISTS data_admin;

SELECT pgclone.clear_jobs();

-- To drop the cloned database (connect to postgres db first):
-- \c postgres
-- DROP DATABASE IF EXISTS db1_clone;