Managing PostgreSQL environments often involves tasks such as copying databases, replicating schemas, creating development environments from production systems, masking confidential information, and comparing database structures across servers. To simplify these operations, the pgclone extension provides a collection of PostgreSQL-native cloning and synchronization features.
In this guide, we tested the extension in a local environment running PostgreSQL 18 and PostgreSQL 17 clusters. The article walks through the installation process, configuration steps, cloning operations, masking features, asynchronous jobs, verification tools, and schema comparison capabilities using practical SQL examples and real execution results.
Here we are using two separate postgres clusters to test the pgclone extension functionalities
You can check the current postgres clusters installed in your system by using the following command
pg_lsclusters
Result :
Ver Cluster Port Status Owner Data directory Log file
17 main 5433 online postgres /var/lib/postgresql/17/main
18 main 5432 online postgres /var/lib/postgresql/18/main
The setup used:
- PostgreSQL 18 on port 5432 as the source server
- PostgreSQL 17 on port 5433 as the target server
Installing pgclone Extension
The extension was installed from source.
git clone https://github.com/valehdba/pgclone
Compile the extension:
make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
Install the extension:
sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config install
Compile and install the extension also in the postgres 17 by using the below command
make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/17/bin/pg_config
sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/17/bin/pg_config install
Creating the Extension
Inside PostgreSQL:
CREATE EXTENSION pgclone;
Set the value of shared_preload_libraries as pgclone like this.
Before doing that check the path of postgres configuration file.
Show config_file;
Result :
config_file
-----------------------------------------
/etc/postgresql/18/main/postgresql.conf
(1 row)
Open the file and set value for the parameter shared_preload_libraries like this.
sudo nano /etc/postgresql/18/main/postgresql.conf
Edit like this.
shared_preload_libraries = 'pgclone' # (change requires restart)
Do the same steps for the other postgres cluster.
Now restart the postgresql server.
sudo systemctl restart postgresql
Inside psql check the functions provided by the pgclone extension like this.
\dx+ pgclone
Result :
Objects in extension "pgclone"
Object description
---------------------------------------------------------------------------
function pgclone.cancel(integer)
function pgclone.clear_jobs()
function pgclone.clone_roles(text)
function pgclone.clone_roles(text,text)
function pgclone.create_masking_policy(text,text,text,text)
function pgclone.database_create(text,text,boolean)
function pgclone.database_create(text,text,boolean,text)
function pgclone.database(text,boolean)
function pgclone.database(text,boolean,text)
function pgclone.diff(text,text)
function pgclone.discover_sensitive(text,text)
function pgclone.drop_masking_policy(text,text)
function pgclone.functions(text,text)
function pgclone.jobs()
function pgclone.masking_report(text)
function pgclone.mask_in_place(text,text,text)
function pgclone.progress_detail()
function pgclone.progress(integer)
function pgclone.resume(integer)
function pgclone.schema_async(text,text,boolean,text)
function pgclone.schema_ex(text,text,boolean,boolean,boolean,boolean)
function pgclone.schema(text,text,boolean)
function pgclone.schema(text,text,boolean,text)
function pgclone.table_async(text,text,text,boolean,text,text)
function pgclone.table_ex(text,text,text,boolean,text,boolean,boolean,boolean)
function pgclone."table"(text,text,text,boolean)
function pgclone."table"(text,text,text,boolean,text)
function pgclone."table"(text,text,text,boolean,text,text)
function pgclone.verify(text)
function pgclone.verify(text,text)
function pgclone.version()
Source Database Setup on Port 5432
Connect to PostgreSQL 18 cluster:
psql -p 5432
Create database:
CREATE DATABASE source_db;
Connect:
\c source_db
Creating Schemas
CREATE SCHEMA sales;
CREATE SCHEMA hr;
CREATE SCHEMA utils;
Listing schemas:
\dn
Output:
Name | Owner
-------+-------------------
hr | postgres
public | pg_database_owner
sales | postgres
utils | postgres
Creating Tables
Customers Table
CREATE TABLE sales.customers (
id SERIAL PRIMARY KEY,
full_name TEXT,
email TEXT,
phone TEXT,
city TEXT,
created_at TIMESTAMP DEFAULT now()
);
Orders Table
CREATE TABLE sales.orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES sales.customers(id),
total NUMERIC,
status TEXT,
created_at TIMESTAMP DEFAULT now()
);
Employees Table
CREATE TABLE hr.employees (
id SERIAL PRIMARY KEY,
full_name TEXT,
email TEXT,
phone TEXT,
salary INT,
ssn TEXT,
department TEXT
);
Inserting Sample Data
Customers
INSERT INTO sales.customers
(full_name, email, phone, city)
VALUES
('Alice Johnson', 'alice@gmail.com', '9999911111', 'Delhi'),
('Bob Smith', 'bob@gmail.com', '8888822222', 'Mumbai'),
('Charlie Brown', 'charlie@gmail.com', '7777733333', 'Chennai'),
('David Lee', 'david@gmail.com', '6666644444', 'Bangalore'),
('Eva Green', 'eva@gmail.com', '5555555555', 'Kochi');
Orders
INSERT INTO sales.orders
(customer_id, total, status)
VALUES
(1, 1200, 'confirmed'),
(2, 4500, 'pending'),
(3, 2300, 'confirmed'),
(1, 7800, 'delivered'),
(5, 1500, 'cancelled');
Employees
INSERT INTO hr.employees
(full_name, email, phone, salary, ssn, department)
VALUES
('John Doe', 'john@company.com', '9999999999', 50000, 'AAA111', 'IT'),
('Jane Smith', 'jane@company.com', '8888888888', 70000, 'BBB222', 'HR'),
('Robert King', 'robert@company.com', '7777777777', 80000, 'CCC333', 'Finance');
Creating Functions
CREATE OR REPLACE FUNCTION utils.get_total_orders()
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
total_count INT;
BEGIN
SELECT COUNT(*) INTO total_count
FROM sales.orders;
RETURN total_count;
END;
$$;
Creating Index
CREATE INDEX idx_orders_status
ON sales.orders(status);
Creating Trigger
Trigger function:
CREATE OR REPLACE FUNCTION sales.log_order_insert()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'New order inserted: %', NEW.id;
RETURN NEW;
END;
$$;
Trigger creation:
CREATE TRIGGER trg_order_insert
AFTER INSERT
ON sales.orders
FOR EACH ROW
EXECUTE FUNCTION sales.log_order_insert();
Creating Test Role
CREATE ROLE app_user LOGIN PASSWORD 'app123';
PostgreSQL displayed a warning about MD5 password deprecation.
Privileges were granted:
GRANT USAGE ON SCHEMA sales TO app_user;
GRANT SELECT, INSERT
ON sales.customers
TO app_user;
Target Database Setup on Port 5433
Connect to PostgreSQL 17 cluster:
psql -p 5433
Create database:
CREATE DATABASE target_db;
Connect:
\c target_db
Install extension:
CREATE EXTENSION pgclone;
Cloning a Single Table
SELECT pgclone.table(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
'sales',
'customers',
true
);
Result:
table
-------
OK
(1 row)
Querying cloned data:
SELECT * FROM sales.customers;
Result :
id | full_name | email | phone | city | created_at
----+---------------+-------------------+------------+-----------+----------------------------
1 | Alice Johnson | alice@gmail.com | 9999911111 | Delhi | 2026-05-07 21:33:18.304229
2 | Bob Smith | bob@gmail.com | 8888822222 | Mumbai | 2026-05-07 21:33:18.304229
3 | Charlie Brown | charlie@gmail.com | 7777733333 | Chennai | 2026-05-07 21:33:18.304229
4 | David Lee | david@gmail.com | 6666644444 | Bangalore | 2026-05-07 21:33:18.304229
5 | Eva Green | eva@gmail.com | 5555555555 | Kochi | 2026-05-07 21:33:18.304229
(5 rows)
All five rows were cloned successfully.
Structure-Only Clone
SELECT pgclone.table(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
'sales',
'orders',
false
);
A warning appeared:
WARNING: pgclone: local exec failed: ERROR: function sales.log_order_insert() does not exist
(query: CREATE TRIGGER trg_order_insert AFTER INSERT ON sales.orders FOR EACH ROW EXECUTE FUNCTION sales.log_order_insert())
This happened because the trigger function was not cloned before cloning the table structure.
The table structure existed successfully, but no rows were copied.
Check the cloned table structure by following the query
select * from sales.orders;
Result :
id | customer_id | total | status | created_at
----+-------------+-------+--------+------------
(0 rows)
Cloning with a Different Table Name
SELECT pgclone.table(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
'sales',
'customers',
true,
'customers_backup'
);
The new table customers_backup was created successfully.
select * from sales.customers_backup ;
Result :
id | full_name | email | phone | city | created_at
----+---------------+-------------------+------------+-----------+----------------------------
1 | Alice Johnson | alice@gmail.com | 9999911111 | Delhi | 2026-05-07 21:33:18.304229
2 | Bob Smith | bob@gmail.com | 8888822222 | Mumbai | 2026-05-07 21:33:18.304229
3 | Charlie Brown | charlie@gmail.com | 7777733333 | Chennai | 2026-05-07 21:33:18.304229
4 | David Lee | david@gmail.com | 6666644444 | Bangalore | 2026-05-07 21:33:18.304229
5 | Eva Green | eva@gmail.com | 5555555555 | Kochi | 2026-05-07 21:33:18.304229
(5 rows)
Cloning Functions
SELECT pgclone.functions(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
'utils'
);
Testing the function:
SELECT utils.get_total_orders();
Output:
get_total_orders
------------------
0
(1 row)
Since no order data existed yet in the target schema during that stage, the function returned zero.
Full Database Clone
Create a new database
CREATE DATABASE full_clone_db;
Connect:
\c full_clone_db
Install extension:
CREATE EXTENSION pgclone;
Clone database:
SELECT pgclone.database(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
true
);
Result:
NOTICE: pgclone: database clone complete -- 4 schemas cloned
Check the schemas by using the following command
\dn
Result :
List of schemas
Name | Owner
---------+-------------------
pgclone | postgres
public | pg_database_owner
sales | postgres
utils | postgres
(4 rows)
Selective Column Cloning
SELECT pgclone.table(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
'sales',
'customers',
true,
'customers_small',
'{"columns":["id","full_name","city"]}'
);
Querying:
SELECT * FROM sales.customers_small;
Output contained only selected columns.
Result :
id | full_name | city
----+---------------+-----------
1 | Alice Johnson | Delhi
2 | Bob Smith | Mumbai
3 | Charlie Brown | Chennai
4 | David Lee | Bangalore
5 | Eva Green | Kochi
(5 rows)
WHERE Clause Filtering
SELECT pgclone.table(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
'sales',
'orders',
true,
'confirmed_orders',
'{"where":"status = ''confirmed''"}'
);
Only confirmed orders were cloned.
select * from sales.confirmed_orders ;
Result :
id | customer_id | total | status | created_at
----+-------------+-------+-----------+----------------------------
1 | 1 | 1200 | confirmed | 2026-05-07 21:33:23.116038
3 | 3 | 2300 | confirmed | 2026-05-07 21:33:23.116038
(2 rows)
Data Masking
SELECT pgclone.table(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
'hr',
'employees',
true,
'employees_masked',
'{
"mask":{
"email":"email",
"full_name":"name",
"phone":"phone",
"salary":{"type":"random_int","min":30000,"max":100000},
"ssn":"null"
}
}'
);
Querying masked table:
SELECT * FROM hr.employees_masked;
Result :
id | full_name | email | phone | salary | ssn | department
----+-----------+----------------------------------+----------+--------+-----+------------
1 | XXXX | 7ac427067fa3b3683af67f3108c59ffc | ***-9999 | 75837 | | IT
2 | XXXX | 7ac427067fa3b3683af67f3108c59ffc | ***-8888 | 60061 | | HR
3 | XXXX | 002355b6e17bebc8ae89e926f8bbd9e2 | ***-7777 | 99827 | | Finance
(3 rows)
Discovering Sensitive Data
SELECT pgclone.discover_sensitive(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
'hr'
);
Output automatically identified sensitive columns such as:
discover_sensitive
-----------------------------------------------------------------------------------------------------------------
{"employees": {"full_name": "name", "email": "email", "phone": "phone", "salary": "random_int", "ssn": "null"}}
(1 row)
Cloning Roles
SELECT pgclone.clone_roles(
'host=localhost port=5432 dbname=source_db user=postgres password=cool'
);
Result:
clone_roles
---------------------------------------------------------
OK: 4 roles created, 1 roles updated, 1 grants applied
(1 row)
Check the roles of the current database by the following command
\du
Result :
List of roles
Role name | Attributes
----------------------------+------------------------------------------------------------
app_user |
audit_test |
authenticator | No inheritance
dashboard_user | Create role, C
Verify Feature
SELECT * FROM pgclone.verify(
'host=localhost port=5432 dbname=source_db user=postgres password=cool'
);
Output:
schema_name | table_name | source_rows | target_rows | match
-------------+------------+-------------+-------------+-------
hr | employees | 0 | 3 | ?
sales | customers | 0 | 5 | ?
sales | orders | 5 | 5 | ?
(3 rows)
This comparison checked row counts between source and target databases.
Masking Report
SELECT * FROM pgclone.masking_report('hr');The report identified:
- unmasked email columns
- unmasked phone columns
- financial fields
- national ID fields
Result :
schema_name | table_name | column_name | sensitivity | mask_status | recommendation
-------------+------------------+-------------+-------------+-------------+---------------------------------
hr | employees | full_name | PII - Name | UNMASKED | Apply mask strategy: name
hr | employees | email | Email | UNMASKED | Apply mask strategy: email
hr | employees | phone | Phone | UNMASKED | Apply mask strategy: phone
hr | employees | salary | Financial | UNMASKED | Apply mask strategy: random_int
hr | employees | ssn | National ID | UNMASKED | Apply mask strategy: null
hr | employees_masked | full_name | PII - Name | UNMASKED | Apply mask strategy: name
hr | employees_masked | email | Email | UNMASKED | Apply mask strategy: email
hr | employees_masked | phone | Phone | UNMASKED | Apply mask strategy: phone
hr | employees_masked | salary | Financial | UNMASKED | Apply mask strategy: random_int
hr | employees_masked | ssn | National ID | UNMASKED | Apply mask strategy: null
(10 rows)
This is useful for GDPR and compliance auditing.
Difference Detection
SELECT jsonb_pretty(
pgclone.diff(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
'sales'
)::jsonb
);
The diff report detected:
- additional target tables
- constraint differences
- trigger differences
Result :
-[ RECORD 1 ]+----------------------------------------------------------------------------------------------------------------------------jsonb_pretty | { + | "views": { | "modified": [ | ], | "only_in_source": [ | ], | "only_in_target": [ | ] | }, | "schema": "sales", | "tables": { | "modified": [ | ], | "only_in_source": [ | ], | "only_in_target": [ | "confirmed_orders", | "customers_small" | ] | }, | "in_sync": false, | "indexes": { | "modified": [ | ], | "only_in_source": [ | ], | "only_in_target": [ | ] | }, | "summary": { | "views_modified": 0, | "tables_modified": 0, | "indexes_modified": 0, | "triggers_modified": 0, | "constraints_modified": 0, | "views_only_in_source": 0, | "views_only_in_target": 0, | "tables_only_in_source": 0, | "tables_only_in_target": 2, | "indexes_only_in_source": 0, | "indexes_only_in_target": 0, | "triggers_only_in_source": 0, | "triggers_only_in_target": 1, | "sequences_only_in_source": 0, | "sequences_only_in_target": 0, | "constraints_only_in_source": 2, | "constraints_only_in_target": 2 | }, | "triggers": { | "modified": [ | ], | "only_in_source": [ | ], | "only_in_target": [ | { | "def": "CREATE TRIGGER trg_order_insert AFTER INSERT ON sales.confirmed_orders FOR EACH ROW EXECUTE FUNCTION sales.log_order_insert()",+ | "name": "trg_order_insert", | "table": "confirmed_orders" | } | ] | },
The extension generated a detailed JSON drift report.
Async Table Cloning
SELECT pgclone.table_async(
'host=localhost port=5432 dbname=source_db user=postgres password=cool',
'sales',
'orders',
true,
'orders_async',
'{}'
);
Result :
table_async
-------------
1
Check the table named sales.orders_async
select * from sales.orders_async ;
Result :
id | customer_id | total | status | created_at
----+-------------+-------+-----------+----------------------------
1 | 1 | 1200 | confirmed | 2026-05-07 21:33:23.116038
2 | 2 | 4500 | pending | 2026-05-07 21:33:23.116038
3 | 3 | 2300 | confirmed | 2026-05-07 21:33:23.116038
4 | 1 | 7800 | delivered | 2026-05-07 21:33:23.116038
5 | 5 | 1500 | cancelled | 2026-05-07 21:33:23.116038
(5 rows)
Monitoring Async Jobs
SELECT * FROM pgclone.progress_detail();
Output showed:
- job id
- progress percentage
- elapsed time
- rows copied
- completion status
Result :
-[ RECORD 1 ]----+----------------------------------------------------------
job_id | 1
status | completed
op_type | table
schema_name | sales
table_name | orders
current_phase | completed
current_table | orders
tables_total | 1
tables_completed | 1
rows_copied | 5
bytes_copied | 0
elapsed_ms | 39
start_time | 2026-05-07 21:47:28.195152+05:30
end_time | 2026-05-07 21:47:28.234649+05:30
error_message |
pct_complete | 100
progress_bar | [¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦] 100.0% | 5 rows | 00:00:00 elapsed
elapsed_time | 00:00:00
The async clone completed successfully with 100 percent progress.
Viewing Jobs
SELECT pgclone.jobs();
Returned JSON job metadata.
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------
jobs | [{"job_id": 1, "status": "completed", "schema": "sales", "table": "orders", "phase": "completed", "tables_completed": 1, "tables_total": 1}]
Clearing Job History
SELECT pgclone.clear_jobs();
Result:
jobs | []
cleared 1 finished job slots
Checking Extension Version
SELECT pgclone.version();
Output:
version
---------------
pgclone 4.1.0
(1 row)
The pgclone extension offers a flexible and efficient approach for handling PostgreSQL cloning and synchronization tasks across different environments. Throughout the testing process, the extension demonstrated support for a wide range of operations including table cloning, schema replication, full database copying, selective column transfers, filtered data cloning, data masking, role migration, asynchronous background jobs, verification reporting, and schema difference analysis.
Overall, pgclone can be highly useful for development environments, staging systems, migration workflows, testing infrastructures, and data anonymization use cases. Its PostgreSQL-native design and broad feature set make it a strong option for managing database cloning and synchronization tasks efficiently.