DOCSEARCHbyAlgolia
Submit Docs

pgsync

repository·master·Indexed 25 days ago

https://github.com/ankane/pgsync

A high-speed, secure command-line tool for syncing data between Postgres databases. It supports partial syncs, table groups with parameterized IDs, schema synchronization via pg_dump/pg_restore, and data rules to anonymize sensitive information. It includes built-in support for excluding framework migration tables from Rails, Django, and Laravel.

Tokens
4.8K
Snippets
7
Records
37
Agent score
85%

What's inside pgsync

  1. Sync data between Postgres databases

    master

    Use the pgsync command to transfer data. By default, it syncs all tables defined in your configuration.

    Common Sync Commands:

    • Sync all tables: pgsync
    • Sync specific tables: pgsync table1,table2
    • Sync using wildcards: pgsync "table*"
    • Sync specific rows (overwrites existing): pgsync products "where store_id = 1"
    • Sync specific rows (preserves existing): pgsync products "where store_id = 1" --preserve
    • Sync specific rows (truncates existing): pgsync products "where store_id = 1" --truncate
    • Sync in batches (for large append-only tables): pgsync large_table --in-batches (requires a numeric, increasing primary key)

    Note: To avoid accidentally overwriting production, the destination is limited to localhost or 127.0.0.1 by default. To allow other hosts, set to_safe: true in your .pgsync.yml.

    pgsync

  2. Handle foreign keys and constraints

    master

    When syncing data with foreign keys, use one of the following strategies:

    1. Defer constraints (Recommended):
      pgsync --defer-constraints
    2. Manual ordering: Sync tables one-at-a-time to ensure correct order:
      pgsync table1,table2,table3 --jobs 1
    3. Disable integrity (Not recommended): This can silently break referential integrity and requires superuser privileges on the destination database.
      pgsync --disable-integrity
      Note: If syncing to Amazon RDS, use the rds_superuser role. Heroku does not support disabling integrity via pgsync.

  3. Initialize pgsync configuration

    master

    To create a .pgsync.yml configuration file in your project directory, run:

    pgsync --init

    If you need to manage multiple database configurations, you can initialize a named configuration:

    pgsync --init db2

    This creates .pgsync-db2.yml. You can then target this configuration using the --db flag:

    pgsync --db db2
    pgsync --init

  4. Install pgsync

    master

    You can install pgsync using RubyGems, Homebrew, or Docker.

    Using RubyGems:

    gem install pgsync

    Using Homebrew:

    brew install pgsync

    Using Docker:

    docker pull ankane/pgsync
alias pgsync="docker run -ti --rm -v .:/conf -w /conf ankane/pgsync"

    Dependencies: If installation fails, ensure you have libpq and ruby installed.

    • macOS: brew install libpq
    • Ubuntu: sudo apt-get install ruby-dev libpq-dev build-essential
    gem install pgsync

  5. Configure sensitive data rules

    master

    Use data_rules in .pgsync.yml to prevent sensitive data from leaving the remote server by applying replacement rules.

    Example Configuration:

    data_rules:
  email: unique_email
  last_name: random_letter
  birthday: random_date
  users.auth_token:
    value: secret
  visits_count:
    statement: "(RANDOM() * 10)::int"
  encrypted_*: null

    Rule Matching:

    • last_name matches all columns named last_name.
    • users.last_name matches only the last_name column in the users table.
    • Wildcards are supported; the first matching rule is applied.

    Available Replacement Options:

    • unique_email (requires single column primary key)
    • unique_phone (requires numeric primary key)
    • unique_secret (requires single column primary key)
    • random_letter
    • random_int
    • random_date
    • random_time
    • random_ip
    • value (replaces with a specific value)
    • statement (replaces with the result of a SQL statement)
    • null
    • untouched

  7. Configure table exclusions and schemas

    master

    You can control which tables are synced via the CLI or the .pgsync.yml file.

    CLI Options:

    • Exclude tables: pgsync --exclude table1,table2
    • Sync all schemas: pgsync --all-schemas
    • Sync specific schemas: pgsync --schemas public,other or pgsync public.table1,other.table2

    Configuration File (.pgsync.yml):

    exclude:
  - table1
  - table2

  8. Handle non-deferrable constraints during sync

    master

    To prevent errors caused by non-deferrable foreign key constraints during data synchronization, use the defer_constraints_v2 option.

    When defer_constraints_v2 is enabled, pgsync performs the following steps:

    1. Starts a transaction on the destination.
    2. Identifies non-deferrable constraints on the destination.
    3. Executes ALTER TABLE ... ALTER CONSTRAINT ... DEFERRABLE for those constraints.
    4. Executes SET CONSTRAINTS ALL DEFERRED.
    5. Starts a transaction on the source to ensure a consistent snapshot.
    6. Runs the synchronization tasks.
    7. Executes SET CONSTRAINTS ALL IMMEDIATE.
    8. Reverts the constraints to NOT DEFERRABLE using ALTER TABLE ... ALTER CONSTRAINT ... NOT DEFERRABLE.

  9. Initialize a new pgsync configuration file

    master

    Use the pgsync --init command to generate a new .pgsync.yml configuration file. This command detects your framework (Rails, Django, or Laravel) and automatically adds common migration tables to the exclude list to prevent them from being synced.

    If a configuration file already exists at the target path, the command will raise an error. You can specify a custom path for the configuration file by providing a database configuration file as an argument or using the --config option.

    pgsync --init [db]

  10. Handle integrity and triggers during sync

    master

    To prevent foreign key or trigger violations during large data transfers, you can use the following options:

    1. Standard Integrity Disabling: Use --disable-integrity to disable specific integrity triggers. This requires superuser privileges.
    2. RDS Compatible Disabling: Use --disable-integrity-v2 to set session_replication_role = replica. This is the preferred method for Amazon RDS environments.
    3. User Trigger Disabling: Use --disable-user-triggers to specifically disable user-defined triggers without affecting internal system triggers.

    When using these options, pgsync wraps the operation in a transaction to ensure triggers are restored even if the sync fails.

  11. Use groups and parameters in pgsync

    master

    You can define groups in your configuration to bundle multiple tables together. When using a group via the CLI, you can pass a parameter to expand SQL templates within that group.

    Syntax: group_name:parameter

    When a parameter is passed to a group, the resolver attempts to substitute {id} and {1} in the associated SQL templates with the provided parameter value.

  12. Use variables and parameterized groups

    master

    You can use groups to sync a specific record and its associated related records by using the {id} placeholder.

    Example .pgsync.yml configuration:

    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 the group with a specific ID:

    pgsync product:123
    pgsync product:123