Thread: Copy Data between different databases
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. So where is the difference between the -c and the -f option? Tim -- Semmelhaack(at)gmx(dot).de
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.klaver@aklaver.com
Hi Adrian:
On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver <adrian.klaver@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 triedpsql -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 aaaa > A
folarte@paqueton:~$ echo bbbb | cat < A
aaaa
folarte@paqueton:~$ echo aaaa > A
folarte@paqueton:~$ echo bbbb | cat < A
aaaa
( in this case the A file will simulate q2.sql, echo bbbb 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 bbbb) | cat
aaaa
bbbb
folarte@paqueton:~$ (cat A; echo bbbb) | cat
aaaa
bbbb
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.klaver@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
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 <folarte@peoplecall.com> wrote:Hi Adrian:On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver <adrian.klaver@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 triedpsql -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.sqlAs 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 aaaa > A
folarte@paqueton:~$ echo bbbb | cat < A
aaaa( in this case the A file will simulate q2.sql, echo bbbb 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 bbbb) | cat
aaaa
bbbbWhich, 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 db2Regards.Francisco Olarte.
So where is the difference between the -c and the -f option?
Tim
-- Semmelhaack(at)gmx(dot).de
--
Adrian Klaver
adrian.klaver@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
On 03/03/2015 10:09 AM, Francisco Olarte wrote: > Hi Adrian: > > On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@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 aaaa > A > folarte@paqueton:~$ echo bbbb | cat < A > aaaa > > ( in this case the A file will simulate q2.sql, echo bbbb 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 bbbb) | cat > aaaa > bbbb > > 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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org > <mailto:pgsql-general@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/__mailpref/pgsql-general > <http://www.postgresql.org/mailpref/pgsql-general> > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian:
On Wed, Mar 4, 2015 at 1:03 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
.... Yeah, they should, I' was not discussing you. I was pointing the SHELL line was incorrect,
Regards.
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.
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
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@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 <adrian.klaver@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.