Re: Copy Data between different databases

Hi Francisco,

thanks!

The solution with

(cat q2.sql; ​ psql -h host1 -U user1 -d db1 -f /q1.sql) | psql -h host2 -U user2 -d db2

worked! First I have forgotten the semicolon at the end of q2.sql and got an error.

Tim

Von: Francisco Olarte [mailto:folarte(at)peoplecall(dot)com]
Gesendet: Mittwoch, 4. März 2015 15:48
An: Adrian Klaver
Cc: Tim Semmelhaack; pgsql-general(at)postgresql(dot)org
Betreff: Re: [GENERAL] 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 <mailto: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.