Re: via psycopg2 or pg2pg? Move rows from one database to other

Thomas:

On Mon, Feb 27, 2017 at 12:47 PM, Thomas Güttler
<guettliml(at)thomas-guettler(dot)de> wrote:
> Thank you for explaining the steps of your algorithm.

My pleasure. But check it anyway, I may have forgotten something ( I
normally implement this things after writing a big flow diagram on a
piece of paper and checking it for a while, or something similar, I
find easier to spot the missing spots graphically )

> Just one question: How to do the actual transfer of data?

It does not matter too much, but ..

> I see two solutions:
> 1, Read the data into a script (via psycopg2 (we love python))
> and dump it into a second connection.
> 2, connect postgres to postgres and transfer the data without a database
> adapter like psycopg2.

For 2 you need and adapter, the foreign data wrapper, anyway. I
personally would go for 1, especially if you can collocate the program
near main db ( same machine or network, so you can have enough speed )
. Normally problems are much easier to diagnose this way, as you only
deal with psycopg2, not with psyco AND fdw, and you will need a
program to do the transfers anyway. Also, IIRC, you had a lot of
machines, so you will need a main program to do all the retrying and
accounting. And you can optimize some things ( like copying from
several satellites and then inserting them at once ).

YMMV anyway, just use whichever is easier for you, but avoid false lazyness ;-)

Francisco Olarte.