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.
Table of Contents
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.