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_<name>.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:

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:

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():

#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():

#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:

#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:

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)):

make WITH_ORACLE=1 ORACLE_HOME=/opt/oracle/instantclient_23_5