Re: COPY to STDOUT and pipes

kevin kempter wrote:

> Any thoughts on what I'm doing wrong?

I suspect that pg_dump is going to do a better job than using psql to
generate the input for the remote load. pg_dump can dump single tables
and can use COPY style data formatting.

As for why your current command isn't working ... You omitted the SQL in
`file2.sql' that you use for the restore, which makes things harder. At
a guess I'd say the "stdin" the input copy is seeing is expected to be
the text directly following the COPY ... FROM command in the input file,
rather than the psql command's stdin.

I do have one suggestion that's ugly but may work if you can't figure
out what's going wrong with the existing method and you're not happy
with using pg_dump for some reason:

You could potentially insert psql \echo commands into the first psql
command, so the command you're using to extract the data produces a
valid sequence of SQL commands that the second psql can read from stdin
(instead of using -f to read a command file). So if `file1.sql' becomes:

\echo 'COPY tablename FROM STDIN;'
copy (
select
cust_id,
cust_name,
last_update_dt
from sl_cust
)
to STDOUT
with delimiter '|'
\echo '\\\.'

then you might be able to use a command line like:

psql -f file1.sql | psql -h newhost

(note that the second psql is reading the first one's stdout as its stdin).

--
Craig Ringer