Importing Large SQL Dumps into a Private PostgreSQL Database: Upload First, Then Run Locally

The Problem

If you've ever had to import a PostgreSQL SQL dump several hundred megabytes (or even several gigabytes) in size into a database hosted inside a private network, you've probably used an SSH tunnel:

ssh -L 5432:db.internal:5432 bastion

psql -h localhost -U admin mydb < dump.sql

At first glance it looks like the obvious solution.

Unfortunately, it has one major weakness: the entire import depends on your Internet connection.

If your laptop disconnects, your VPN drops, or your home connection experiences even a brief interruption after an hour of importing data, the SSH tunnel is closed and the import fails.

For large databases, this can mean wasting hours and potentially leaving the database in an inconsistent state if the import wasn't wrapped in a transaction.

---

A Better Strategy

Instead of streaming SQL through an SSH tunnel, separate the process into two independent phases:

1. Upload the dump file to the private network.
2. Execute the import locally on the database server.

This simple architectural change makes a huge difference.

The only operation that sends data over your Internet connection is the file upload.

Once the file reaches the database server, the import runs entirely inside the private network.

This doesn't make the import independent of your laptop on its own. If you start psql from your SSH session, a disconnection during the import still interrupts it. What changes is that the long and fragile part, uploading gigabytes over a home connection, becomes a separate step that can be retried and resumed. If the import itself takes hours, start it on the database server inside screen or tmux, so that it survives a disconnection. I described how in another article (in Italian).

---

Typical Architecture

The infrastructure looks like this:

Developer PC
      │
      │  rsync (compressed)
      ▼
Bastion Host
      │
      │  rsync / scp over private network
      ▼
Database Server
      │
      │  psql -f dump.sql
      ▼
PostgreSQL

The bastion host acts only as an entry point into the private network.

The database never needs to be exposed to the Internet.

On the database server, the import should run as a single transaction, so that a failure leaves the database as it was:

psql --single-transaction -v ON_ERROR_STOP=1 -f dump.sql mydb

--single-transaction wraps the whole file in BEGIN and COMMIT. ON_ERROR_STOP=1 makes psql stop at the first error and roll back: without it, psql keeps sending the rest of the file, every following statement fails because the transaction is already aborted, and you only find out at the end, after a long list of errors. This doesn't work if the dump contains its own BEGIN, COMMIT or ROLLBACK, or commands that cannot run inside a transaction block.

---

Why This Approach Is More Reliable

With the traditional SSH tunnel approach, every SQL statement travels through the entire network path:

Developer PC
      │
   Internet
      │
Bastion Host
      │
Private Network
      │
Database

For a multi-gigabyte dump, this connection may remain open for hours.

Any interruption breaks the import.

With the upload-and-execute approach, the dump crosses the Internet only once, for the upload. Once it completes, the database reads the SQL file directly from its local disk. Statements no longer travel over the Internet one by one, and an interrupted upload doesn't touch the database: nothing has been sent to it yet.

---

Why rsync?

Although scp works perfectly well, rsync offers two advantages when transferring SQL dumps:

• it compresses text files during transfer (-z), dramatically reducing upload times;
• it can resume interrupted transfers with --partial (or -P, which also shows progress), avoiding the need to restart large uploads from scratch. Without that option, rsync deletes the partially transferred file when the transfer is interrupted.

Since SQL dumps are plain text, compression ratios are often excellent.

---

Automation

I eventually wrapped this workflow in a Bash script. This is a reduced version, without the details of my environment (key paths, sudo, host names):

#!/usr/bin/env bash
# usage: import-dump.sh <dump.sql|dump.backup> <bastion> <db_host> <db_name> <db_user>
# Assumes the bastion can reach the database server over SSH.
set -uo pipefail

file="$1"; bastion="$2"; db_host="$3"; db_name="$4"; db_user="$5"
ext="${file##*.}"
# A fixed name lets rsync --partial resume an interrupted upload
tmp="/tmp/import_$(basename "$file")"

psql_cmd="psql --single-transaction -v ON_ERROR_STOP=1 -d $db_name -U $db_user"
case "$ext" in
  sql)
    exec_cmd="$psql_cmd -f $tmp" ;;
  backup|dump)
    # Convert to plain SQL without owners and grants the import user may not have
    exec_cmd="pg_restore --no-owner --no-privileges -f - $tmp | $psql_cmd -f -" ;;
  *)
    echo "Unsupported file type: .$ext" >&2; exit 1 ;;
esac

# 1. Upload to the bastion: the only step that sends the dump over the Internet
rsync -avz --partial "$file" "$bastion:$tmp" || exit 1

# 2. Copy over the private network, run the import, clean up
ssh -T "$bastion" <<REMOTE
rsync -av --partial "$tmp" "$db_host:$tmp" || exit 1
rm -f "$tmp"
ssh -n "$db_host" "$exec_cmd"
status=\$?
ssh -n "$db_host" "rm -f $tmp"
exit \$status
REMOTE

A few details:

• the temporary file has a fixed name, so running the script again after an interrupted upload lets rsync --partial resume it;
• .backup and .dump files are converted to plain SQL with pg_restore --no-owner --no-privileges and piped into psql, which avoids the ownership problems described below;
• the script waits for psql to finish, to print the result and remove the temporary files. This means the import is still tied to the SSH session.

I tested this version with two containers acting as bastion and database server: a .sql file, a .sql file with an error halfway (nothing was committed), and a custom-format dump of a table owned by another role. I also killed the SSH connection during a slow import: psql stopped, and nothing was committed.

---

Common Pitfalls

While implementing this process I encountered a few recurring issues that are worth mentioning.

PostgreSQL Peer Authentication

Many PostgreSQL installations use peer authentication for local connections.

In that case, the Linux user executing psql must match the PostgreSQL role.

Otherwise you'll receive errors such as:

FATAL: Peer authentication failed

---

Schema Ownership

If a previous import was executed with a different database user, schemas and tables may end up owned by another role.

Subsequent imports can then fail with permission errors even if the user appears to have the required privileges.

---

Minimal Cloud Images

Minimal Linux images often don't include rsync.

Installing it on the bastion host or database server is usually worthwhile, especially when transferring large SQL dumps over slower Internet connections.

---

What About pg_dump and pg_restore?

If you control both the export and the import, using PostgreSQL's custom dump format is generally preferable:

pg_dump -Fc database > backup.dump

pg_restore -d database -j 4 backup.dump

The custom format supports parallel restores (-j) and is usually faster than executing a plain SQL script. The -d option matters: without it, pg_restore doesn't restore anything and writes the equivalent SQL script to standard output.

However, many real-world scenarios involve receiving an existing .sql dump from another team, a customer, or a third-party application.

In those situations, changing the dump format isn't an option.

The upload-and-execute approach works regardless of how the SQL file was generated.

---

Final Thoughts

What worked for me was keeping data transfer and data execution as two separate steps. The upload is the part that suffers most from a bad connection, and it can be repeated and resumed. The import then runs next to the database, as a single transaction when the dump allows it, so it either completes or leaves nothing behind.

The same principle applies to any private network behind a bastion host.