Re: Copy Data between different databases

Hi Adrian:

On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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:

folarte(at)paqueton:~$ echo aaaa > A
folarte(at)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(at)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(dot)klaver(at)aklaver(dot)com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>