CompeAnansi posted:

Since no one is replying I'll mention what we do for Postgres to Postgres syncs since Redshift is based on Postgres and has the COPY command (although it may be somewhat restricted). Assuming that none of the tables in the DB are larger than memory, you can use:

code:

COPY table TO STDOUT

to pull the table into an in memory buffer, then immediately push it back out to the destination (after setting the buffer back to the start with file.seek(0)) with:

code:

COPY table FROM STDIN

As I said I'm not 100% sure how that'll carry over to Redshift (or what your source DBs are), but it's a potential starting place for high speed duplication.

EDIT: A cursory google search after posting turned this up, though: https://stackoverflow.com/questions/31603429/redshift-copy-from-stdin-using-java-program. Sounds like you'll need to copy it to S3 first then you can use the

code:

COPY table FROM Amazon S3

command instead, but this is significantly less efficient because you have to store the table in an intermediary stage rather than just temporarily in memory.