# Data Import/Export

> Data Import/Export guide for pgcli

---

LLMS index: [llms.txt](/llms.txt)

---

Export and import databases to dump files. Supports custom format (recommended) and plain SQL, with automatic gzip compression. Also supports piping between instances.

```bash
# Export to custom format (recommended, fastest restore)
pg export -i proj01 -o backup.dump

# Export to SQL format (human-readable)
pg export -i proj01 -o backup.sql

# Export with gzip compression (auto-detected from .gz extension)
pg export -i proj01 -o backup.dump.gz
pg export -i proj01 -o backup.sql.gz

# Export specific database
pg export -i proj01 -d mydb -o backup.dump

# Export with custom compression level (0-9)
pg export -i proj01 -o backup.sql.gz --compress=9

# Export with verbose output (show progress)
pg export -i proj01 -o backup.dump -v

# Import from custom format
pg import -i proj02 backup.dump

# Import from SQL format
pg import -i proj02 backup.sql

# Import compressed file (auto-detected from .gz extension)
pg import -i proj02 backup.dump.gz

# Import to specific database
pg import -i proj02 -d mydb backup.dump

# Import with cleanup (drop existing objects before restore)
pg import -i proj02 --clean backup.dump

# Import with verbose output
pg import -i proj02 backup.dump -v

# Pipe between instances (no temp file)
pg export -i proj01 | pg import -i proj02
pg export -i proj01 -d mydb | pg import -i proj02 -d mydb --clean

# Pipe across hosts via SSH
pg export -i proj01 | ssh user@remote "pg import -i proj02"
ssh user@remote "pg export -i proj01" | pg import -i proj02
ssh user@host1 "pg export -i proj01" | ssh user@host2 "pg import -i proj02"

# Work with remote databases via connection string (--dsn)
pg export --dsn postgres://user:pass@host:5432/mydb -o backup.dump
pg import --dsn postgres://user:pass@host:5432/mydb backup.dump --clean
pg export -i proj01 | pg import --dsn postgres://user:pass@host:5432/mydb
pg export --dsn postgres://user:pass@host1:5432/db1 | pg import --dsn postgres://user:pass@host2:5432/db2

# DSN can also be used for local instances (useful when ports differ from defaults)
pg export --dsn postgres://admin:pass@127.0.0.1:35432/mydb | pg import --dsn postgres://admin:pass@127.0.0.1:35433/mydb --clean
```

**Format comparison:**

| Feature | Custom (`.dump`) | SQL (`.sql`) |
|---------|------------------|--------------|
| Import speed | Faster (binary format) | Slower (text format) |
| File size | Smaller (compressed) | Larger (plain text) |
| Human-readable | No | Yes |
| Selective restore | Yes (specific tables) | No |
| Best for | Migration, backup, large databases | Version control, CI seed data, manual editing |

**Format detection:** Uses magic bytes (content-based) with extension fallback.
- Files starting with `PGDMP` → custom format
- `.sql` or `.sql.gz` → plain SQL format
- `.gz` extension or gzip magic bytes (`0x1f 0x8b`) → automatic decompression
- Extension is used as fallback if content detection fails

**Remote databases (--dsn):** Connect to any PostgreSQL instance using a connection string.
- No local PostgreSQL installation needed
- Works with local-to-remote, remote-to-local, and remote-to-remote migrations
- Supports all the same flags as local instances (`-o`, `-d`, `--clean`, `-v`, `--compress`)

**Note on existing data:** Importing into a database with existing tables will fail unless you use the `--clean` flag, which drops objects before restoring. Use `--clean` when importing into a database that already contains data.

**Use cases:**
- Migrate data between instances: `pg export -i proj01 | pg import -i proj02`
- Cross-host migration: `pg export -i proj01 | pg import --dsn postgres://user:pass@remote:5432/db`
- Share database with team: `pg export -i proj01 -o dump.dump.gz` (compressed, smaller file)
- Backup before major changes: `pg export -i proj01 -o pre-migration.sql.gz`
- CI/CD pipelines: export test data, import into fresh test databases