Thread: copy file from a client app to remote postgres isntance

Hi all,

I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy meta-command in a psql command:

psql -U %s -p %s -d %s -f - <<EOT\n here hoes a \copy meta-command \nEOT\n

and executing it. But it's quite an unnatural way to write database code. Has anything changed in the last ten years? Or, is there a better way to copy file contents in a remote database?

Well, actually, just ordinary 3 tier architecture. Simple UI connected via restful API with backend written in php, which copies some data in a remote database, that's pretty much it.

пн, 7 нояб. 2022 г. в 20:30, Rob Sargent <robjsargent@gmail.com>:

On 11/7/22 09:57, Вадим Самохин wrote:

Hi all,

I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy meta-command in a psql command:

psql -U %s -p %s -d %s -f - <<EOT\n here hoes a \copy meta-command \nEOT\n

and executing it. But it's quite an unnatural way to write database code. Has anything changed in the last ten years? Or, is there a better way to copy file contents in a remote database?

There are bulk copy routines available.  What is your architecture? 

On 11/7/22 8:57 AM, Вадим Самохин wrote:
> Hi all,
> I have an application that must copy a local file in csv format to a 
> postgres table on a remote host. The closest solution is this one 
> (https://stackoverflow.com/a/9327519/618020 
> <http://stackoverflow.com/a/9327519/618020>). It boils down to 
> specifying a \copy meta-command in a psql command:
> 
> |psql -U %s -p %s -d %s -f - <<EOT\n here hoes a \copy meta-command \nEOT\n
> ||
> and executing it. B|ut it's quite an unnatural way to write database 
> code. Has anything changed in the last ten years? Or, is there a better 
> wayto copy file contents in a remote database?
> 

1) Set up postgres_fdw:

https://www.postgresql.org/docs/current/postgres-fdw.html

on local machine to point at table on remote machine and then \copy or 
COPY to local machine.

2) Copy the CSV file to remote machine and then do \copy or COPY there.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi all,

I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy meta-command in a psql command:

psql -U %s -p %s -d %s -f - <<EOT\n here hoes a \copy meta-command \nEOT\n

and executing it. But it's quite an unnatural way to write database code. Has anything changed in the last ten years? Or, is there a better way to copy file contents in a remote database?

On 2022-11-07 19:57:04 +0300, Вадим Самохин wrote:
> I have an application that must copy a local file in csv format to a postgres
> table on a remote host. The closest solution is this one (https://
> stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy
> meta-command in a psql command:
>
> psql -U %s -p %s -d %s -f - <<EOT\n here hoes a \copy meta-command \nEOT\n
>
> and executing it. But it's quite an unnatural way to write database
> code.

This looks like something you would use in another programming language
(maybe C or Python?) to construct a shell command.

Do you want do this once (from the shell) or from code?

If the former, starting psql and typing

\copy table_name from 'filename.csv' ...

Doesn't seem that unnatural to me.
(That just invokes COPY ... FROM STDIN on the server and feeds data to
it over the existing SQL connection.)

If it's the latter, your programming language's postgresql library
probably has a method for invoking copy.


> Has anything changed in the last ten years? Or, is there a
> better way to copy file contents in a remote database?

COPY is the fastest way to load data.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 2022-11-07 14:40:40 -0600, Ron wrote:
> On 11/7/22 10:57, Вадим Самохин wrote:
>     I have an application that must copy a local file in csv format to a
>     postgres table on a remote host. The closest solution is this one (https://
>     stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy
>     meta-command in a psql command:
>
>     psql -U %s -p %s -d %s -f - <<EOT\n here hoes a \copy meta-command \nEOT\n
>
>
>     and executing it. But it's quite an unnatural way to write database code. Has anything changed in the last ten
years?Or, is there a better way to copy file contents in a remote database? 
>
>
> I'd write a small Python script, using the csv module to read the data and
> psycopg2 to load it.

If you use insert statements it will be significantly slower (which may
not matter for small files or one-off actions). If you use copy_from()
you don't have to parse it (but then why use Python at all?)

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 11/9/22 10:17, Peter J. Holzer wrote:
> On 2022-11-07 14:40:40 -0600, Ron wrote:
>> On 11/7/22 10:57, Вадим Самохин wrote:
>>      I have an application that must copy a local file in csv format to a
>>      postgres table on a remote host. The closest solution is this one (https://
>>      stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy
>>      meta-command in a psql command:
>>
>>      psql -U %s -p %s -d %s -f - <<EOT\n here hoes a \copy meta-command \nEOT\n
>>      
>>
>>      and executing it. But it's quite an unnatural way to write database code. Has anything changed in the last ten
years?Or, is there a better way to copy file contents in a remote database?
 
>>
>>
>> I'd write a small Python script, using the csv module to read the data and
>> psycopg2 to load it.
> If you use insert statements it will be significantly slower (which may
> not matter for small files or one-off actions). If you use copy_from()
> you don't have to parse it (but then why use Python at all?)

If OP does not want to embed psql in his app, then he must find a different 
solution.  Python is such an option.


-- 
Angular momentum makes the world go 'round.

On 2022-11-09 12:57:23 -0600, Ron wrote:
> On 11/9/22 10:17, Peter J. Holzer wrote:
> > On 2022-11-07 14:40:40 -0600, Ron wrote:
> > > On 11/7/22 10:57, Вадим Самохин wrote:
> > >      I have an application that must copy a local file in csv format to a
> > >      postgres table on a remote host. The closest solution is this one (https://
> > >      stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy
> > >      meta-command in a psql command:
> > >
> > >      psql -U %s -p %s -d %s -f - <<EOT\n here hoes a \copy meta-command \nEOT\n
> > >
> > >      and executing it. But it's quite an unnatural way to write
> > >      database code. Has anything changed in the last ten years?
> > >      Or, is there a better way to copy file contents in a remote
> > >      database?
> > >
> > >
> > > I'd write a small Python script, using the csv module to read the data and
> > > psycopg2 to load it.
> > If you use insert statements it will be significantly slower (which may
> > not matter for small files or one-off actions). If you use copy_from()
> > you don't have to parse it (but then why use Python at all?)
>
> If OP does not want to embed psql in his app, then he must find a different
> solution.  Python is such an option.

Invoking a program written in Python is just as hard (or simple) as
invoking a program written in C (psql). But that Python
program is additional code in their project which has to be first
written and then maintained.

What they probably should do is write the code in the programming
language they are already using for their app. And as I wrote just using
copy (from within their app, not from psql or a python script or
whatever) is probably the simplest solution. But since the OP chose not
to tell us what programming language or library they use, it's hard to
be more specific.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"