pgclone

Overview

preload for async/progress; RPM LLVM_BINPATH build fix retained in the 4.4.2 package.

Version

Build

You can build the RPM / DEB packages for pgclone using pig build:

pig build pkg pgclone         # build RPM / DEB packages

Install

You can install pgclone directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install pgclone;          # Install for current active PG version

pig ext install -y pgclone -v 18  # PG 18
pig ext install -y pgclone -v 17  # PG 17
pig ext install -y pgclone -v 16  # PG 16
pig ext install -y pgclone -v 15  # PG 15
pig ext install -y pgclone -v 14  # PG 14

dnf install -y pgclone_18       # PG 18
dnf install -y pgclone_17       # PG 17
dnf install -y pgclone_16       # PG 16
dnf install -y pgclone_15       # PG 15
dnf install -y pgclone_14       # PG 14

apt install -y postgresql-18-pgclone   # PG 18
apt install -y postgresql-17-pgclone   # PG 17
apt install -y postgresql-16-pgclone   # PG 16
apt install -y postgresql-15-pgclone   # PG 15
apt install -y postgresql-14-pgclone   # PG 14

Preload:

shared_preload_libraries = 'pgclone';

Create Extension:

CREATE EXTENSION pgclone;

Usage

Sources:

• pgclone v4.4.2 README
• pgclone v4.4.2 usage guide
• Async cloning guide
• pgclone v4.4.2 release notes

pgclone clones tables, schemas, functions, roles, or whole databases over a PostgreSQL connection. It also provides preflight checks, structural diffs, masking, consistent snapshots, and optional background jobs. Use it for controlled database copies, not as an unattended substitute for backup and recovery.

Create and Run a Clone

CREATE EXTENSION pgclone;
SELECT pgclone.version();

SELECT pgclone.table(
  'host=source.example dbname=app user=clone_user',
  'public',
  'customers',
  true
);

Schema and database entry points follow the same connection-first pattern:

SELECT pgclone.schema(
  'host=source.example dbname=app user=clone_user',
  'sales',
  true
);

SELECT pgclone.database(
  'host=source.example dbname=app user=clone_user',
  true
);

The main API includes pgclone.table, pgclone.schema, pgclone.functions, pgclone.database, and pgclone.database_create. The _ex variants expose explicit choices for indexes, constraints, and triggers.

Filter and Mask Data

JSON options can restrict columns and rows:

SELECT pgclone.table(
  'host=source.example dbname=app user=clone_user',
  'public',
  'users',
  true,
  'users_lite',
  '{"columns":["id","name","email"],"where":"active"}'
);

Version 4.4 adds schema- and database-level masks, table inclusion patterns, and exclude_tables. Mask expressions run in the source-side COPY query, so values that are successfully masked do not reach the target unmasked.

The 4.4.2 mask validator skips unsafe or incompatible masks: constant values that cannot cast to the column, NULL for NOT NULL columns, non-hash masks on unique or primary-key columns, and masks on foreign-key columns. A skipped mask leaves that column unmasked. Treat warnings as a failed privacy gate and inspect the result before distributing a clone.

Preflight, Diff, and Consistency

SELECT pgclone.preflight(
  'host=source.example dbname=app user=clone_user',
  'public'
)::jsonb;

SELECT pgclone.diff(
  'host=source.example dbname=app user=clone_user',
  'public'
)::jsonb;

preflight checks connectivity, versions, privileges, capacity, names, roles, extensions, and tablespaces. diff reports DDL differences without applying changes.

Schema and database clones use a shared exported snapshot by default so related tables are copied consistently. A long snapshot can delay source vacuum cleanup and WAL recycling. Set the consistent option to false only when accepting cross-table inconsistency is an explicit tradeoff.

Async Jobs

Async execution requires preload and a restart:

shared_preload_libraries = 'pgclone'

SELECT pgclone.schema_async(
  'host=source.example dbname=app user=clone_user',
  'sales',
  true,
  '{"parallel":4}'
);

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

pgclone also exposes progress_detail, resume, and clear_jobs for job administration. Size max_worker_processes for the requested parallelism.

Important Boundaries

• The upstream usage guide requires superuser privileges to install and use pgclone.
• Async schema/database/parallel paths do not honor masks, tables, or exclude_tables in v4.4.2. Use the documented synchronous path when those controls are a security requirement.
• Keep passwords out of stored SQL and logs; prefer libpq service files, passfiles, or another controlled credential mechanism.
• Version 4.4.2 improves sequence-state copying and protects PostgreSQL 17 source sessions from transaction_timeout, but callers must still validate object ownership, extensions, roles, large objects, and post-clone application behavior.