Back Up and Restore PostgreSQL via SSH Tunnel

Need to back up a PostgreSQL database on Sliplane? This guide shows you how to securely create and restore backups using an SSH tunnel. This method keeps your database private while giving you full access without exposing it publicly.

What You'll Learn

In this guide, you'll:

• Set up an SSH tunnel service in Sliplane
• Create backups using pg_dump over the tunnel
• Restore databases using pg_restore
• Handle different backup formats and restore options

Note: If your database is already public and your IP is whitelisted, you can connect directly without an SSH tunnel.

Prerequisites

Before you start, make sure you have:

• PostgreSQL client tools installed: pg_dump, pg_restore, psql
• Your Sliplane PostgreSQL database credentials
• A Sliplane project with a running PostgreSQL service

Step 1: Create an SSH Tunnel Service

First, create a dedicated SSH tunnel service in your Sliplane project:

1. In your Sliplane dashboard, go to your project
2. Click Add Service → Presets → SSH Tunnel
3. Use these settings:
   • Image: ghcr.io/sliplane/docker-ssh:latest
   • Environment Variables:
     • HOST=0.0.0.0
     • PORT=2222
     • ROOT_PASSWORD=choose a strong password
4. Deploy the service and note its public domain (e.g., ssh-tunnel-123.sliplane.app)

Step 2: Establish the SSH Tunnel

Open a terminal and create a local tunnel to your Sliplane database:

ssh -p 2222 \
  [email protected] \
  -L 5433:postgres-service-name.internal:5432 \
  -N

Replace postgres-service-name.internal with your PostgreSQL service's internal hostname. Keep this terminal window open during the backup/restore process.

Step 3: Test the Connection

Verify that the tunnel works before creating backups:

PGPASSWORD='your-password' \
psql -h 127.0.0.1 -p 5433 -U your-user -d your-database \
-c 'SELECT version();'

If you see the PostgreSQL version, your tunnel is working correctly.

Step 4: Create a Backup

Now that your SSH tunnel is working, it's time to create a backup. PostgreSQL offers multiple backup options through pg_dump, each with different use cases and advantages.

Custom Archive (Recommended)

This format is compressed, supports parallel restores, and allows selective restores:

PGPASSWORD='your-password' \
pg_dump \
  -h 127.0.0.1 -p 5433 \
  -U your-user -d your-database \
  -Fc \
  -f backup-$(date +%F).dump

Plain SQL Format

Easy to read and edit, but slower to restore:

PGPASSWORD='your-password' \
pg_dump \
  -h 127.0.0.1 -p 5433 \
  -U your-user -d your-database \
  -f backup-$(date +%F).sql

Backup Specific Objects

Backup only specific schemas or tables:

# Backup a specific schema
pg_dump -n schema_name -h 127.0.0.1 -p 5433 -U your-user -d your-database -f schema-backup.dump

# Backup specific tables
pg_dump -t table1 -t table2 -h 127.0.0.1 -p 5433 -U your-user -d your-database -f tables-backup.dump

Step 5: Restore the Backup

With your backup file ready, you can now restore it to your PostgreSQL database. The restore process depends on your backup format and whether you're restoring to an existing database or creating a new one.

Restore Custom Archive

To an existing database:

# Create the database if it doesn't exist
PGPASSWORD='your-password' \
createdb -h 127.0.0.1 -p 5433 -U your-user target-database

# Restore the backup
PGPASSWORD='your-password' \
pg_restore \
  -h 127.0.0.1 -p 5433 \
  -U your-user -d target-database \
  --clean --if-exists \
  --no-owner --no-privileges \
  backup-YYYY-MM-DD.dump

Create Database During Restore

PGPASSWORD='your-password' \
pg_restore -C \
  -h 127.0.0.1 -p 5433 -U your-user \
  -d postgres \
  backup-YYYY-MM-DD.dump

Parallel Restore (Faster)

For large databases, use parallel processing:

PGPASSWORD='your-password' \
pg_restore -j 4 \
  -h 127.0.0.1 -p 5433 -U your-user -d target-database \
  backup-YYYY-MM-DD.dump

Restore Plain SQL

PGPASSWORD='your-password' \
psql -h 127.0.0.1 -p 5433 -U your-user -d target-database \
-f backup-YYYY-MM-DD.sql

This method gives you secure, reliable access to your PostgreSQL database on Sliplane for backups and restores while keeping your infrastructure secure.