Re: Copy Data between different databases - Mailing list 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