CODEWITHSUNDEEP
mysqlpostgresqldockercsv
BaoTrung Tran
BaoTrung Tran

Reputation: 460

How to export table postgres from docker to macos local

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.

Upvotes: 0

Views: 1497

Answers (1)

BertC
BertC

Reputation: 2666

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.

Upvotes: 2

Related Questions