# PgClone Architecture This document describes the internal architecture of pgclone, covering the codebase structure, key design decisions, and PostgreSQL version compatibility. ## Codebase Structure ``` pgclone/ ├── src/ │ ├── pgclone.c # Main extension (~5100 lines) │ │ # - Table, schema, database clone functions │ │ # - DDL generation (indexes, constraints, triggers, views) │ │ # - COPY protocol data transfer │ │ # - Selective column / WHERE filter logic │ │ # - Data masking / anonymization engine │ │ # - Auto-discovery of sensitive data │ │ # - Static mask-in-place via UPDATE │ │ # - Dynamic masking policies via views │ │ # - Role cloning with permissions and passwords │ │ # - _PG_init(), shmem hooks, version function │ ├── pgclone_bgw.c # Background worker (~1000 lines) │ │ # - bgw_main entry point │ │ # - Async table/schema clone workers │ │ # - Worker pool (pgclone_pool_worker_main) │ │ # - Shared memory progress updates │ ├── pgclone_bgw.h # Shared definitions │ │ # - Job state struct, status enums │ │ # - Shared memory layout (pgclone_state) │ │ # - Pool queue struct (PgclonePoolQueue) │ │ # - MAX_JOBS, MAX_POOL_TASKS, progress fields │ ├── pgclone_diff.c # v4.1.0 — schema drift / DDL diff (~600 lines) │ │ # - pgclone_diff(): JSON drift report │ │ # - Read-only on both sides; isolated unit │ │ # - Self-contained connect helpers + JSON escaper │ └── pgclone_preflight.c # v4.2.0 — pre-flight validator (~700 lines) │ # - pgclone_preflight(): JSON readiness report │ # - Checks: connection, version, permissions, │ # capacity, name conflicts, missing │ # extensions/roles/tablespaces │ # - Read-only on both sides; isolated unit │ ├── pgclone_oracle.c # v4.5.0 — Oracle → PostgreSQL table clone │ │ # - pgclone_table_from_oracle(), pgclone_oracle_type_to_pg() │ │ # - Metadata (ALL_TAB_COLUMNS / ALL_CONSTRAINTS), │ │ # type mapping, DDL, COPY text-format loader │ ├── pgclone_oci.c # v4.5.0 — OCI wrapper; includes NO PostgreSQL headers │ └── pgclone_oci.h # - Plain-C API (ora_connect / ora_open / ora_fetch ...) │ # - Real OCI only with -DPGCLONE_WITH_ORACLE; else stubs │ ├── pgclone_sqlserver.c # v4.6.0 — SQL Server → PostgreSQL table clone │ │ # - pgclone_table_from_sqlserver(), pgclone_sqlserver_type_to_pg() │ │ # - Metadata (sys.columns / sys.indexes), │ │ # type mapping, DDL, COPY text-format loader │ ├── pgclone_tds.c # v4.6.0 — FreeTDS wrapper; includes NO PostgreSQL headers │ └── pgclone_tds.h # - Plain-C API (tds_connect / tds_open / tds_fetch ...) │ # - Real FreeTDS only with -DPGCLONE_WITH_SQLSERVER; else stubs │ ├── pgclone_mysql.c # v4.7.0 — MySQL / MariaDB → PostgreSQL table clone │ │ # - pgclone_table_from_mysql(), pgclone_mysql_type_to_pg() │ │ # - Metadata (information_schema), type mapping, DDL, │ │ # COPY text-format loader │ ├── pgclone_myc.c # v4.7.0 — MySQL client wrapper; includes NO PostgreSQL headers │ └── pgclone_myc.h # - Plain-C API (myc_connect / myc_open / myc_fetch ...) │ # - Real client only with -DPGCLONE_WITH_MYSQL; else stubs ├── sql/ │ └── pgclone--X.Y.Z.sql # SQL function definitions per version ├── test/ │ ├── fixtures/seed.sql # Test data │ ├── fixtures/oracle_seed.sql # Oracle-side seed for test_oracle.sh │ ├── fixtures/sqlserver_seed.sql # SQL Server-side seed for test_sqlserver.sh │ ├── fixtures/mysql_seed.sql # MySQL-side seed for test_mysql.sh │ ├── pgclone_test.sql # 66 pgTAP tests (groups 1–20) │ ├── test_loopback.sh # 21 loopback-DDL tests (roles, verify, report, DDM) │ ├── run_tests.sh # Test orchestrator │ ├── run_all.sh # Multi-version runner │ ├── test_async.sh # Async test suite (21 tests incl. worker pool) │ ├── test_oracle.sh # Oracle integration test (needs Oracle; run by CI job `oracle`) │ ├── test_sqlserver.sh # SQL Server integration test (needs SQL Server; run by CI job `sqlserver`) │ ├── test_mysql.sh # MySQL integration test (needs MySQL; run by CI job `mysql`) │ └── test_database_create.sh ├── .github/workflows/ci.yml # GitHub Actions CI (PG 14–18 matrix) ├── Dockerfile # Multi-version build container ├── docker-compose.yml # Source + test containers (PG 14–18) ├── Makefile # PGXS-based build ├── pgclone.control # Extension metadata ├── META.json # PGXN metadata ├── pre_deploy_checks.sh # Pre-release validation (22 checks) ├── CHANGELOG.md # Version history ├── CONTRIBUTING.md # Contributor guide └── SECURITY.md # Security policy ``` ## Core Design Decisions ### Why libpq Instead of SPI? pgclone uses **loopback libpq connections** to the local target database for all DDL operations instead of PostgreSQL's SPI (Server Programming Interface). The reason: SPI executes within the calling transaction's snapshot, so DDL statements like `CREATE TABLE` aren't visible to subsequent SPI calls within the same function invocation until the transaction commits. By connecting via libpq (even to `localhost`), each DDL statement executes in its own transaction and is immediately visible. ### Why C Instead of PL/pgSQL? - Direct access to the COPY protocol via `PQgetCopyData` / `PQputCopyData` for high-throughput data transfer - Background worker registration requires C (`RegisterDynamicBackgroundWorker`) - Shared memory allocation for progress tracking requires C hooks - Fine-grained error handling and resource cleanup with `PG_TRY` / `PG_CATCH` ### Isolated Translation Units for Read-Only Features (v4.1.0+) Read-only feature additions live in their own `.c` files and do **not** share helpers with `src/pgclone.c`: - `src/pgclone_diff.c` (v4.1.0) — `pgclone.diff()` - `src/pgclone_preflight.c` (v4.2.0) — `pgclone.preflight()` Each such file: 1. **Re-implements** the connect / READ ONLY / `quote_literal_cstr` / JSON escape helpers it needs. Duplication is intentional: a bug or refactor in `pgclone.c` cannot regress these features, and the reverse is also true. 2. **Wraps both source and local connections** in `BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY` and ends with `ROLLBACK`. The function never executes DDL or DML. 3. **Returns a single JSON document** assembled with `StringInfo` appends and a local RFC 8259 string escaper (`pgd_escape_json` / `pf_escape_json`) so the feature has zero dependency on `utils/jsonapi.h` (whose location varies across PG versions). When adding the next read-only feature (e.g. `pgclone.table_sample()` in v4.3.0), follow the same pattern: a new `src/pgclone_.c`, appended to `OBJS` in the Makefile, with its SQL function declared in both the per-version full-install file and the upgrade script. ### Consistent-snapshot clones (v4.3.0+) Every clone wraps source-side reads in a `BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY` transaction. For multi-connection operations — schema clones (per-table sub-calls plus FK retry / views / matviews / functions / triggers passes), database clones, and parallel pool mode — one keeper connection exports a snapshot via `pg_export_snapshot()` and every other source connection imports it via `SET TRANSACTION SNAPSHOT`. This is the correctness model `pg_dump -j` uses for parallel-dump consistency. Three call patterns: 1. **Single-connection sync (`pgclone.table()`)** — the source connection BEGINs at REPEATABLE READ READ ONLY for the function body, COMMITs at the end. No snapshot export needed because nothing else opens its own source connection. 2. **Multi-connection sync (`pgclone.schema()`, `pgclone.database()`)** — the initial source connection becomes the snapshot keeper. It exports a snapshot ID (or imports one passed via JSON options when nested under another consistent op) and stays open for the full duration of the clone. Every sub-DirectFunctionCall and every later libpq connection (FK retry, views, matviews, functions, triggers) receives the snapshot ID through the options JSON and `SET TRANSACTION SNAPSHOT` on its own source connection. 3. **Parallel pool async (`pgclone.schema_async(... '{"parallel": N}')`)** — a dedicated `pgclone_pool_coordinator_main` background worker is launched first. It opens its own source connection, BEGINs, calls `pg_export_snapshot()`, publishes the ID to shared memory (`PgclonePoolQueue.snapshot_id`) and sets `snapshot_ready = true`. The N pool workers wait on a latch for the flag, import the snapshot, and bump `snapshot_imported_count`. The coordinator COMMITs and exits once `imported_count == snapshot_expected_workers && launch_complete` — at which point every importer's own transaction independently owns the snapshot. Failure handling: if any pool worker fails to import, it sets `pool.snapshot_failed = true`; the coordinator and every other worker that hasn't yet bound abort cleanly. If the coordinator fails before publishing, workers time out (~60s wait) and report failure. The coordinator caps its hold at ~10 minutes regardless. Opt-out: `'{"consistent": false}'` in any options-JSON skips all of the above and runs with v4.2.x semantics. The keeper connection is then closed immediately after the table-list read like before, and the pool coordinator is not launched. ### Snapshot-keeper resilience (v4.3.1, issue #9) The keeper transaction sits idle-in-transaction for the bulk of a long clone. Three independent paths can silently terminate it and invalidate the exported snapshot: 1. **Firewall / NAT idle TCP drop** on remote source connections. pgclone injects `keepalives=1 keepalives_idle=30 keepalives_interval=10 keepalives_count=6` into every source conninfo (unless the user already set them), giving ~90s drop detection and keeping perimeter NAT entries warm. Done at the libpq layer via `PQconninfoParse` + `PQconnectdbParams`, so both URI (`postgresql://...`) and keyword-form conninfo strings are handled. 2. **`idle_in_transaction_session_timeout`** on the source server. The keeper's BEGIN issues `SET LOCAL idle_in_transaction_session_timeout = 0` and `SET LOCAL statement_timeout = 0`; both GUCs are `PGC_USERSET` (no privilege required) and `SET LOCAL` reverts at COMMIT, so the settings never leak into pooled connections. 3. **Silent post-drop continuation.** Between major sub-phases of `pgclone.schema()` (per-table loop, FK retry, views, functions, triggers) and `pgclone.database()` (per-schema loop) pgclone runs a cheap `SELECT 1` ping on the keeper. A failure produces a clear error naming the keeper rather than the misleading `invalid snapshot identifier` that PostgreSQL emits when the exported-snapshot file has been reaped. Operators who explicitly want to bypass snapshot sharing — for example, on an unreliable link where one of the above is guaranteed to fire — can pass `'{"consistent": false}'` in the options JSON. Each importer then begins its own REPEATABLE READ transaction independently. Cross-table referential consistency is no longer guaranteed, but every per-table copy is still internally consistent. ### COPY Protocol Data Transfer Data is transferred using PostgreSQL's COPY protocol, which is significantly faster than row-by-row INSERT: 1. Open a `COPY ... TO STDOUT` on the source connection 2. Open a `COPY ... FROM STDIN` on the target connection 3. Stream data between them in chunks via `PQgetCopyData` / `PQputCopyData` 4. Finalize with `PQputCopyEnd` This avoids parsing and re-serializing individual rows. ### Data Masking Architecture (v3.0.0) Data masking is implemented as **server-side SQL expressions** injected into the COPY query's SELECT list. Instead of `COPY table TO STDOUT`, pgclone generates: ```sql COPY (SELECT id, CASE WHEN email IS NULL THEN NULL WHEN position('@' in email::text) > 0 THEN left(email::text, 1) || '***@' || split_part(email::text, '@', 2) ELSE '***' END AS email, 'XXXX' AS full_name, NULL AS ssn FROM schema.table) TO STDOUT ``` This approach has three key benefits: 1. **Zero overhead** — masking happens inside PostgreSQL's query executor on the source, data flows through COPY already masked. No row-by-row C processing. 2. **No dependencies** — all mask expressions use built-in PostgreSQL functions (`left()`, `right()`, `md5()`, `split_part()`, `random()`). No pgcrypto or external libraries. 3. **Composable** — masks work with existing `columns`, `where`, `indexes`, `constraints`, and `triggers` options without special handling. When masks are specified without an explicit column list, pgclone queries `pg_catalog.pg_attribute` on the source to discover column names, then applies mask expressions to matching columns while passing others through unmodified. --- ### Oracle → PostgreSQL cloning (v4.5.0) `pgclone.table_from_oracle()` reads Oracle through OCI and loads PostgreSQL over the same loopback libpq connection the other clone paths use. - **OCI isolation.** `oci.h` and `postgres.h` both typedef `text`, so they cannot share a translation unit. All OCI calls live in `pgclone_oci.c`, behind the PostgreSQL-free interface in `pgclone_oci.h`; `pgclone_oracle.c` only sees that interface. Without `-DPGCLONE_WITH_ORACLE` the OCI file compiles to stubs that report "built without Oracle support", so the default build has no Oracle dependency at all. - **Metadata.** Columns come from `ALL_TAB_COLUMNS` (which already omits hidden columns), constraints from `ALL_CONSTRAINTS` / `ALL_CONS_COLUMNS` (enabled `P` and `U` only). `map_oracle_type()` turns each column into a PostgreSQL type; unsupported types are skipped with a WARNING and any constraint touching them is dropped with a NOTICE. - **Data path.** Every value is fetched as text (numbers and datetimes through explicit `TO_CHAR` formats, with `NLS_NUMERIC_CHARACTERS='.,'` and the session time zone forced to UTC so output is locale-independent), `RAW` as bytes, and `CLOB`/`NCLOB` through a LOB locator read with streaming `OCILobRead2` (a plain character define silently truncates at 64 KB because OCI reports lengths in 16 bits). Rows are escaped into COPY text format and streamed with `PQputCopyData` in ~64 KB batches. - **Atomicity and consistency.** The Oracle side runs under `SET TRANSACTION READ ONLY` (one consistent snapshot). The PostgreSQL side does `BEGIN`, `CREATE TABLE`, `COPY`, `COMMIT`, with a `ROLLBACK` in the error path, so a failure leaves no partial table. An existing target is never overwritten. --- ### SQL Server → PostgreSQL cloning (v4.6.0) `pgclone.table_from_sqlserver()` mirrors the Oracle path: SQL Server is read through FreeTDS and PostgreSQL is loaded over the loopback libpq connection. - **FreeTDS isolation.** `sybdb.h` and `postgres.h` both define `BOOL`, `TRUE` and `FALSE`, so they cannot share a translation unit. All DB-Library calls live in `pgclone_tds.c`, behind the PostgreSQL-free interface in `pgclone_tds.h`; `pgclone_sqlserver.c` only sees that interface. Without `-DPGCLONE_WITH_SQLSERVER` the file compiles to stubs that report "built without SQL Server support". DB-Library reports errors through process-global callbacks; the wrapper records the last message in a static buffer (safe: a backend is single-threaded). - **Metadata.** Columns come from `sys.columns` / `sys.types` (base type via `TYPE_NAME(system_type_id)`, so alias types such as `sysname` resolve), constraints from `sys.indexes` / `sys.index_columns` (enabled primary keys and unique constraints, key columns only). `map_sqlserver_type()` maps each column; unsupported types are skipped with a WARNING and constraints touching them are dropped with a NOTICE. - **Data path.** Every column is converted to text on the server with fixed, locale-independent `CONVERT` styles (23 / 121 / 127 for dates and times, 3 for floats, 2 for money), text columns go through `NVARCHAR(MAX)` so the server handles code-page conversion and FreeTDS delivers UTF-8, and binary columns come back as hex digits. Rows are escaped into COPY text format and streamed with `PQputCopyData` in ~64 KB batches. - **Atomicity and consistency.** When the source database has `ALLOW_SNAPSHOT_ISOLATION` on, the copy runs inside a `SNAPSHOT` transaction; otherwise a NOTICE is raised and it reads at READ COMMITTED. The PostgreSQL side does `BEGIN`, `CREATE TABLE`, `COPY`, `COMMIT`, with a `ROLLBACK` in the error path, so a failure leaves no partial table. An existing target is never overwritten. --- ### MySQL / MariaDB → PostgreSQL cloning (v4.7.0) `pgclone.table_from_mysql()` follows the same shape as the Oracle and SQL Server paths: the source is read through its C client API and PostgreSQL is loaded over the loopback libpq connection. - **Client isolation.** `mysql.h` is kept out of the PostgreSQL translation units (conflicting typedefs, and its `mysql_*` names are easy to confuse with ours). All client calls live in `pgclone_myc.c` behind the `myc_*` interface in `pgclone_myc.h`. Without `-DPGCLONE_WITH_MYSQL` the file compiles to stubs that report "built without MySQL support". The same code builds against libmysqlclient and MariaDB Connector/C; the one divergence (TLS option names) is selected with `MARIADB_PACKAGE_VERSION`. - **Streaming.** Rows are read with `mysql_use_result()` (unbuffered), so memory stays flat for large tables. The wrapper embeds the `MYSQL` handle so that, on an error path, `myc_disconnect()` can drop the connection first and free the half-read result afterwards, instead of reading the rest of the table just to discard it. - **Metadata.** Columns come from `information_schema.columns` (type, `column_type` for `unsigned`, precisions, nullability), unique keys from `information_schema.statistics` (`PRIMARY` becomes the primary key). Indexes over a skipped column, a column prefix or an expression are dropped with a NOTICE. - **Data path.** The session is pinned to `time_zone = '+00:00'` and `sql_mode = ''`, so `TIMESTAMP` values are UTC (loaded as `timestamptz`) and backslash escapes behave predictably. Binary columns are selected as `HEX()`, `BIT(n)` as a zero-padded bit string; zero and partial-zero dates, which PostgreSQL rejects, are loaded as NULL and counted in a NOTICE. Rows are escaped into COPY text format and streamed with `PQputCopyData` in ~64 KB batches. - **Atomicity and consistency.** The MySQL side runs under `START TRANSACTION WITH CONSISTENT SNAPSHOT` at `REPEATABLE READ` (a consistent InnoDB snapshot). The PostgreSQL side does `BEGIN`, `CREATE TABLE`, `COPY`, `COMMIT`, with a `ROLLBACK` in the error path, so a failure leaves no partial table. An existing target is never overwritten. --- ## Shared Memory Architecture Async operations use PostgreSQL shared memory to track job progress: ```c typedef struct PgcloneJobState { int job_id; int status; // PENDING, RUNNING, COMPLETED, FAILED, CANCELLED char schema_name[NAMEDATALEN]; char table_name[NAMEDATALEN]; char current_table[NAMEDATALEN]; int tables_total; int tables_completed; int64 rows_copied; int64 start_time_ms; int64 elapsed_ms; char error_message[256]; // ... more fields } PgcloneJobState; typedef struct PgcloneSharedState { LWLock *lock; int num_jobs; PgcloneJobState jobs[MAX_JOBS]; } PgcloneSharedState; ``` - Allocated once during `_PG_init()` via shared memory hooks - Protected by a lightweight lock (`LWLock`) for concurrent access - Read by `pgclone.progress()`, `pgclone.jobs()`, and `pgclone.jobs_view` - Written by background workers as they progress --- ## PostgreSQL Version Compatibility pgclone uses C preprocessor guards to maintain compatibility across PG 14–18: ### Shared Memory Request (PG 15+) PostgreSQL 15 introduced `shmem_request_hook` — shared memory must be requested during this hook, not directly in `_PG_init()`: ```c #if PG_VERSION_NUM >= 150000 static shmem_request_hook_type prev_shmem_request_hook = NULL; static void pgclone_shmem_request(void) { if (prev_shmem_request_hook) prev_shmem_request_hook(); RequestAddinShmemSpace(sizeof(PgcloneSharedState)); RequestNamedLWLockTranche("pgclone", 1); } #endif ``` In `_PG_init()`: ```c #if PG_VERSION_NUM >= 150000 prev_shmem_request_hook = shmem_request_hook; shmem_request_hook = pgclone_shmem_request; #else RequestAddinShmemSpace(sizeof(PgcloneSharedState)); RequestNamedLWLockTranche("pgclone", 1); #endif ``` ### Signal Handler (PG 17+) PostgreSQL 17 removed the `die` signal handler, replacing it with `SignalHandlerForShutdownRequest`: ```c #if PG_VERSION_NUM >= 170000 #include "postmaster/interrupt.h" pqsignal(SIGTERM, SignalHandlerForShutdownRequest); #else pqsignal(SIGTERM, die); #endif ``` ### Other Version-Specific Guards - `d.adsrc` was removed from `pg_attrdef` in PG 12+ — pgclone uses `pg_get_expr()` instead - `strlcpy` vs `strncpy` for safe string copy across versions - SQL return type consistency across version-specific `.sql` files --- ## Background Worker Lifecycle 1. **Registration:** `pgclone.table_async()` or `pgclone.schema_async()` allocates a job slot in shared memory, populates connection info and parameters, then calls `RegisterDynamicBackgroundWorker()`. 2. **Startup:** The worker process starts via `pgclone_bgw_main()`, which: - Sets up signal handlers - Connects to both source and target databases via libpq - Updates job status to RUNNING 3. **Execution:** The worker calls the same core clone functions used by sync operations, with periodic updates to shared memory (rows copied, current table, elapsed time). 4. **Worker Pool mode (v2.2.0):** For `pgclone.schema_async` with `"parallel": N`, the parent process: - Queries the source for the list of tables - Populates a shared-memory task queue (`PgclonePoolQueue`) - Launches exactly N pool workers via `pgclone_pool_worker_main()` - Each worker grabs the next unclaimed task from the queue, clones it, then grabs the next — until the queue is empty - Dynamic load balancing: faster workers automatically handle more tables - Resource usage is O(N) instead of O(tables) for bgworkers and DB connections 5. **Completion:** Worker sets status to COMPLETED or FAILED, disconnects from databases, and exits. --- ## Local Loopback Connections (v2.1.4+) pgclone uses loopback libpq connections for DDL execution on the target database. Since v2.1.4, these connections prefer Unix domain sockets (read from the `unix_socket_directories` GUC) over TCP `127.0.0.1`. This means the default `local all all peer` line in `pg_hba.conf` is sufficient — no `trust` entry is needed. If Unix sockets are unavailable, pgclone falls back to TCP `127.0.0.1` automatically. --- ## Resource Management pgclone carefully manages resources to avoid leaks: - Every `PQconnectdb()` has a matching `PQfinish()` in all code paths (including error paths) - Every `PQexec()` result is freed with `PQclear()` - `PG_TRY / PG_CATCH` blocks ensure cleanup on errors - Background workers disconnect from both source and target databases before exiting - COPY pipeline errors consume remaining results to prevent connection state corruption --- ## Build System pgclone uses PostgreSQL's PGXS build system: ```makefile MODULES = pgclone EXTENSION = pgclone DATA = sql/pgclone--*.sql PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) ``` This integrates with `pg_config` to find the correct include paths, library directories, and installation locations for the target PostgreSQL version. ### Optional Oracle support `pgclone_oracle.o` and `pgclone_oci.o` are always built. Passing `WITH_ORACLE=1` additionally defines `PGCLONE_WITH_ORACLE`, adds `$(ORACLE_HOME)/sdk/include` to the include path (through `PG_CPPFLAGS`, which PGXS folds into `CPPFLAGS` — a later target-specific append would be ignored) and links `-lclntsh` from `$(ORACLE_LIB)` (default `$(ORACLE_HOME)`): ```bash make WITH_ORACLE=1 ORACLE_HOME=/opt/oracle/instantclient_23_5 ```