Re: COPY to/from non-local file

On Jun 27, 2007, at 9:32 AM, Jaime Silvela wrote:

> I've been looking around for this functionality:
> Is it possible to use COPY with a file that doesn't reside in the
> DB's filesystem? I know there is sort of a solution in using COPY
> from stdin/ COPY to stdout, however that depends on calling the
> psql command, which breaks the flow of control of the programs I'm
> trying to write.
>
> Here's the story: I have a Ruby on Rails application which sits on
> server A, and connects to a Postgres Database running on B. Using
> the regular flow, the rails code writes into various tables, and
> then generates a table that needs to be exported into a file which
> will be used as input into a separate program, a simulator, and
> kept in the filesystem of the Rails server. Using the COPY command
> would entail either of
> a) Using COPY to generate the file on server B, then transfer to A
> - but how? ftp? I want to reduce this type of coupling
> b) Using COPY TO STDOUT from psql, called in the Rails code with a
> backtick, then gathering the output and filing it. - but this
> solution depends on having psql in the path of the Rails server,
> reintroducing the server credentials, and from a programming point
> of view is kind of ugly.
> c) The Postgres driver for Rails tries to give an interface to the
> COPY command using "raw_connection", "getline" and "endcopy", but
> it's quite brittle, so I'm avoiding it altogether.
>
> At the moment I'm avoiding those solutions, and instead get the
> table into Rails space with a CURSOR on a SELECT, then simply write
> the file in Rails, one line at a time. However, the tables I'm
> writing are pretty big, and the performance is so much worse than
> with COPY...
>
> Any suggestions?
> Thanks
> Jaime

The way we usually handle situations similar to this is to use
network mounts of directories that are visible from both servers
using, say, nfs.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com