pg_restore only works with binary or directory format dumps, not plain text SQL files. Use psql to restore text dumps or recreate dumps using pg_dump with the -Fc (custom) format flag.
This error occurs when you try to use the pg_restore utility on a plain text SQL dump file. The pg_restore tool is designed exclusively for restoring backups created in PostgreSQL's custom (-Fc), directory (-Fd), or tar (-Ft) archive formats. Plain text SQL dumps, which are plain-text files containing SQL commands, must be restored using the psql command-line client instead. The error message indicates a format mismatch between the dump file and the restoration tool being used.
First, verify whether your dump is in plain text format by examining the first line:
head -1 /path/to/dump.sqlIf it shows SQL statements like "CREATE TABLE", "INSERT INTO", or PostgreSQL version comments, it is a plain text dump. If it shows binary data or has a specific header, it may be a custom format.
If you have a plain text SQL dump, restore it using psql instead of pg_restore:
psql -U username -d database_name < /path/to/dump.sqlAlternatively, use the -f flag:
psql -U username -d database_name -f /path/to/dump.sqlReplace "username" with your PostgreSQL user, "database_name" with the target database, and provide the correct path to your dump file.
To use pg_restore in the future, create dumps using the custom format with the -Fc flag:
pg_dump -Fc -U username -d source_database > backup.dumpThen restore using pg_restore:
pg_restore -U username -d target_database backup.dumpThe custom format provides better compression, faster restoration, and the ability to restore specific tables.
When using the custom format, you can speed up restoration with parallel jobs using the -j flag:
pg_restore -U username -d target_database -j 4 backup.dumpReplace "4" with the number of parallel jobs (typically matching your CPU core count for best performance).
If you are using backup automation (e.g., Laravel Backup, DBeaver, or other tools), update their configuration to:
- Use psql for text format dumps, or
- Configure the backup tool to create dumps in custom format (-Fc) and use pg_restore
Check your backup tool's documentation for the correct configuration option for dump format.
Plain text dumps are the default output of pg_dump and are portable across different PostgreSQL versions and architectures. However, for production environments, the custom format (-Fc) is recommended because it offers compression by default, supports parallel restoration with -j flag, and allows selective restoration of individual tables without loading the entire dump into memory. The directory format (-Fd) is also useful for very large databases as it creates a directory structure that can be backed up incrementally. When migrating between PostgreSQL versions, custom or directory formats are preferred as they provide better control over the restoration process.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL