pgsync

Quickly and securely sync data between environments

:tangerine: Battle-tested at Instacart

Installation

gem install pgsync

And in your project directory, run:

pgsync --setup

This creates .pgsync.yml for you to customize. We recommend checking this into your version control (assuming it doesn’t contain sensitive information). pgsync commands can be run from this directory or any subdirectory.

How to Use

Sync all tables

pgsync

Sync specific tables

pgsync table1,table2

Sync specific rows

pgsync products "WHERE id < 1000"

Exclude Tables

pgsync --exclude users

To always exclude, add to .pgsync.yml.

exclude:
  - table1
  - table2

For Rails, you probably want to exclude schema migrations.

exclude:
  - schema_migrations

Groups

Define groups in .pgsync.yml:

groups:
  group1:
    - table1
    - table2

And run:

pgsync group1

You can also use groups to sync a specific record and associated records in other tables.

To get user 123 and his or her orders, use:

groups:
  user:
    users: "WHERE id = {id}"
    orders: "WHERE user_id = {id}"

And run:

pgsync user:123

Schema

Sync schema

pgsync --schema-only

Specify tables

pgsync table1,table2 --schema-only

Sensitive Information

Prevent sensitive information - like passwords and email addresses - from leaving the remote server.

Define rules in .pgsync.yml:

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

last_name matches all columns named last_name and users.last_name matches only the users table. Wildcards are supported, and the first matching rule is applied.

Options for replacement are:

  • null
  • value
  • statement
  • unique_email
  • unique_phone
  • random_letter
  • random_int
  • random_date
  • random_time
  • random_ip
  • untouched

Multiple Databases

To use with multiple databases, run:

pgsync --setup db2

This creates .pgsync-db2.yml for you to edit. Specify a database in commands with:

pgsync --db db2

Safety

To keep you from accidentally overwriting production, the destination is limited to localhost or 127.0.0.1 by default.

To use another host, add to_safe: true to your .pgsync.yml.

Upgrading

Run:

gem install pgsync

To use master, run:

gem install specific_install
gem specific_install ankane/pgsync

Thanks

Inspired by heroku-pg-transfer.

TODO

  • Support for schemas other than public

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help: