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
Fetch all tables
pgsync
Fetch specific tables
pgsync table1,table2
Fetch 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 sync specific rows:
groups:
user:
users: "WHERE id = {id}"
orders: "WHERE user_id = {id}"
And run:
pgsync user:123
to get rows associated with user 123
.
Schema
Fetch 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:
- Report bugs
- Fix bugs and submit pull requests
- Write, clarify, or fix documentation
- Suggest or add new features