DEV Community

minnogit
minnogit

Posted on • Edited on

Stop Importing Large SQL Dumps Through SSH Tunnels: A More Reliable Approach

#devops #ssh #postgres

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
Enter fullscreen mode Exit fullscreen mode

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 depends on your Internet connection is the file upload.

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

Even if your laptop is disconnected, the database continues processing the dump without interruption.

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
Enter fullscreen mode Exit fullscreen mode

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

The database never needs to be exposed to the Internet.

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
Enter fullscreen mode Exit fullscreen mode

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

Any interruption breaks the import.

With the upload-and-execute approach, the network is only used once:

Upload dump
      │
      ▼
Execute locally
Enter fullscreen mode Exit fullscreen mode

Once the upload completes, the database reads the SQL file directly from its local disk.

This makes the execution:

  • independent of the developer's network connection;
  • significantly faster;
  • much more reliable.

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, avoiding the need to restart large uploads from scratch.

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

Automation

I eventually wrapped this workflow in a Bash script that automates the entire process:

  • uploads the dump to the bastion host;
  • transfers it to the database server over the private network;
  • executes psql locally on the database host;
  • verifies each step;
  • removes temporary files when the import completes successfully.

The script is fully parameterized, making it reusable across different environments without exposing the database directly to the Internet.

The important takeaway, however, isn't the script itself.

It's the workflow.

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
Enter fullscreen mode Exit fullscreen mode

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 backup.dump
Enter fullscreen mode Exit fullscreen mode

The custom format supports parallel restores and is usually faster than executing a plain SQL script.

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

The biggest lesson from this experience wasn't about Bash scripting or PostgreSQL.

It was about separating data transfer from data execution.

By treating them as two independent phases, the import process becomes:

  • more reliable;
  • easier to automate;
  • independent of the developer's network stability;
  • compatible with any infrastructure that uses a bastion host.

Whether your database runs on AWS, Azure, Google Cloud, or an on-premises private network, the same principle applies.

Sometimes the most effective optimization isn't making the import faster.

It's making sure it can finish successfully.

Top comments (0)