pgclone

pgclone : Clone PostgreSQL databases, schemas, tables, and functions across environments

Overview

Packages

Source

pig build pkg pgclone;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pgclone;		# install via package name, for the active PG version

pig install pgclone -v 18;   # install for PG 18
pig install pgclone -v 17;   # install for PG 17
pig install pgclone -v 16;   # install for PG 16
pig install pgclone -v 15;   # install for PG 15
pig install pgclone -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pgclone';

Create this extension with:

CREATE EXTENSION pgclone;

Usage

Source: README, Usage guide, Async guide, Release v4.3.2, changelog, SQL install script

pgclone clones tables, schemas, functions, roles, and whole databases directly from SQL. In v4.x the public API is namespaced under the pgclone schema; upstream and Pigsty currently track PostgreSQL 14-18.

Core clone functions

CREATE EXTENSION pgclone;
SELECT pgclone.version();

SELECT pgclone.table(
  'host=source-server dbname=mydb user=postgres password=secret',
  'public',
  'customers',
  true
);

SELECT pgclone.schema(
  'host=source-server dbname=mydb user=postgres password=secret',
  'sales',
  true
);

SELECT pgclone.database(
  'host=source-server dbname=mydb user=postgres password=secret',
  true
);

• pgclone.table(...), pgclone.schema(...), pgclone.functions(...), pgclone.database(...)
• pgclone.database_create(...) creates a local target database and clones into it.
• _ex variants expose explicit booleans for indexes, constraints, and triggers.

Options and masking

• JSON options support columns, where, conflict, and object toggles such as indexes, constraints, and triggers.
• JSON options also include consistent; it defaults to cross-table consistent snapshots in v4.3.0+ and can be disabled per call with {"consistent": false}.
• Upstream documents masking, auto-discovery of sensitive columns, static masking, dynamic masking, clone verification, and GDPR/compliance reporting in the usage guide.

SELECT pgclone.table(
  'host=source-server dbname=mydb user=postgres',
  'public', 'users', true, 'users_lite',
  '{"columns":["id","name","email"],"where":"status = ''active''"}'
);

Consistency, diff, and preflight

SELECT pgclone.diff(
  'host=source-server dbname=prod user=postgres',
  'app_schema'
)::jsonb;

SELECT pgclone.preflight(
  'host=source-server dbname=prod user=postgres',
  'app_schema'
)::jsonb;

• pgclone.diff(conninfo, schema) reports read-only DDL drift for tables, columns, indexes, constraints, triggers, views, and sequences.
• pgclone.preflight(conninfo, schema) checks source and target readiness before a clone, including connection, version, permission, capacity, naming-conflict, missing-role, missing-extension, and tablespace issues.
• v4.3.0+ clones read the source under REPEATABLE READ READ ONLY by default. Multi-connection schema, database, and parallel-pool clones share one exported snapshot, preserving parent/child consistency while a live source is taking writes.
• Long clones hold a source transaction open, which can delay vacuum cleanup and WAL recycling; use {"consistent": false} when that tradeoff matters more than cross-table consistency.

Async and progress

-- postgresql.conf
shared_preload_libraries = 'pgclone'

SELECT pgclone.schema_async(
  'host=source-server dbname=mydb user=postgres',
  'sales', true, '{"parallel":4}'
);

SELECT * FROM pgclone.jobs_view;
SELECT pgclone.progress(1);
SELECT pgclone.cancel(1);

• pgclone.table_async(...) and pgclone.schema_async(...) run in background workers.
• pgclone.jobs_view, pgclone.progress_detail(), pgclone.resume(), and pgclone.clear_jobs() provide job tracking and recovery.
• v4.3.2 ports the snapshot-keeper resilience fixes to async/background-worker paths, including keepalive injection and timeout protection for networked source connections.

Caveats

• Upstream requires PostgreSQL 14+.
• The usage guide states the extension requires superuser privileges to install and use.
• Async features need shared_preload_libraries = 'pgclone'; worker-pool parallelism also depends on max_worker_processes.
• Consistent async clones may still be opted out with {"consistent": false} if a source-side snapshot issue must be bypassed.