Copying and populating a table from a local machine
Written by
Updated at November 10, 2023
You can both copy data from a table to a local file and populate a table with data from a local file using the psql interactive terminal.
Getting started
-
Install the dependencies:
sudo apt update && sudo apt install --yes postgresql-client
Copying data from a table
To copy table data to a file, run this command:
psql "host=c-<cluster_ID>.rw.mdb.yandexcloud.net \
port=6432 \
sslmode=verify-full \
dbname=<DB_name> \
user=<username> \
target_session_attrs=read-write" \
-c "\copy (SELECT * FROM <table_name>) to stdout(DELIMITER '<delimiter_character>')" \
>> <local_file_name>
Populating a table with data
Note
You need to create a table in advance. The number of columns and data types in a local file must match the Managed Service for PostgreSQL table parameters.
To populate your table with data from a local file, run this command:
cat <local_file_name> | \
psql "host=c-<cluster_ID>.rw.mdb.yandexcloud.net \
port=6432 \
sslmode=verify-full \
dbname=<DB_name> \
user=<username> \
target_session_attrs=read-write" \
-c "COPY <table_name> FROM stdin (DELIMITER '<delimiter_character>')"