pgsync

Quickly and securely sync data between environments

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 (truncates destination table first)

pgsync products --where "id < 100"

To preserve existing rows, use:

pgsync products --where "id < 100" --preserve

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

Schema

Fetch schema

pgsync schema

Specify tables

pgsync schema table1,table2

Groups

Define groups in .pgsync.yml:

groups:
  group1:
    - table1
    - table2
  group2:
    - table3
    - table4

And run:

pgsync groups group1,group2

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: