When transferring data between two databases, I would like to know if there is any disadvantages in the following approach:
psql -h [HOST1] -U postgres -d [DB1] -c [SQL1] | psql -h [HOST2] -d [DB2] -U postgres -c [SQL2]
[SQL1]="\copy (SELECT [FIELDS_SUBSET] FROM [TABLE_NAME]) TO STDOUT" [SQL2]="\copy [NEW_TABLE_NAME]( [FIELDS_SUBSET] ) FROM STDIN"
Basically, I extract some data from DB1 to stdout and then I immediately read it from stdin in order to import this data into DB2. Both databases are in different networks. This approach removes the need of an intermediate file.
In this context, I would like to know if this strategy has any drawbacks if compared to using intermediate files. For instance, is this approach suitable for transfering large volumes of data (GBs of data)?
Also, by using COPY with STDIN and STDOUT, do I still have to worry about the COPY command caveats? (Reference)