Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010

    Question Unanswered: File transfer between client and server using postgres connection

    Hello anybody
    Is there a way to transfer files between client and server using a postgresql connection?
    I don't want to write my files into a postgresql table.
    I want to write them in the server's filesystem and get them back as needed.

    Thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Why don't you use the OS command (rcp,scp,...) ?
    F. Celaia
    DBA Oracle/MS-SQL/MySQLSybase/DB2/

  3. #3
    Join Date
    Apr 2010
    Thank you fadace, for paying attention to my question.
    I have a gui application that connects to a postgresql server,
    I want the client side user to be able to add files related to a table row
    without requiring him to provide a separate username (and/or) password,
    (e.g. for an sftp connection).

    Another problem is that giving access to another service adds more
    complexity to security management.

    Thanks again.

  4. #4
    Join Date
    Aug 2009
    Olympia, WA
    Like fadace, I'm not wild about the design. But you could use an untrusted pl like plpython to read/write the files and send as a bytea or base64 string.

  5. #5
    Join Date
    Jun 2004
    Arizona, USA
    possibly. It would have to take place in a roundabout fashion, though.

    I haven't tried this, but you should be able to accomplish the task by
    1. Store the file contents as a large object (temporarily) on the server
      1. Read the file on the client into a byte buffer
      2. Using low-level libpq access from your client app, create a large object on the server
      3. Store the file buffer contents in the large object
    2. Use the SQL server-side lo_export function, create a file from the large object on the server
    3. Store the file path and any other descriptive information in a table on the server, for later retrieval
    4. Delete the large object on the server with the lo_unlink function

    To copy the file from the server to the client, you would reverse this process. First, get the path info from your directory table, import the file as a large object with the server-side functions, then using the client app, retrieve the file from large object storage and save as a local file on the client. Finally, kill the large object on the server.

    I have to ask, since you're using the large object facilities of pg, (including the client access via libpq,) to temporarily store the file as a largeobject, why not store the files as large objects in pg instead of in the server filesystem? It would be a lot less convoluted.
    Last edited by loquin; 04-15-10 at 16:42.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  6. #6
    Join Date
    Apr 2010
    Thanks a lot for your answers, I need some time to understand them.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts