How to export table postgres from docker to macos local

Question

I using docker and then install postgres . Now i want export one table from postgres and insert it into mysql. I using command step by step command look like:

On database postgres I access to docker container and running command

\copy users TO  '/Users/maclocaluser/abc.csv' WITH CSV HEADER DELIMITER ';'

But it thow me exception : /Users/maclocaluser/abc.csv: No such file or directory

But when i using tool dbever or pgadmin it export success. Not error.

And then when i want import into mysql using docker i using command :

LOAD DATA  LOCAL INFILE '/Users/maclocaluser/abc.csv'  INTO TABLE users  FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

It same thow me exception :

File '/Users/maclocaluser/abc.csv' not found (Errcode: 2 - No such file or directory) but it exist my dictory.

How to export and import it on mac . When i using ubuntu it run success. I using tool mysql or dbever it import success but command line is not.

BertC · Accepted Answer · 2021-05-11 08:03:20Z

2

Your postgres is in a Docker Container. That is a different machine than your mac, which is the Host. So, the Docker Container does not know a /Users/maclocaluser folder.

What you should do is map a directory in the docker container to a folder on the host (your Macbook). (See Volumes on Docker docs)

Then copy the data from postgres to a CSV in that directory on the container. And the Volumes will make sure it end up on your Mac.

As an example, see the docker-compose.yml below.

version: '3.9'

services:

  db:
    image: postgres:latest
    restart: "no"
    container_name: db
    volumes:
      - ./database:/var/lib/postgresql/data
      - ./transfer:/var/transfer:rw
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: root
      POSTGRES_USER: myrootpassword
      POSTGRES_DB: mydatabase

It starts a database and maps the database itself to your host folder called ./database. And it also maps the folder ./transfer to /var/transfer on your Docker Container.

Your Copy command will look like this:

\copy users TO  '/var/transfer/abc.csv' WITH CSV HEADER DELIMITER ';'

If you execute this command on Postgres, your abc.csv file should end up in ./transfer on your macbook.

answered May 11, 2021 at 8:03

BertC

2,74835 silver badges41 bronze badges

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

3 Comments

BaoTrung Tran Over a year ago

thanks you so much . but where to put my folder ./database . In /Users/maclocaluser right ? So i have a question : Why pgadmin and dbever export success it ?

BertC Over a year ago

If you have pgadmin running in docker also, it's on the same docker-network. In dbever you probably make a connection with port 5432. If you map the Docker-Container port 5432 to the Host-port 5432, then dbever can access the database on that port while it is running on the host.

BertC Over a year ago

Your folder ./database is on the same level as your docker-compose.yml file. Same for the ./transfer folder.

Collectives™ on Stack Overflow

How to export table postgres from docker to macos local

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related