pg_restore: 'input file appears to be a text format dump. Please use psql.'

pg_restore: error: input file appears to be a text format dump. Please use psql. — the file was written by pg_dump in its default plain format, which is an SQL script, and pg_restore reads only the archive formats. Run the file with psql -f instead; the message is the fix.

The reverse mistake has its own message: a custom-format dump (pg_dump -Fc) fed to psql makes it refuse with The input is a PostgreSQL custom-format dump. Look at the first bytes to know which one you have: an archive starts with PGDMP, a plain dump with --.

The error

$ pg_restore -U postgres -d shop_copy shop.sql
pg_restore: error: input file appears to be a text format dump. Please use psql.

The Windows client says the same, before it connects to anything:

PS> & 'C:\Program Files\PostgreSQL\18\bin\pg_restore.exe' -l shop.sql
pg_restore: error: input file appears to be a text format dump. Please use psql.

Why it happens

pg_dump writes either a plain SQL script (the default, -Fp) or an archive: custom (-Fc), directory (-Fd) or tar (-Ft). Archives carry a table of contents, which is what lets pg_restore -l list them and restore single objects. A plain script has none of that; it is just statements for psql. pg_restore reads the start of the file, finds the SQL comment header instead of an archive signature, and stops with exit code 1 without touching the database.

The file name says nothing: .sql, .dump and .backup are conventions, not formats. In the other direction, psql 18 recognised the custom-format file both with -f and on standard input, printed its two-line hint and also exited with code 1.

The fix

$ head -c 5 shop.dump
PGDMP
$ head -n 3 shop.sql
--
-- PostgreSQL database dump
--

On Windows, Format-Hex shows the same bytes; the first line of its output, after the path header, was:

00000000   50 47 44 4D 50 01 10 00 04 08 01 01 00 2D 00 00  PGDMP........-..

A plain dump goes to psql, into an existing empty database:

$ createdb -U postgres shop_copy2
$ psql -U postgres -d shop_copy2 -q -f shop.sql
 set_config
------------

(1 row)

The one-row result is the dump's own set_config call, not an error; the customers table arrived with its row. A custom-format dump goes to pg_restore:

$ pg_restore -U postgres -d shop_copy shop.dump
$ psql -U postgres -d shop_copy -c '\dt'
              List of tables
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | customers     | table | postgres
 public | orders        | table | postgres
 public | orders_import | table | postgres
(3 rows)

If you control the backup, prefer -Fc: the same file can then be listed with pg_restore -l before you restore anything, as the Windows client did here.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4) on Docker 29.9.2 under Windows 11, with pg_dump, pg_restore and psql 18.4 inside it. The database shop held three small tables and was dumped twice: pg_dump -U postgres -d shop -f shop.sql and pg_dump -U postgres -d shop -Fc -f shop.dump. Each file was then handed to the wrong tool and the right one, restoring into empty databases. The Windows checks used pg_restore.exe 18.3 from a PostgreSQL 18 install on files copied out with docker cp. The names fix7-pg, shop, shop_copy and shop_copy2 are this test's own, and the container was removed afterwards.

Frequently asked

How do I restore a .sql file made by pg_dump?

A .sql file from pg_dump is a plain SQL script, so run it with psql into an existing empty database: createdb -U postgres newdb, then psql -U postgres -d newdb -f file.sql. pg_restore cannot read it and says so.

How do I tell whether a PostgreSQL dump is plain SQL or custom format?

Look at the first bytes. A custom-format archive starts with PGDMP; a plain dump starts with a comment line such as -- PostgreSQL database dump. On Linux use head -c 5, on Windows Format-Hex. pg_restore -l also lists the contents of an archive and refuses a plain file.

Why does psql say The input is a PostgreSQL custom-format dump?

The file was made with pg_dump -Fc, which writes an archive rather than SQL. Restore it with pg_restore -d targetdb file instead of psql.

More decoded errors in the Fixes category. For choosing a format and a routine in the first place, see PostgreSQL backup and restore for SQL Server DBAs.