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:fola...@peoplecall.com]
Gesendet: Mittwoch, 4. März 2015 15:48
An: Adrian Klaver
Cc: Tim Semmelhaack; pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Copy Data between different databases
Hi Adrian:
On Wed, Mar 4, 2015 at 1:03 AM, Adrian Klaver mailto:adrian.kla...@aklaver.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.
