When transferring PostgreSQL database dumps between machines, you might encounter the frustrating error:

pg_restore: [archiver] input file does not appear to be a valid archive

This typically occurs when there's a mismatch between the dump format and restore command usage. Let's examine a concrete case where this happened:

pg_dump -a -f db.txt dbname
pg_restore -a -d dbname db.txt

The issue stems from using incompatible flags and formats. The -a flag in pg_dump creates a plain text SQL script (data-only dump), while pg_restore expects an archive format file (custom, directory, or tar format).

Solution 1: Using Plain SQL Format Consistently

If you want to use plain text SQL files:

# Dump data only
pg_dump -a -f db.sql dbname

# Restore using psql (not pg_restore)
psql -d dbname -f db.sql

Solution 2: Using Archive Format Properly

For archive formats that work with pg_restore:

# Create custom format dump
pg_dump -Fc -f db.dump dbname

# Restore properly
pg_restore -d dbname db.dump

Partial Restores

# Restore specific tables
pg_restore -t customers -d dbname db.dump

Schema-only Operations

# Dump schema only
pg_dump -s -Fc -f schema.dump dbname

# Restore schema with data
pg_restore --schema-only -d dbname full.dump

Before restoring, check your dump file format:

file db.dump
# Should return something like: db.dump: PostgreSQL custom database dump

For plain SQL files, you can inspect the first few lines:

head -n 5 db.sql
# Should show SQL statements

When working with large databases:

# Create compressed dump
pg_dump -Fc -Z 9 -f db.dump.gz dbname

# Restore compressed dump
gunzip -c db.dump.gz | pg_restore -d dbname

---

When migrating PostgreSQL data between servers, you might encounter this frustrating error:

pg_restore: [archiver] input file does not appear to be a valid archive

This typically happens when trying to restore a dump file created with pg_dump, especially when using different formats or incorrect commands.

The error occurs primarily because:

• Using plain-text format with pg_restore (which expects custom/archive formats)
• File corruption during transfer
• Version mismatch between PostgreSQL tools
• Incorrect command syntax

For data-only migration (-a flag), use these correct command sequences:

Option 1: Using Custom Format

# Dump with custom format
pg_dump -Fc -a -f db.dump dbname

# Restore
pg_restore -a -d dbname db.dump

Option 2: Using Plain Text Format

# Dump as plain SQL
pg_dump -a -f db.sql dbname

# Restore using psql (not pg_restore)
psql dbname < db.sql

After transferring dump files, always verify:

# For custom format
pg_restore -l db.dump

# For plain text
head -n 10 db.sql

If issues persist:

1. Check PostgreSQL versions match
2. Try binary transfer (scp/rsync) instead of FTP
3. Test with smaller datasets first
4. Consider using pg_dumpall for complete database clusters

Here's a complete example for production use:

# On source server
pg_dump -Fc -v -h source_host -U db_user -f /tmp/db_backup.dump db_name

# Transfer securely
scp /tmp/db_backup.dump user@dest_server:/tmp/

# On destination
pg_restore -v -h localhost -U db_user -d db_name /tmp/db_backup.dump