pgsync: Parallel Postgres Data Sync with Selective Masking

Copying Postgres Data Between Environments: What pgsync Addresses

Developers regularly need a copy of production data on their local machine to reproduce bugs, test migrations, or seed a staging environment. The standard approach, pg_dump followed by pg_restore, dumps the full database or schema, can be slow on large databases, and copies sensitive columns like emails and tokens verbatim.

pgsync is a Ruby command-line tool that solves three specific problems with this workflow. It transfers tables in parallel to reduce transfer time. It applies column-level masking rules before data leaves the source, so sensitive fields never reach the destination database. And it handles partial table transfers with SQL WHERE clauses, so engineers can pull only the rows relevant to a specific test case without copying an entire table. The README describes it as designed for speed, security, flexibility, and convenience, and notes it is battle-tested at Instacart.

How pgsync Works: Parallel Reads and .pgsync.yml Configuration

pgsync reads connection strings from a `.pgsync.yml` file in the project directory. The `from` key holds the source database URL and the `to` key holds the destination. Tables are transferred in parallel by default, with each table processed in its own thread.

The tool compares the schemas of the source and destination before transferring. The README states pgsync handles schema differences gracefully, including missing columns and extra columns. If the destination is missing a column that the source has, pgsync skips that column. If the destination has an extra column not in the source, pgsync leaves it untouched.

pgsync does not attempt to sync Postgres extensions. If your schema depends on extension-provided types or functions, pgsync transfers the data rows but the extensions themselves must already be present in the destination.

Installing and Running a First Sync

Install pgsync as a Ruby gem:

gem install pgsync

Or with Homebrew:

brew install pgsync

A Docker image is also available. The Dockerfile in the repository builds from `ruby:3-alpine`, installs `libpq-dev` and `postgresql16-client`, then installs the gem. After installation, initialize the configuration in your project directory:

pgsync --init

This creates `.pgsync.yml` for you to edit with the source and destination connection strings. The README recommends committing this file to version control as long as it does not contain sensitive credentials. Running `pgsync --init` in a Rails project automatically excludes Active Record metadata and schema migration tables from syncs by adding them to the `exclude` list.

To sync all tables:

pgsync

To sync specific tables:

pgsync table1,table2

Wildcards are supported:

pgsync "table*"

To sync only rows matching a condition:

pgsync products "where store_id = 1"

By default, existing rows in the destination are overwritten. To preserve them, add `--preserve`. To clear the destination table first, use `--truncate`. To debug by viewing the SQL that runs:

pgsync --debug

The same init logic applies to Django projects (which exclude `django_migrations`) and Laravel projects (which exclude `migrations`), so the generated `.pgsync.yml` starts with the right exclusions for each framework.

Groups and Variable-Based Record Pulls

Groups let you define a named set of tables in `.pgsync.yml` and sync them together with a single command. A group definition looks like this:

groups:
  group1:
    - table1
    - table2

Run it with `pgsync group1`. Groups also support variables for pulling a specific record and its related data across tables. To fetch product 123 with its reviews, last 10 coupons, and store record:

groups:
  product:
    products: "where id = {1}"
    reviews: "where product_id = {1}"
    coupons: "where product_id = {1} order by created_at desc limit 10"
    stores: "where id in (select store_id from products where id = {1})"

Run with `pgsync product:123`. This is useful for reproducing issues tied to a specific entity without copying entire tables.

The README recommends using groups when possible to take advantage of parallelism, since tables in a group are transferred concurrently.

Masking Sensitive Columns with data_rules

The `data_rules` section in `.pgsync.yml` defines replacement values for columns that should not leave the production database. A rule like `email: unique_email` replaces every value in any column named `email` with a synthetic email address. A fully qualified name like `users.last_name` applies the rule only to that specific table and column. Wildcards are supported, and the first matching rule is applied.

Available replacement strategies include `unique_email`, `unique_phone`, `unique_secret`, `random_letter`, `random_int`, `random_date`, `random_time`, `random_ip`, a literal `value`, a SQL `statement`, `null`, and `untouched`. Rules starting with `unique_` require the table to have a single-column primary key. `unique_phone` requires a numeric primary key.

The README describes the rule as preventing sensitive data from ever leaving the remote server. The masking happens before the data is read from the source and sent over the network, not as a post-processing step on the destination.

Foreign Key Constraints and the Safety Default

Foreign key constraints can fail when tables are loaded in the wrong order during a sync. pgsync provides three options. The recommended approach is to defer constraints during the sync:

pgsync --defer-constraints

The second option is to manually specify table order by setting `--jobs 1` so tables sync one at a time:

pgsync table1,table2,table3 --jobs 1

The third option, `--disable-integrity`, disables foreign key triggers and can silently break referential integrity. The README labels this not recommended. It requires superuser privileges on the destination. If the destination is Amazon RDS, use the `rds_superuser` role. The README notes there is no known way to do this on Heroku.

For safety, pgsync limits the destination to `localhost` or `127.0.0.1` by default, to prevent accidental overwrites of a production database. To use another host, add `to_safe: true` to `.pgsync.yml`. This is a deliberate design choice that adds a friction point before any sync targeting a non-local host.

Where pgsync Falls Short: Schema, Extensions, and Large Tables

pgsync is not a schema migration tool. It transfers data rows; the destination schema must already match the source, or differ only in ways pgsync handles gracefully (extra or missing columns). For schema changes, the README recommends a dedicated schema migration tool. Schema sync options (`--schema-first` and `--schema-only`) exist as convenience methods, but both wipe existing data in the destination tables before writing. Use them only when you intend to reset the destination entirely.

pg_dump with pg_restore is the more general alternative. It can dump schema and data together, includes extensions, and supports formats like directory or custom for parallel restore. The trade-off is that pg_dump makes no provision for masking sensitive columns and does not support partial row syncs without post-processing. Engineers who need a full database copy for backup or migration purposes should use pg_dump; engineers who need a sanitized, partial copy for development should use pgsync.

For large append-only tables, pgsync supports `--in-batches`, which processes the table in segments and resumes where it left off if interrupted. This requires a numeric, increasing primary key. The README describes it as useful for backfills. For tables without that key structure, the batch mode is not available. pgsync does not support syncing tables from multiple schemas by default; use `--all-schemas` or `--schemas public,other` to include schemas beyond the search path.

pgsync is licensed under MIT, which places no restrictions on commercial use. The gem has no recent GitHub releases tagged on GitHub, but the README is specific about commands and behavior, and the repository includes a CHANGELOG.

Editorial conclusion

pgsync is a good fit for teams that regularly copy production data to development or staging environments and need to strip out personal information before the transfer. The single-command install and SQL-based row filtering make the common case quick to set up. It is the wrong tool for continuous replication, schema migration, or syncing Postgres extensions. The `to_safe: true` flag and `--defer-constraints` behavior are worth reviewing before any sync that targets a database outside localhost. The last push was on 2026-08-15.

Frequently asked questions

Official sources

1. ankane/pgsync on GitHub
2. Issues
3. License: MIT
4. README