Re: [GENERAL] Copy Data between different databases

[Tim Semmelhaack]

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.

 

Re: [GENERAL] Copy Data between different databases

[Jim Nasby]

On 3/3/15 8:18 AM, Tim Semmelhaack wrote:

When I run a much simpler version of the query with the -c "Select .."
option it works. Because the sql-scripts are quite long, I don't to do it
without the -f option.


When you say quite long... are you trying to do multiple commands in q1 
or q2? As in, do you have more than just a single COPY statement in 
either file? Because the odds of that working are not very good...

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Copy Data between different databases

[Francisco Olarte]

Hi Adrian:

On Wed, Mar 4, 2015 at 1:03 AM, Adrian Klaver 
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.

Re: [GENERAL] Copy Data between different databases

[Adrian Klaver]

On 03/03/2015 10:09 AM, Francisco Olarte wrote:

Hi Adrian:

On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote:

On 03/03/2015 06:18 AM, Tim Semmelhaack wrote:

Hi,

I want to copy data between two servers (Version 9.1 and 9.4)

I've tried

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

Both sql-scripts include the COPY (SELECT ...) TO STDOUT or COPY
(SELECT
...) TO STDIN
As a result nothing is copied.

When I run a much simpler version of the query with the -c
"Select .."
option it works. Because the sql-scripts are quite long, I don't
to do it
without the -f option.


Have you tried?:

psql -h host1 -U user1 -d db1 < /q1.sql | psql -h host2 -U user2 -d
db2 < /q2.sql


​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

"
-f filename
--file=filename

Use the file filename as the source of commands instead of reading 
commands interactively. After the file is processed, psql terminates. 
This is in many ways equivalent to the meta-command \i.


If filename is - (hyphen), then standard input is read.

Using this option is subtly different from writing psql < filename. 
In general, both will do what you expect, but using -f enables some nice 
features such as error messages with line numbers. There is also a 
slight chance that using this option will reduce the start-up overhead. 
On the other hand, the variant using the shell's input redirection is 
(in theory) guaranteed to yield exactly the same output you would have 
received had you entered everything by hand."


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



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.




folarte@paqueton:~$ echo  > A
folarte@paqueton:~$ echo  | cat < A


( in this case the A file will simulate q2.sql, echo  is simulating
the first psql command and cat is simulating the second psql command ).
You are redirecting the second psql input twice, one with | other with
<. A simple variant is:

folarte@paqueton:~$ (cat A; echo ) | cat



Which, translating back to psql, should be:

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

Regards.
 Francisco Olarte.









So where is the difference between the -c and the -f option?

Tim
-- Semmelhaack(at)gmx(dot).de






--
Adrian Klaver
adrian.kla...@aklaver.com 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/__mailpref/pgsql-general






--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Copy Data between different databases

[Ryan King]

Have you considered using dblink() or foreign data wrappers to transfer the 
data? 
You can do a select from source, insert into target using one of these methods.

RC

> On Mar 3, 2015, at 12:09 PM, Francisco Olarte  wrote:
> 
> Hi Adrian:
> 
> On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver  > wrote:
> On 03/03/2015 06:18 AM, Tim Semmelhaack wrote:
> Hi,
> 
> I want to copy data between two servers (Version 9.1 and 9.4)
> 
> I've tried
> 
> ​​psql -h host1 -U user1 -d db1  -f /q1.sql | psql -h host2 -U user2 -d db2 -f
> /q2.sql
> 
> Both sql-scripts include the COPY (SELECT ...) TO STDOUT or COPY (SELECT
> ...) TO STDIN
> As a result nothing is copied.
> 
> When I run a much simpler version of the query with the -c "Select .."
> option it works. Because the sql-scripts are quite long, I don't to do it
> without the -f option.
> 
> Have you tried?:
> 
> psql -h host1 -U user1 -d db1 < /q1.sql | psql -h host2 -U user2 -d db2 < 
> /q2.sql
> 
> ​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:
> 
> folarte@paqueton:~$ echo  > A
> folarte@paqueton:~$ echo  | cat < A
> 
> 
> ( in this case the A file will simulate q2.sql, echo  is simulating the 
> first psql command and cat is simulating the second psql command ). You are 
> redirecting the second psql input twice, one with | other with <. A simple 
> variant is:
> 
> folarte@paqueton:~$ (cat A; echo ) | cat
> 
> 
> 
> Which, translating back to psql, should be:
> 
> (cat q2.sql; ​psql -h host1 -U user1 -d db1  -f /q1.sql) | psql -h host2 -U 
> user2 -d db2 
> 
> Regards.
> Francisco Olarte.
> 
> 
> 
> 
> 
> 
> 
>  
> 
> 
> So where is the difference between the -c and the -f option?
> 
> Tim
> -- Semmelhaack(at)gmx(dot).de
> 
> 
> 
> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org 
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general 
> 
> 

Re: [GENERAL] Copy Data between different databases

[Francisco Olarte]

Hi Adrian:

On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver 
wrote:

> On 03/03/2015 06:18 AM, Tim Semmelhaack wrote:
>
>> Hi,
>>
>> I want to copy data between two servers (Version 9.1 and 9.4)
>>
>> I've tried
>>
>> ​​
>> psql -h host1 -U user1 -d db1  -f /q1.sql | psql -h host2 -U user2 -d db2
>> -f
>> /q2.sql
>>
>> Both sql-scripts include the COPY (SELECT ...) TO STDOUT or COPY (SELECT
>> ...) TO STDIN
>> As a result nothing is copied.
>>
>> When I run a much simpler version of the query with the -c "Select .."
>> option it works. Because the sql-scripts are quite long, I don't to do it
>> without the -f option.
>>
>
> Have you tried?:
>
> psql -h host1 -U user1 -d db1 < /q1.sql | psql -h host2 -U user2 -d db2 <
> /q2.sql
>

​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:

folarte@paqueton:~$ echo  > A
folarte@paqueton:~$ echo  | cat < A


( in this case the A file will simulate q2.sql, echo  is simulating the
first psql command and cat is simulating the second psql command ). You are
redirecting the second psql input twice, one with | other with <. A simple
variant is:

folarte@paqueton:~$ (cat A; echo ) | cat



Which, translating back to psql, should be:

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

Regards.
Francisco Olarte.









>
>
>> So where is the difference between the -c and the -f option?
>>
>> Tim
>> -- Semmelhaack(at)gmx(dot).de
>>
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Copy Data between different databases

[Adrian Klaver]

On 03/03/2015 06:18 AM, Tim Semmelhaack wrote:

Hi,

I want to copy data between two servers (Version 9.1 and 9.4)

I've tried

psql -h host1 -U user1 -d db1  -f /q1.sql | psql -h host2 -U user2 -d db2 -f
/q2.sql

Both sql-scripts include the COPY (SELECT ...) TO STDOUT or COPY (SELECT
...) TO STDIN
As a result nothing is copied.

When I run a much simpler version of the query with the -c "Select .."
option it works. Because the sql-scripts are quite long, I don't to do it
without the -f option.


Have you tried?:

psql -h host1 -U user1 -d db1 < /q1.sql | psql -h host2 -U user2 -d db2 
< /q2.sql




So where is the difference between the -c and the -f option?

Tim
-- Semmelhaack(at)gmx(dot).de







--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general