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 <fola...@peoplecall.com> wrote: > > Hi Adrian: > > On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver <adrian.kla...@aklaver.com > <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: > > 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.kla...@aklaver.com <mailto:adrian.kla...@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> >
