pg2pg

A cli tool that lets you copy data between Postgres (and Amazon Redshift) servers. Just create a config.json file

Install

npm install -g pg2pg

Usage

pg2pg -c config.json

Sample config

The following config will copy all data in the users table from some_redshift_db to some_postgres_db.

NOTE: The table must exist in the target db.

{
    "schema_version": 1.1,
    "refs": {
        "some_postgres_db": {
            "type": "postgres",
            "host": "",
            "database": "",
            "user": "",
            "password": "",
            "port": 5432
        },
        "some_redshift_db": {
            "type": "redshift",
            "host": "",
            "database": "",
            "user": "",
            "password": "",
            "port": 543
        }
    },
    "commands": [
        {
            "type": "copy",
            "description": "Copy users table to the new server",
            "source_database": "${some_redshift_db}",
            "source_query": "SELECT * FROM users",
            "target_database": "some_postgres_db",
            "target_table": "users"
        }
    ]
}
 

Commands

copy

source_database - Reference to a db which contains the data source_query : A query to be executed on the source_database source_query_file : Path to a file that contains the source_query target_database - Reference to a db where the results will be copied to target_table - Name of the table in the target_database batchSize - (optional. Default 1000) The number of rows to batch insert

{
    "type": "copy",
    "description": "Copy users table to the new server",
    "source_database": "some_redshift_db",
    "source_query": "SELECT * FROM users",
    "target_database": "some_postgres_db",
    "target_table": "users",
    "bathSize" : 1000
}

query

database - Reference to a db to run query on query : A query to be executed on the database query_file : Path to a file that contains the query

{
    "type": "query",
    "description": "Clear all existing users",
    "database": "some_postgres_db",
    "query": "DELETE FROM users"
}

queryParams

queryParams are used to substitute a variable into your query at execution time. queryParams : value_to_pass

"queryParams": 
{
      "start_date": "2016-01-01",
      "end_date": "2017-01-01"
}