Remote Data Transfers: Moving CSV Files onto a Postgres Server

When your database is hosted on a cloud instance, a dedicated data center, or a virtual machine, you can't point a SQL query at a local folder on your laptop. To import CSV data onto a remote Postgres server, you have to solve a logistical challenge: getting data across the network safely and efficiently.

This guide covers three approaches — streaming, file-first upload, and batch automation — plus security practices for transfers over the open internet. If you're on Amazon RDS specifically, the RDS-specific import guide covers the aws_s3 extension, which eliminates the network bottleneck entirely.

Method 1: The Stream Approach — No File Transfer Required

The most common way to import CSV data to a remote Postgres instance is the psql client-side \copy command. Instead of moving the file to the server's hard drive, psql reads the file on your local machine and pushes the data through the existing database connection.

psql -h your-remote-host.com -U db_user -d target_db \
  -c "\copy target_table FROM 'local_data.csv' WITH (FORMAT csv, HEADER true);"

This avoids the permission errors that plague server-side COPY — the database never needs to read a file on disk. For the full \copy syntax including column mapping and NULL handling, see the command-line import guide.

Method 2: The Move-Then-Load Approach — For Large Files

If you're dealing with multi-gigabyte files, network interruptions can kill a \copy stream halfway through, leaving your table in an incomplete state. The safer approach for large files is to transfer first with SCP, then run the faster server-side COPY command locally.

Step 1: Transfer via SCP

scp ./my_huge_data.csv remote_user@your-server-ip:/tmp/

Step 2: Load server-side

Once the file is on disk, SSH in and run the high-speed server-side COPY:

COPY target_table
  FROM '/tmp/my_huge_data.csv'
  WITH (FORMAT csv, HEADER true);

Server-side COPY is also the foundation of the bulk-loading optimizations covered in the millions-of-rows guide — combine SCP + COPY FREEZE + index rebuilds for peak throughput on large migrations.

Method 3: Automating Batch Transfers

If your remote workflow involves dozens of daily log files, running commands manually doesn't scale. A simple Bash loop handles the whole folder in a single execution — and is easy to wire into a nightly cron job:

#!/bin/bash
REMOTE_HOST="db.example.com"
DB_NAME="production_db"

for file in ./logs/*.csv; do
    echo "Pushing $file..."
    psql -h "$REMOTE_HOST" -d "$DB_NAME" \
      -c "\copy log_table FROM '$file' CSV HEADER"
done

For larger pipelines where each CSV might have a different structure, pure Bash starts to break down — you'd need per-file column mapping logic. That's the problem the staging strategy solves at scale.

Security: Encrypting the Transfer

When CSV data travels over the open internet, it can be intercepted in transit. Two practices cover the main attack surfaces:

# Open the tunnel (run in a separate terminal)
ssh -L 5433:localhost:5432 user@remote-server

# Then connect via the local tunnel port
psql -h localhost -p 5433 -d target_db \
  -c "\copy target_table FROM 'data.csv' CSV HEADER"

Expert Tip: Atomic Full-Refresh with TRUNCATE

When performing a full refresh on a remote table, network latency makes every command count. If a \copy transfer fails halfway through, you end up with a half-loaded table that's neither the old state nor the new one.

psql -h host -d db_name -c \
  "BEGIN;
   TRUNCATE target_table;
   \copy target_table FROM 'data.csv' CSV HEADER;
   COMMIT;"

If any row in the CSV fails a type check or constraint, the ROLLBACK fires automatically. This pairs well with the data validation approach in CSV import troubleshooting guide — validate the file locally before kicking off the remote transaction.