Extensions
- pgclone 4.7.0
- Clone PostgreSQL databases, schemas, and tables across environments
Documentation
- ARCHITECTURE
- PgClone Architecture
- TESTING
- PgClone Testing
- CONTRIBUTING
- Contributing to PgClone
- PULL_REQUEST_TEMPLATE
- PULL_REQUEST_TEMPLATE
- SECURITY
- Security Policy
- ASYNC
- PgClone Async Operations
- TESTING_MANUAL
- PgClone Manual Testing Guide
- USAGE
- PgClone Usage Guide
- CHANGELOG
- Changelog
README
PgClone
A PostgreSQL extension that clones databases, schemas, tables, and functions between PostgreSQL instances — directly from SQL. No pg_dump, no pg_restore, no shell scripts. Since v4.5.0 it can also pull a table straight out of an Oracle database, since v4.6.0 out of Microsoft SQL Server, and since v4.7.0 out of MySQL / MariaDB, into PostgreSQL.
Key Features
- Clone anything - tables, schemas, functions, entire databases, roles and permissions
- Oracle → PostgreSQL - clone an Oracle table (structure, NOT NULL, PK/UNIQUE, data) with automatic type mapping, a
dry_runDDL preview and an all-or-nothing transaction (optional OCI build) - SQL Server → PostgreSQL - clone a SQL Server table or view (structure, NOT NULL, PK/UNIQUE, data) with automatic type mapping, a
dry_runDDL preview and an all-or-nothing transaction (optional FreeTDS build) - MySQL / MariaDB → PostgreSQL - clone a MySQL table or view (structure, NOT NULL, PK/UNIQUE, data) with automatic type mapping, a consistent InnoDB snapshot, a
dry_runDDL preview and an all-or-nothing transaction (optional MySQL client build) - Fast data transfer - uses PostgreSQL COPY protocol
- Data masking - anonymize sensitive columns during cloning (email, name, phone, hash, partial, null, random, constant)
- Auto-discovery - scan source schemas for sensitive columns and get suggested mask rules
- Static & dynamic masking — mask existing tables in-place, or create role-based masked views
- Async operations - background workers with progress tracking and visual progress bar
- Parallel cloning - multiple background workers for concurrent table cloning
- Selective cloning - filter columns and rows with
columnsandWHEREoptions - Full DDL support - indexes, constraints (PK, UNIQUE, CHECK, FK, EXCLUDE), triggers, views, materialized views, sequences
- Conflict resolution - error, skip, replace, or rename strategies
- Cross-version - tested on PostgreSQL 14, 15, 16, 17, and 18
Quick Start
# Build and install
git clone https://github.com/valehdba/pgclone.git
cd pgclone
make
sudo make install
-- Enable the extension
CREATE EXTENSION pgclone;
-- Clone a table
SELECT pgclone.table(
'host=source-server dbname=mydb user=postgres password=secret',
'public', 'customers', true
);
-- Clone an entire schema
SELECT pgclone.schema(
'host=source-server dbname=mydb user=postgres password=secret',
'sales', true
);
-- Clone a full database
SELECT pgclone.database(
'host=source-server dbname=mydb user=postgres password=secret',
true
);
-- Clone a table from Oracle (needs the optional Oracle build, see Installation)
SELECT pgclone.table_from_oracle(
'host=ora-server port=1521 service=ORCLPDB1 user=scott password=tiger',
'HR', 'EMPLOYEES'
);
-- Clone a table from SQL Server (needs the optional FreeTDS build, see Installation)
SELECT pgclone.table_from_sqlserver(
'host=mssql-server port=1433 database=Sales user=sa password=secret',
'dbo', 'Customers'
);
-- Clone a table from MySQL / MariaDB (needs the optional MySQL client build, see Installation)
SELECT pgclone.table_from_mysql(
'host=mysql-server port=3306 user=root password=secret',
'shop', 'orders'
);
Requirements
- PostgreSQL 14 or later
- PostgreSQL development headers (
postgresql-server-dev-XX) libpqdevelopment library (libpq-dev)- GCC or compatible C compiler
- Optional, for Oracle cloning: Oracle Instant Client Basic/Basic Light + SDK (provides
oci.handlibclntsh) - Optional, for SQL Server cloning: FreeTDS development files (
freetds-dev/freetds-devel/brew install freetds; providessybdb.handlibsybdb) - Optional, for MySQL / MariaDB cloning: a MySQL C client library with headers (
libmariadb-dev/mariadb-connector-c-devel/brew install mariadb-connector-c, or libmysqlclient)
Installation
Debian / Ubuntu
sudo apt-get install postgresql-server-dev-18 libpq-dev build-essential
RHEL / CentOS / Rocky / AlmaLinux
sudo dnf install postgresql18-devel libpq-devel gcc make
macOS (Homebrew)
brew install postgresql@18
Build
make
sudo make install
# For a specific PostgreSQL version:
make PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
sudo make install PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
Build with Oracle support (optional)
pgclone.table_from_oracle() talks to Oracle through OCI, so it is compiled in only on request. A normal build is unaffected and the function then raises a clear “built without Oracle support” error.
make WITH_ORACLE=1 ORACLE_HOME=/opt/oracle/instantclient_23_5
sudo make install WITH_ORACLE=1 ORACLE_HOME=/opt/oracle/instantclient_23_5
The PostgreSQL server process must be able to load libclntsh at runtime (LD_LIBRARY_PATH / DYLD_LIBRARY_PATH or ldconfig). See Cloning a Table from Oracle for usage, type mapping and limitations.
Build with SQL Server support (optional)
pgclone.table_from_sqlserver() talks to SQL Server through FreeTDS (DB-Library), so it is compiled in only on request. A normal build is unaffected and the function then raises a clear “built without SQL Server support” error.
# Debian/Ubuntu: sudo apt-get install freetds-dev macOS: brew install freetds
make WITH_SQLSERVER=1
sudo make install WITH_SQLSERVER=1
# FreeTDS in a non-standard prefix (e.g. Homebrew on Apple Silicon)
make WITH_SQLSERVER=1 FREETDS_HOME=/opt/homebrew
See Cloning a Table from SQL Server for usage, type mapping and limitations.
Build with MySQL support (optional)
pgclone.table_from_mysql() talks to MySQL / MariaDB through the standard C client API, so it is compiled in only on request. A normal build is unaffected and the function then raises a clear “built without MySQL support” error.
# Debian/Ubuntu: sudo apt-get install libmariadb-dev macOS: brew install mariadb-connector-c
make WITH_MYSQL=1
sudo make install WITH_MYSQL=1
# pick a specific client library
make WITH_MYSQL=1 MYSQL_CONFIG=/opt/homebrew/opt/mariadb-connector-c/bin/mariadb_config
See Cloning a Table from MySQL for usage, type mapping and limitations.
Troubleshooting: make install fails with llvm-lto: No such file or directory
If PostgreSQL was itself built --with-llvm (check pg_config --configure | grep llvm), PGXS also builds a JIT bitcode file for every extension and links it with llvm-lto. Some LLVM packages (seen on RHEL/CentOS with a separately-installed llvm-toolset, e.g. llvm20) don’t ship that specific tool, so the build fails like:
/bin/sh: /usr/lib64/llvm20/bin/llvm-lto: No such file or directory
make: *** [.../pgxs.mk:241: install] Error 127
This is an LLVM/PGXS toolchain mismatch, not a pgclone issue — bitcode is only used for JIT-inlining expressions across a function’s internals, which has no meaningful benefit for an I/O-bound cloning extension. Skip it with with_llvm=no:
make with_llvm=no
sudo make with_llvm=no install
Enable
CREATE EXTENSION pgclone;
SELECT pgclone.version();
For async operations, add to postgresql.conf and restart:
shared_preload_libraries = 'pgclone'
pgclone uses Unix domain sockets for local loopback connections, so the default local all all peer line in pg_hba.conf is sufficient — no trust entry needed. If Unix sockets are unavailable, pgclone falls back to TCP 127.0.0.1 and appropriate pg_hba.conf configuration is required.
Documentation
| Document | Description |
|---|---|
| Usage Guide | All functions, parameters, data masking, Oracle, SQL Server and MySQL cloning, and examples |
| Async Operations | Background workers, progress tracking, parallel cloning |
| Testing | Test infrastructure, pgTAP and integration suites (incl. Oracle Free, SQL Server and MySQL), Docker, CI/CD |
| Manual Testing | Step-by-step manual test scenarios for all functionalities |
| Architecture | Codebase structure, design decisions, Oracle/OCI, SQL Server/FreeTDS and MySQL client isolation, PG version compatibility |
| Changelog | Version history and release notes |
Roadmap
- [x] v0.1.0: Clone tables, schemas, functions, databases
- [x] v0.2.0: Indexes, constraints, triggers
- [x] v0.3.0: Async background workers with progress tracking
- [x] v1.0.0: Resume support and conflict resolution
- [x] v1.1.0: Selective column cloning and data filtering
- [x] v1.2.0: Materialized views and exclusion constraints
- [x] v2.0.0: True multi-worker parallel cloning
- [x] v2.0.1:
pgclone.database_create()— create + clone database - [x] v2.1.0: Progress tracking view (
pgclone.jobs_view) - [x] v2.1.1: Visual progress bar
- [x] v2.1.3: Elapsed time tracking
- [x] v2.1.4: Unix domain socket auth (no more pg_hba.conf trust requirement)
- [x] v2.2.0: Worker pool (fixed pool size instead of one bgworker per table)
- [x] v2.2.1: Read-only transaction for WHERE clause (SQL injection protection)
- [x] v2.0.1:
- [x] v3.0.0: Data anonymization / masking
- [x] v3.1.0: Auto-Discovery of Sensitive Data
- [x] v3.2.0: Applying Static Data Masking to cloned data
- [x] v3.3.0: Applying Dynamic Data Masking to cloned data
- [x] v3.4.0: Clone roles with permissions and passwords
- [x] v3.5.0: Clone verification - compare row counts across source and target
- [x] v3.6.0: GDPR/Compliance masking report
- [x] v4.0.0: Schema namespace — all functions under
pgcloneschema (pgclone.table(),pgclone.schema(), etc.) - [x] v4.1.0: Schema diff — DDL drift detection between source and target (
pgclone.diff) - [x] v4.2.0: Pre-flight validator — connection, space, permissions, version, name-conflict checks before clone (
pgclone.preflight) - [x] v4.3.0: Consistent-snapshot clones — every clone reads the source under one shared
REPEATABLE READ READ ONLYsnapshot (sync, async, and parallel pool); cross-table FK consistency on a live source is now guaranteed (same model aspg_dump -j) - [x] v4.4.0: Schema/database-level in-line masking (
"masks") and regex table subset filters ("tables"/"exclude_tables") — discussion #16 - [x] v4.4.1: Type-aware masking — a mask that a column’s type cannot store (e.g.
random_inton aboolean) is skipped with a warning instead of aborting the clone;discover_sensitiveno longer suggests incompatible masks — issue #17 - [x] v4.4.2: Constraint- and length-aware masking — masks are clamped to a column’s length and skipped when they would overflow, feed a bad literal to a numeric column, or violate a NOT NULL / UNIQUE / FOREIGN KEY constraint; UNIQUE/PK and NOT NULL sensitive columns are steered to
hash— issue #18 - [x] v4.4.3:
"conflict"(error/skip/replace/rename) is now honored by the synchronous clone path (pgclone.table()/pgclone.schema()/pgclone.database()), not just the async job path — issue #22 - [x] v4.4.4: Auto-discovery of bank account numbers/IBAN;
pgclone.masking_report()gains a(conninfo, schema)overload to audit a remote source directly — issue #23 - [x] v4.5.0: Clone a table from Oracle into PostgreSQL —
pgclone.table_from_oracle()with Oracle→PG type mapping,dry_run, CLOB/RAW support and an optional OCI build (WITH_ORACLE=1); verified in CI against Oracle Free - [x] v4.6.0: Clone a table from Microsoft SQL Server into PostgreSQL —
pgclone.table_from_sqlserver()with SQL Server→PG type mapping,dry_run,NVARCHAR(MAX)/VARBINARY/UNIQUEIDENTIFIERsupport and an optional FreeTDS build (WITH_SQLSERVER=1); CI job against SQL Server 2022 - [x] v4.7.0: Clone a table from MySQL / MariaDB into PostgreSQL —
pgclone.table_from_mysql()with MySQL→PG type mapping,dry_run, unsigned/BIT/JSON/ENUM/SET/BLOBsupport, zero-date handling and an optional client build (WITH_MYSQL=1); CI job against MySQL 8.0 - [ ] v4.8.0: FK-aware referential sampling — sample N rows and follow foreign keys to keep child rows consistent (
pgclone.table_sample)