How to export .csv file from Postgres container to host NodeJS container (may be like a stream)?

Question

Well. I have docker-compose.yaml with Postgres image (it is simple sample)

And I have NodeJS-script with raw SQL-query to Postgres:

 'COPY (SELECT * FROM mytable) to ‘/var/lib/postgresql/data/mytable.csv‘'

What happening? mytable.csv saved into Postgres container

What I need? Save mytable.csv to HOST MACHINE (or another container from docker-compose)

Anyway, context: I have big tables (1m+ rows) and it necessary to generate and save files by Postgres server. But this process (saving) will start via NodeJS script with "COPY"-query in other container / host machine.

Does you know information about how to do this things?

my docker-compose.yml:

version: "3.6"

services:
  postgres:
    image: postgres:10.4
    environment:
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=1234
    volumes:
      - postgres-storage:/var/lib/postgresql/data
    ports: 
      - "5432:5432"

UPDATE: I did some graphic in Miro for my process. The main problem in THIRD:I can't return .csv file to NodeJS or save it into NodeJS container. I can do 2 things:

Return rows for forming file in NodeJS (but NodeJS server will do it slowly)
Save .CSV file in Postgres container. But I need a .CSV file into a NodeJS container Schema with two containers that I need

Does this answer your question? Why query won't save in csv file while it's seems normal in postgresql console — Jim Jones
– Jim Jones, Commented Jul 31, 2020 at 12:50
Hello @Ivan Ratkin, Welcome to SO! Can you please edit your post and add the Dockerfile and docker-compose.yml, or at least the relevant extracts of those files? This can help others understand/reproduce the problem you are facing, increasing the changes for this question to receive an answer. Thank you! — Neo Anderson
– Neo Anderson, Commented Jul 31, 2020 at 13:23

Ivan Ratkin · Accepted Answer · 2020-08-01 15:00:31Z

0

Well, thanks for man, who linked this question with question about COPY TO STDOUT (sorry, didn't remember question ID).

So, problem solved by using COPY TO STDOUT and small npm-module pg-copy-streams

The code:

const client = new Client(config);
await client.connect();
const output = fs.createWriteStream('./output.csv');
const result = client.query(copyTo('COPY (select * from my_table) TO STDOUT WITH (FORMAT CSV, HEADER)'));
result.pipe(output);

So, Postgres will sent csv file in stream to host NodeJS script, and on the NodeJS-side we need to only write this stream in file without converting to csv.

Thanks!

answered Aug 1, 2020 at 15:00

Ivan Ratkin

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to export .csv file from Postgres container to host NodeJS container (may be like a stream)?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related