psycopg2: connect copy_from and copy_to

[james.pye at gmail.com]

> Doesn't PostGres come with Export/Import apps ? That would be easiest
> (and faster).

Yes, PostgreSQL core has import/export apps, but they tend to target
general administration rather than transactional loading/moving of
data. ie, dump and restore a database or schema. There is a pgfoundry
project called pgloader that appears to be targeting a more general
scenario of, well, loading data, but I imagine most people end up
writing custom ETL for data flow.

> Else,
>
> prod_cursor.execute('select data from production')
> for each_record in cursor.fetchall():
>     dev_cursor.execute('insert into testing')
>
> that is one way of doing it

In any high volume cases you don't want to do that. The current best
practice for loading data into an existing PostgreSQL database is
create temp, load into temp using COPY, merge from temp into
destination(the last part is actually the tricky one ;).