Skip site navigation (1) Skip section navigation (2)

Re: COPY to/from non-local file

From: Erik Jones <erik(at)myemma(dot)com>
To: Jaime Silvela <JSilvela(at)Bear(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: COPY to/from non-local file
Date: 2007-06-27 15:53:00
Message-ID: E5552450-269E-4ACF-B40B-811AA85DACB8@myemma.com (view raw or flat)
Thread:
Lists: pgsql-general
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



In response to

pgsql-general by date

Next:From: CGDate: 2007-06-27 16:02:07
Subject: How do you handle shared memory corruption issues? (contrib/xml2)
Previous:From: Tomasz RakowskiDate: 2007-06-27 15:47:56
Subject: autovacumm not working ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group