pgsync

# pgsync

Sync data from one Postgres database to another (like `pg_dump`/`pg_restore`). Designed for:

- **speed** - tables are transferred in parallel
- **security** - built-in methods to prevent sensitive data from ever leaving the server
- **flexibility** - gracefully handles schema differences, like missing columns and extra columns
- **convenience** - sync partial tables, groups of tables, and related records

:tangerine: Battle-tested at [Instacart](https://www.instacart.com/opensource)

[![Build Status](https://github.com/ankane/pgsync/actions/workflows/build.yml/badge.svg)](https://github.com/ankane/pgsync/actions)

## Installation

pgsync is a command line tool. To install, run:

```sh
gem install pgsync
```

This will give you the `pgsync` command. If installation fails, you may need to install [dependencies](#dependencies).

You can also install it with Homebrew:

```sh
brew install pgsync
```

Or [Docker](#docker).

## Setup

In your project directory, run:

```sh
pgsync --init
```

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

First, make sure your schema is set up in both databases. We recommend using a schema migration tool for this, but pgsync also provides a few [convenience methods](#schema). Once that’s done, you’re ready to sync data.

Sync tables

```sh
pgsync
```

Sync specific tables

```sh
pgsync table1,table2
```

Works with wildcards as well

```sh
pgsync "table*"
```

Sync specific rows (existing rows are overwritten)

```sh
pgsync products "where store_id = 1"
```

You can also preserve existing rows

```sh
pgsync products "where store_id = 1" --preserve
```

Or truncate them

```sh
pgsync products "where store_id = 1" --truncate
```

## Tables

Exclude specific tables

```sh
pgsync --exclude table1,table2
```

Add to `.pgsync.yml` to exclude by default

```yml
exclude:
  - table1
  - table2
```

Sync tables from all schemas or specific schemas (by default, only the search path is synced)

```sh
pgsync --all-schemas
# or
pgsync --schemas public,other
# or
pgsync public.table1,other.table2
```

## Groups

Define groups in `.pgsync.yml`:

```yml
groups:
  group1:
    - table1
    - table2
```

And run:

```sh
pgsync group1
```

## Variables

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

To get product `123` with its reviews, last 10 coupons, and store, use:

```yml
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})"
```

And run:

```sh
pgsync product:123
```

## Schema

Sync the schema before the data (this wipes out existing data)

```sh
pgsync --schema-first
```

Specify tables

```sh
pgsync table1,table