COPY data between localhost and PostgreSQL running with Docker

by

A short article showing how to easily use the COPY command to move data between localhost and a PostgreSQL database running with Docker.

With the COPY command PostgreSQL povides an easy way to move data between the database and the local file system. In this article we show how to use this command when PostgreSQL is running with Docker. This is especially useful if you want to move large amounts of data, e.g. populating a database with mass-data.

Mounting a data transfer volume to the PostgreSQL container

In this article I assume you are familiar on how to run PostgreSQL with Docker using a separate user to manage permissions for local mounted volumes. In our example the user is named postgres and has ID 1002.

To be able to transfer data between PostgreSQL in Docker and the host file system, we’ll need a local directory mounted as a volume to the database container. In this article we’ll use /tmp/postgres for that. Any other directory would be good too as long as the PostgreSQL Docker container can access it.

First, we’ll create the local directory with appropriate permissions. This may require root privileges on your system.

$ cd /tmp
$ mkdir postgres
$ chown postgres:postgres postgres/

Having that, let’s mount the new directory as a volume to the PostgreSQL container. For that we’ll add the option -v /tmp/postgres:/tmp/postgres:Z to the Docker run command – for details refer to the guide on running PostgreSQL with Docker. This maps /tmp/postgres from the Docker container to our locally created /tmp/postgres directory.

Note we make use of the “:Z” option for the mounted volume here to overcome potential issues with SELinux. You might not need that depending on you SELinux configuration. For more details also refer to the corresponding section in our MongoDB docker guide.

The final command to launch PostgreSQL with local storage (recommended) and the mounted data transfer directory for COPY’ing data is:

$ docker run -d \
  --name mypostgres \
  --user 1002 \
  -e POSTGRES_PASSWORD=Test123$ \
  -e PGDATA=/var/lib/postgresql/data/pgdata \
  -v /var/db/postgres:/var/lib/postgresql/data:Z \
  -v /tmp/postgres:/tmp/postgres:Z \
  -p 5432:5432 \
  postgres:16.2

That’s it – you now have a PostgreSQL database running in Docker with directory /tmp/postgres mounted to localhost, ready for transferring data.

Extract data to localhost with COPY TO

First, we’ll use COPY to transfer data from a PostgreSQL table to localhost. For that we connect to the database running with Docker and create a simple table tasks having two columns. Then we insert some rows and use COPY to extract the data to a local CSV file. 

$ psql -h localhost -p 5432 -U postgres          
Password for user postgres: 

postgres=# CREATE TABLE tasks (id bigint PRIMARY KEY, description varchar(100));
CREATE TABLE
postgres=# INSERT INTO tasks VALUES (1, 'My first Task');
INSERT 0 1
postgres=# INSERT INTO tasks VALUES (2, 'Task Nr. 2');
INSERT 0 1
postgres=# COPY tasks TO '/tmp/postgres/export.csv' DELIMITER ',' CSV HEADER;
COPY 2
postgres=#

Back on localhost after logging out from PostgreSQL we can verify that the data has been written to export.csv in the directory /tmp/postgres of the local file system.

postgres=# quit
$ cat /tmp/postgres/export.csv 
id,description
1,My first Task
2,Task Nr. 2

Brilliant, that’s working as expected.

Insert data from localhost with COPY FROM

Next, we’ll go the other way round and insert data from a local CSV file into a table. For that we place a file import.csv in the transfer directory on localhost.

$ cat /tmp/postgres/import.csv 
id,description
100,Task-100
200,Task-200
300,Task-300

Having that, we connect back again to our PostgreSQL database, truncate the tasks table and populate it with the data from the import file using the COPY command.

$ postgres psql -h localhost -p 5432 -U postgres
Password for user postgres: 

postgres=# TRUNCATE TABLE tasks;
TRUNCATE TABLE
postgres=# COPY tasks FROM '/tmp/postgres/import.csv' DELIMITER ',' CSV HEADER;
COPY 3
postgres=# SELECT * FROM tasks;
 id  | description 
-----+-------------
 100 | Task-100
 200 | Task-200
 300 | Task-300
(3 rows)

postgres=#

That’s already it. We’ve successfully moved data between the local file system and our dockerized PostgreSQL back and forth using the COPY command.

Useful links