Can Postgres Java client COPY query write to local hard drive?

Question

I have two Docker containers. One is running a Spring Boot application. The other is running Postgres.

I am trying to get three tables from Postgres onto the Java server in a format that I can import into pandas (CSV, most likely?), but I am having a tough time.

I can't use ProcessBuilder to run pg_dump or psql, because they are not available in the Java image.

I know there is a way to use the docker executable and socket installed on the host machine, but that's a logistical problem because of many existing servers already configured. It would be greatly preferable to solve this issue in code without having to make changes to the o/s or docker-compose.yml.

I thought I could use the Postgres COPY query, but it seems that it is writing the files onto the Postgres container's hard drive, not the Java container's hard drive.

Is there any way (scp? ftp?) to transfer the COPY'ed files from one container to the other?

Maybe a shared volume between the two containers could work, but that requires hand-editing the docker-compose.yml of all the deployed servers.

How can I get a CSV dump (or any other pandas-friendly format) of these tables from the Postgres container over to the Java container using only Java code?

Answer 1

It turns out the COPY query can be used to get the data to the client. Instead of a file, you specify STDOUT. Note that you have to use CopyManager rather than Statement.

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

        CopyManager copyManager = new CopyManager(connection.unwrap(BaseConnection.class));

        try (PrintWriter writer = new PrintWriter(new FileWriter(filePath))) {
            copyManager.copyOut("COPY " + tableName + " TO STDOUT WITH (FORMAT CSV, HEADER)", writer);
        }