Contents
- PgClone Testing
- Test Suite Overview
- 1. pgTAP Sync Tests (66 tests)
- 2. Loopback-DDL Tests (21 tests)
- 3. Database Create Tests (7 tests)
- 4. Async Tests (21 tests)
- 5. Consistent-Snapshot Tests (v4.3.0)
- 6. Snapshot-Keeper Resilience Tests (v4.3.1, issue #9)
- 7. Oracle Integration Test (43 checks)
- 8. SQL Server Integration Test (57 checks)
- 9. MySQL Integration Test (70 checks)
- Running Tests Locally
- Docker Test Architecture
- GitHub Actions CI
- Test Data
- Adding New Tests
- Test Suite Overview
PgClone Testing
PgClone has a comprehensive test suite that runs across PostgreSQL versions 14–18 using pgTAP, Docker Compose, and GitHub Actions CI.
Test Suite Overview
The test suite is organized into nine test groups (the Oracle, SQL Server and MySQL integration suites are described in sections 7, 8 and 9):
1. pgTAP Sync Tests (66 tests)
Located in test/pgclone_test.sql, these cover core synchronous functionality:
| Group | Tests | What It Covers |
|---|---|---|
| 1. Extension loading | 3 | Extension installs, version string |
| 2. Table clone (data) | 3 | Clone table with data, verify rows |
| 3. Table rename | 3 | Clone with different target name |
| 4. Structure only | 3 | Clone without data, verify 0 rows |
| 5. Schema clone | 5 | Full schema with tables + verify data |
| 6. Indexes | 2 | Verify indexes are cloned |
| 7. Constraints | 3 | PK, FK verification |
| 8. Views | 2 | Regular views cloned |
| 9. Functions | 2 | Functions cloned with correct signatures |
| 10. Selective columns | 3 | Column filtering, verify column count |
| 11. WHERE filter | 2 | Row filtering, verify filtered count |
| 12. SQL injection | 4 | Semicolon, DROP, INSERT rejection, false-positive safety |
| 13. Email mask | 5 | Email masking, domain preserved, original removed |
| 14. Name mask | 2 | Name replacement with XXXX |
| 15. Null mask | 2 | SSN nullification |
| 16. Hash mask | 2 | MD5 hash, 32-char hex output |
| 17. Constant mask | 2 | Fixed value replacement |
| 18. Combined masks | 3 | Multi-mask + WHERE filter composition |
| 19. Auto-discovery | 6 | Detect email, name, phone, salary, ssn columns |
| 20. Mask in-place | 7 | Clone-then-mask workflow, verify UPDATE masking |
2. Loopback-DDL Tests (21 tests)
Located in test/test_loopback.sh. These tests call functions that use loopback libpq connections for DDL (CREATE VIEW, CREATE ROLE, GRANT, etc.) which cannot run inside pgTAP’s transaction without deadlocking:
| Section | Tests | What It Covers |
|---|---|---|
| Clone roles | 6 | Role creation, LOGIN/CREATEDB attributes, permissions |
| Clone verify | 4 | Row count comparison, match indicators, schema/all modes |
| Masking report | 4 | GDPR audit, sensitivity detection, mask status |
| Dynamic masking | 7 | Create/drop policy, view existence, masked view data |
3. Database Create Tests (7 tests)
Located in test/test_database_create.sh — verifies pgclone.database_create() creates a new database, clones data into it, and supports idempotent re-cloning.
4. Async Tests (21 tests)
Located in test/test_async.sh — covers background worker operations (8 tests):
- TEST 1:
pgclone.table_async— basic async table clone - TEST 2:
pgclone.table_async— with different target name - TEST 3:
pgclone.schema_async— sequential mode - TEST 4:
pgclone.progress— check progress JSON - TEST 5:
pgclone.jobs— list all jobs - TEST 6:
pgclone.jobs_view— progress tracking view - TEST 7:
pgclone.clear_jobs— cleanup completed/failed jobs - TEST 8: Worker Pool — parallel schema clone with pool workers (v2.2.0)
5. Consistent-Snapshot Tests (v4.3.0)
Located in test/test_consistent.sh. Builds a parent/child schema with a foreign-key relationship, runs a background writer that continuously commits parent+child rows in separate transactions, then runs pgclone.schema() (sync), pgclone.schema_async() (parallel pool), and the {"consistent": false} opt-out path. Asserts that for every child row in the target there exists a matching parent row — without consistent-snapshot wrapping, copying tables one-at-a-time against a moving source would yield orphan FKs. The opt-out test confirms {"consistent": false} still functions and completes successfully.
6. Snapshot-Keeper Resilience Tests (v4.3.1, issue #9)
Located in test/test_snapshot_keeper.sh. Five groups, ~20 assertions, exercising every layer of the v4.3.1 and v4.3.2 fixes:
| Group | What it stresses | Assertions |
|---|---|---|
| 1 | SET LOCAL idle_in_transaction_session_timeout = 0 on keeper (sets idle_in_transaction_session_timeout = 1s on source role and runs a 5-table schema clone — deterministic regression for the original report) |
7 |
| 2 | SET LOCAL statement_timeout = 0 on keeper (sets statement_timeout = 1s on source role and runs a 2-table schema clone — confirms both keeper queries and importer COPYs are protected) |
4 |
| 3 | pgclone.database_create() keeper across the outer per-schema loop (two-schema source, tight idle_in_transaction_session_timeout, fresh target DB) |
6 |
| 4 | PQconninfoParse + PQconnectdbParams augmentation: both URI (postgresql://…) and keyword-form conninfo strings work; user-supplied keepalives_idle=120 keepalives_count=3 are preserved (no overwrite of explicit choices) |
3 |
| 5 (v4.3.2) | Async path (pgclone.schema_async()) under tight idle_in_transaction_session_timeout. Confirms the bgw mirror fix: bgw_connect_with_keepalives + SET LOCAL timeouts in bgw_begin_repeatable_read + bgw_keeper_ping in the pool coordinator. Skipped automatically when pgclone is not in shared_preload_libraries. |
5 |
Without the v4.3.1 fix, groups 1 and 3 would fail deterministically. Without the v4.3.2 fix, group 5 would fail deterministically on the same source-role condition.
7. Oracle Integration Test (43 checks)
Located in test/test_oracle.sh, seeded by test/fixtures/oracle_seed.sql. It needs a pgclone built with WITH_ORACLE=1 and a reachable Oracle database, so it is not part of the default ./test/run_all.sh run. It has its own Docker Compose setup (below), and CI executes it in the dedicated oracle job against an oracle-free service container. It covers:
dry_runoutput (DDL,PRIMARY KEY,UNIQUE, skippedBLOB) and that a dry run creates nothing- the Oracle → PostgreSQL column types and the PK / UNIQUE /
NOT NULLconstraints - value fidelity:
NUMBERprecision/scale,BINARY_DOUBLE, unicode, tabs/backslashes/newlines (COPY escaping), NULLs, a 100,000-characterCLOB,DATEwith time, microsecondTIMESTAMP,TIMESTAMP WITH TIME ZONE,RAW - a 20,000-row table (several COPY flushes)
include_data => false, an existing target (never overwritten), a missing Oracle table and a wrong password (no leftover table)
The type-mapping function pgclone.oracle_type_to_pg() and the “built without Oracle” error path are additionally covered by pgTAP group 29 in test/pgclone_test.sql, which runs in the normal PG 14–18 matrix.
Running it locally with Docker Compose
./test/run_all.sh oracle # PG 14, 17 and 18 against Oracle Free
./test/run_all.sh oracle 17 # a single PostgreSQL version
# or by hand
docker compose up -d oracle-db
docker compose run --rm test-oracle-pg17
docker compose down -v
Dockerfile.oracle builds PostgreSQL + Oracle Instant Client (Basic Light, SDK, SQL*Plus) and compiles pgclone with WITH_ORACLE=1. test/run_oracle_tests.sh (the container’s init script) waits for the oracle-db service (gvenzl/oracle-free), seeds it, runs test/test_oracle.sh and prints ALL TESTS PASSED on success, which run_all.sh detects. The seed is re-runnable.
Apple Silicon: the Oracle Free image is x86-64 only, so
oracle-dbruns under emulation (platform: linux/amd64) — enable Rosetta in Docker Desktop and expect a slow first start (the runner waits up to 15 minutes). The PostgreSQL test image builds natively; on arm64 it uses Oracle’s unversioned “latest” arm64 Instant Client, while x86-64 is pinned to 23.5 like CI.
Running it against your own Oracle
To run it yourself:
export ORACLE_CONNINFO="host=localhost port=1521 service=FREEPDB1 user=app password=apppass"
sqlplus app/apppass@//localhost:1521/FREEPDB1 @test/fixtures/oracle_seed.sql
PGHOST=localhost PGPORT=5434 PGUSER=postgres bash test/test_oracle.sh
8. SQL Server Integration Test (57 checks)
Located in test/test_sqlserver.sh, seeded by test/fixtures/sqlserver_seed.sql. It needs a pgclone built with WITH_SQLSERVER=1 and a reachable SQL Server, so it is not part of the default ./test/run_all.sh run. CI executes it in the dedicated sqlserver job against a SQL Server 2022 service container. It covers:
dry_runoutput (DDL,PRIMARY KEY,UNIQUE, skippedGEOGRAPHY) and that a dry run creates nothing- the SQL Server → PostgreSQL column types and the PK / UNIQUE /
NOT NULLconstraints - value fidelity:
DECIMAL,MONEY,TINYINT,BIT,REAL/FLOAT, unicode, tabs/backslashes/newlines (COPY escaping), NULLs, a 100,000-characterNVARCHAR(MAX),DATE,TIME(3),DATETIME, microsecondDATETIME2,DATETIMEOFFSET,UNIQUEIDENTIFIER,VARBINARY - a 20,000-row table (several COPY flushes)
include_data => false, an existing target (never overwritten), a missing table and a wrong password (no leftover table)
The type-mapping function pgclone.sqlserver_type_to_pg() and the “built without SQL Server” error path are additionally covered by pgTAP group 30 in test/pgclone_test.sql, which runs in the normal PG 14–18 matrix.
Running it locally with Docker Compose
./test/run_all.sh sqlserver # PG 14, 17 and 18 against SQL Server 2022
./test/run_all.sh sqlserver 17 # a single PostgreSQL version
# or by hand
docker compose up -d sqlserver-db
docker compose run --rm test-sqlserver-pg17
docker compose down -v
Dockerfile.sqlserver builds PostgreSQL + FreeTDS and compiles pgclone with WITH_SQLSERVER=1. test/run_sqlserver_tests.sh (the container’s init script) waits for the sqlserver-db service, seeds it with tsql, runs test/test_sqlserver.sh and prints ALL TESTS PASSED on success. The seed is re-runnable.
Apple Silicon: the SQL Server image is x86-64 only, so
sqlserver-dbruns under emulation (platform: linux/amd64) — enable Rosetta in Docker Desktop. It needs about 2 GB of RAM.
Running it against your own SQL Server
export SQLSERVER_CONNINFO="host=localhost port=1433 database=pgc_test user=sa password=..."
tsql -S localhost -p 1433 -U sa < test/fixtures/sqlserver_seed.sql
PGHOST=localhost PGPORT=5434 PGUSER=postgres bash test/test_sqlserver.sh
9. MySQL Integration Test (70 checks)
Located in test/test_mysql.sh, seeded by test/fixtures/mysql_seed.sql. It needs a pgclone built with WITH_MYSQL=1 and a reachable MySQL (or MariaDB) server, so it is not part of the default ./test/run_all.sh run. CI executes it in the dedicated mysql job against MySQL 8.0. It covers:
dry_runoutput (DDL,PRIMARY KEY,UNIQUE, skippedPOINT) and that a dry run creates nothing- the MySQL → PostgreSQL column types (unsigned widening,
TINYINT(1),BIT,ENUM/SET,JSON,YEAR,TIME,TIMESTAMP) and the PK / UNIQUE /NOT NULLconstraints - value fidelity: integer extremes (
INT UNSIGNED,BIGINT UNSIGNED),DECIMAL,FLOAT/DOUBLE, unicode, tabs/backslashes/newlines (COPY escaping), NULLs, a 100,000-characterLONGTEXT, negative fractionalTIME, microsecondDATETIME, UTCTIMESTAMP,VARBINARY/BLOB, and zero dates loading as NULL - a 20,000-row table (several COPY flushes)
include_data => false, an existing target (never overwritten), a missing table and a wrong password (no leftover table)
The type-mapping function pgclone.mysql_type_to_pg() and the “built without MySQL” error path are additionally covered by pgTAP group 31 in test/pgclone_test.sql, which runs in the normal PG 14–18 matrix.
Running it locally with Docker Compose
./test/run_all.sh mysql # PG 14, 17 and 18 against MySQL 8.0
./test/run_all.sh mysql 17 # a single PostgreSQL version
# or by hand
docker compose up -d mysql-db
docker compose run --rm test-mysql-pg17
docker compose down -v
Dockerfile.mysql builds PostgreSQL + MariaDB Connector/C and compiles pgclone with WITH_MYSQL=1. test/run_mysql_tests.sh (the container’s init script) waits for the mysql-db service, seeds it with the mariadb client, runs test/test_mysql.sh and prints ALL TESTS PASSED on success. The seed is re-runnable. Both images are multi-arch, so no emulation is needed on Apple Silicon.
Running it against your own MySQL
export MYSQL_CONNINFO="host=localhost port=3306 user=root password=... ssl=no"
mysql -uroot -p < test/fixtures/mysql_seed.sql
PGHOST=localhost PGPORT=5434 PGUSER=postgres bash test/test_mysql.sh
Running Tests Locally
Prerequisites
- Docker and Docker Compose installed
- Repository cloned locally
Run all tests on a specific PostgreSQL version
docker compose down -v
docker compose up source-db -d
docker compose run --rm test-pg17
Filter test output
docker compose run --rm test-pg17 2>&1 | grep -E "(ok|not ok|WARNING|ERROR|PASSED|FAILED)"
Run on all PostgreSQL versions
for pg in 14 15 16 17 18; do
echo "=== PostgreSQL $pg ==="
docker compose down -v
docker compose up source-db -d
docker compose run --rm test-pg$pg 2>&1 | tail -5
done
Clean up
docker compose down -v
Docker Test Architecture
Source Database (source-db)
- Uses
postgres:18image - Seeded with
test/fixtures/seed.sqlcontaining test schemas, tables, indexes, constraints, triggers, views, materialized views, functions, and sample data - Exposed via Docker Compose networking (no host port mapping needed)
Test Containers (test-pg14 through test-pg18)
Each test container:
- Builds from the project
Dockerfilewith aPG_VERSIONbuild arg - Installs build dependencies, pgTAP, and compiles pgclone
- Configures
shared_preload_libraries = 'pgclone'andmax_worker_processes = 32 - Runs
test/run_tests.shas an initdb script, which:- Waits for the source database to be ready
- Creates extensions (pgtap, pgclone)
- Sets the source connection info as a PostgreSQL GUC (
app.source_conninfo) - Runs pgTAP tests, database create tests, and async tests
- Reports pass/fail and exits
Why Docker Compose Instead of Host-Based CI?
The extension installs a .so file into PostgreSQL’s pkglibdir. When the target database runs inside a Docker container but the extension is built on the host, the container can’t find the .so. Docker Compose ensures the extension is built inside the same container that runs the target PostgreSQL, solving this path mismatch.
GitHub Actions CI
The CI pipeline (.github/workflows/ci.yml) runs on every push to main, fix/**, feature/**, and feat/** branches, and on PRs to main.
Matrix Strategy
Tests run in parallel across PostgreSQL 14, 15, 16, 17, and 18 with fail-fast: false — so a failure on one version doesn’t cancel the others.
Oracle job
A second job, oracle, runs on PostgreSQL 14 and 18. It starts the gvenzl/oracle-free service container, installs Oracle Instant Client (Basic Light + SDK + SQL*Plus), builds pgclone with WITH_ORACLE=1, seeds Oracle, starts a target PostgreSQL with libclntsh on its library path, and runs test/test_oracle.sh.
SQL Server job
A third job, sqlserver, runs on PostgreSQL 14 and 18. It starts the mcr.microsoft.com/mssql/server:2022-latest service container, installs FreeTDS (freetds-dev, freetds-bin), builds pgclone with WITH_SQLSERVER=1, seeds SQL Server with tsql, starts a target PostgreSQL, and runs test/test_sqlserver.sh.
MySQL job
A fourth job, mysql, runs on PostgreSQL 14 and 18. It starts MySQL 8.0 with docker run (so it can pass --default-authentication-plugin=mysql_native_password), installs libmariadb-dev and the MariaDB client, builds pgclone with WITH_MYSQL=1, seeds MySQL, starts a target PostgreSQL, and runs test/test_mysql.sh.
CI Steps
- Install PostgreSQL — from PGDG APT repository
- Build pgclone —
make PG_CONFIG=...with version-specificpg_config - Start target PostgreSQL — separate instance on port 5434 with
shared_preload_libraries = 'pgclone' - Seed source database — GitHub Actions service container on port 5433
- Install pgTAP — built against the same PG version
- Run sync tests — 66 pgTAP tests
- Run loopback-DDL tests — 21 shell tests for clone_roles, verify, masking_report, DDM
- Run database_create tests — creates/clones/verifies/cleans up
- Run async tests — background worker tests with polling
- Show logs on failure — dumps PostgreSQL server log for debugging
Service Container
The source database runs as a GitHub Actions service container (postgres:${{ matrix.pg_version }}), which provides a fresh source database for each matrix job.
Test Data
The test fixture (test/fixtures/seed.sql) creates:
- Schema:
test_schema - Tables:
customers(10 rows),orders(10 rows),order_items(10 rows) - Indexes: 5 indexes including expression index (
lower(name)) and DESC index - Constraints: PK on all tables, FK (
orders → customers,order_items → orders), UNIQUE (email), CHECK (score) - Triggers: 2 triggers with trigger functions
- Sequences:
invoice_seq - Views:
active_customers,order_summary - Materialized View:
customer_statswith unique index - Functions:
update_timestamp(),log_order_change(),get_customer_orders(int) - Public schema table:
simple_test(5 rows) for basic clone tests
Adding New Tests
pgTAP test
Add to test/pgclone_test.sql, increment the plan count:
SELECT plan(67); -- increment after adding your test
-- Your new test
SELECT lives_ok(
format('SELECT pgclone.table(%L, %L, %L, true)',
current_setting('app.source_conninfo'),
'public', 'new_table'),
'description of what this tests'
);
Important: Functions that use loopback DDL connections (CREATE/DROP/GRANT via libpq loopback) will deadlock inside pgTAP’s transaction. Add those tests to test/test_loopback.sh instead.
Loopback-DDL test
Add to test/test_loopback.sh for functions that run DDL via loopback:
RESULT=$(pg "SELECT pgclone.your_function(...);" || echo "ERROR")
run_test "description" "[ '$RESULT' != 'ERROR' ]"
Shell-based test
Create a new script in test/ and add it to test/run_tests.sh:
bash /build/pgclone/test/test_your_feature.sh 2>&1
YOUR_EXIT=$?
if [ $YOUR_EXIT -ne 0 ]; then
TEST_EXIT=1
fi