[PostgreSQL] Save and load data via COPY and STDOUT/STDIN

pg_save_load_via_copy_and_stdout_stdin.sh

# Prepare test data (table size: 1M rows, ~87MB)
psql -t -X postgres://user:[email protected]:5432/dbname -c "
CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  age INTEGER NOT NULL
);
INSERT INTO customers(first_name,last_name,age) VALUES(CONCAT('FirstName',generate_series(1, 1000000)), CONCAT('LastName',round((random()*100)::integer,0)), round((random()*100)::integer,0));
"

# Save data from table using COPY and STDOUT (data.csv size ~37MB)
psql -t -X postgres://user:[email protected]:5432/dbname -c "COPY customers TO STDOUT (DELIMITER '|');" > data.csv

# Truncate table
psql -t -X postgres://user:[email protected]:5432/dbname -c "TRUNCATE TABLE customers;"

# Load data to table using COPY and STDIN
psql -t -X postgres://user:[email protected]:5432/dbname -c "\timing on" -c "BEGIN; SET LOCAL synchronous_commit TO OFF; COPY customers FROM STDIN (DELIMITER '|'); COMMIT;" < data.csv

# Let's watch the copying process in another terminal
psql -t -X postgres://user:[email protected]:5432/dbname -c "SELECT * FROM pg_stat_progress_copy;"

# Yah! Congratulation!
psql -t -X postgres://user:[email protected]:5432/dbname -c "SELECT * FROM customers LIMIT 10;"

Speed up your PostgreSQL bulk inserts with COPY
https://dev.to/josethz00/speed-up-your-postgresql-bulk-inserts-with-copy-40pk