Re: Copy Data between different databases

Hi Adrian:

On Wed, Mar 4, 2015 at 1:03 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

>
> ​As you pointed, my bet is in the -f case COPY FROM STDIN expects the
>> data on the file ( otherwise pg_dumps would not work ), but your
>> sugestion seems to have a problem of double redirection, let me elaborate:
>>
>
> Well according to here, they should be roughly equivalent:
>
> http://www.postgresql.org/docs/9.3/interactive/app-psql.html
>

​.... Yeah, they should​, I' was not discussing you. I was pointing the
SHELL line was incorrect,

> Trying it showed they where and ended with the same result, the data was
> not copied over:(
>

​Of course, I'll be greatly surprissed if they did. ​

>
>
> If I did this:
>
> psql -h host1 -U user1 -d db1 -f /q1.sql | psql -h host2 -U user2 -d db2
> -f -
>
> I saw the stdout from my 'q1.sql' show up at the second command, where it
> threw an error because it was just the data without the COPY .. FROM
> statement. So the second command must eat the stdin before it actually runs
> q2.sql. Figured this would have been an easy fix. In my case for this sort
> of thing I use Python/psycopg2 and its COPY TO/FROM commands and run it
> through a buffer. Though of late I have starting using Pandas also.
>

​Of course you end up with an error, I would have reported a bug otherwise.
And also you are not using q2.sql so the result would have been wrong. I
did send you a form ( what you've nicely quoted back ) :

>> (cat q2.sql; ​ psql -h host1 -U user1 -d db1 -f /q1.sql) | psql -h host2
>> -U user2 -d db2
>>
>
​of putting q2.sql in front ​of the output from q1.sql in the same pipe,
even with some samples of how this pipes works. Maybe you stopped reading
too soon. I cannot try it as I do not have q1.sql or q2.sql, but given what
I know about the reading/writing code of psql ( and that I have made this
kinds of things before ) it should work. It's a classical shell construct,
use a sub-shell ( parentheses ) to combine several commands and pipe its
output to another one. The problem what all the others constructs seem to
be trying to do it with a pipe of single commands, which is much more
difficult. Of course, if the problemis due to inadequate shells ( as, say,
cmd.exe ) it may need to be done in other ways.

Regards.
Francisco Olarte.