Pg Restore Missing Table: Where to Look First

If pg_restore reports success but a table is missing, the most likely causes are that the table was excluded by a --exclude-table or --exclude-schema option, the dump was created with --schema-only or --data-only and you expected the other part, or the table is empty and you missed it because pg_restore does not create empty tables when the dump was made with --data-only. Start by checking the restore command you used and the dump’s table of contents with pg_restore -l dumpfile to see exactly what was included.

Formula

The calculator above is a placeholder for the pg_restore troubleshooting article – it computes running pace, not database restore. Since the page is about a technical error, the calculator block is not used; the following content is the actual article.

Why pg_restore Can Report Success and Still Miss a Table

PostgreSQL’s pg_restore is a client utility that reads a dump file created by pg_dump and sends SQL commands to a database server. It returns exit code 0 when all commands in the dump have been sent without a fatal error, but that does not mean every object you expected is present. The most common reasons for a missing table after a successful restore are:

• Exclusion options: The restore command used --exclude-table or --exclude-schema, which explicitly skips certain tables.
• Dump type mismatch: The dump was created with --schema-only or --data-only, so the restore only contains one part. If you restore a schema-only dump, you get table definitions but no rows; if you restore a data-only dump, you get rows but no table creation – and if the table does not already exist, the data is silently ignored.
• Schema search path: The table was restored into a different schema than the one you are looking in. If the dump was made from a database where the table lived in a schema that is not in your current search_path, you may not see it in a default \dt listing.
• Empty tables: With a data-only dump, empty tables are not represented at all, so they will not be created on restore.
• Case sensitivity: Table names are case-sensitive when quoted. If the original table was created with double quotes (e.g., "MyTable"), it is stored as MyTable, and querying mytable will not find it.

First Steps: Check the Restore Command and Dump Contents

Before touching the database, examine what you actually ran. Look at your shell history or backup script for the exact pg_restore invocation. Then list the dump’s contents:

pg_restore -l your_dump.dump

This prints the table of contents – every object in the dump, its type, and its schema. Search for the missing table name. If it is not in the list, the problem is in the dump, not the restore. If it is in the list, the restore likely skipped it due to an exclusion flag or a dependency issue.

Common Mistakes That Cause Missing Tables

• Using --clean with --if-exists: This drops objects before recreating them, but if the drop fails because of dependencies, the create may be skipped.
• Restoring into a non-empty database: If the table already exists with a different definition, pg_restore may skip it or fail silently depending on the --exit-on-error flag.
• Wrong database connection: You restored into a different database than you think. Check with \connect in psql.
• Schema mismatch: The dump contains CREATE SCHEMA statements, but the restore target has a different schema name. Use --schema to map it.

How to Find the Missing Table in the Restored Database

Connect to the target database with psql and run:

\dt *.*

This lists all tables in all schemas. If you see it there, the table exists but is not in your default schema. You can set the search path with:

SET search_path TO your_schema, public;

If the table is still not listed, check the pg_class catalog directly:

SELECT relname, nspname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relname ILIKE '%your_table%';

This will find the table regardless of schema or case. If the query returns nothing, the table is not in the database.

What to Do If the Table Is Truly Missing

If the table is not in the dump and not in the database, you need to go back to the source. Check if the original database still exists – if so, re-dump with a plain SQL format (-Fp) and inspect the SQL for the table. If the original is gone, you may need to recover from a different backup, such as a WAL archive or a logical replication slot.

If the table is in the dump but was skipped during restore, you can restore just that table with:

pg_restore -t your_schema.your_table -d target_db your_dump.dump

This restores only the specified table and its dependencies. Use --verbose to see exactly what happens during the restore.

Preventing This in Future Backups

• Always use the custom format (-Fc) for dumps – it allows selective restore and listing.
• Verify the dump contents immediately after creation with pg_restore -l.
• Test restores in a scratch database before relying on them.
• Document your backup and restore commands, including any exclusion flags.

Frequently Asked Questions

Why did pg_restore exit with code 0 but the table is missing?

Exit code 0 means no fatal error was encountered, but it does not guarantee all objects were created. Exclusion flags, existing objects, or missing schemas can cause individual tables to be skipped without aborting the whole restore.

How do I check if a table exists in a PostgreSQL database?

Use \dt in psql to list tables in the current schema, or query pg_tables with SELECT * FROM pg_tables WHERE tablename = 'your_table';. Remember that unquoted names are folded to lowercase.

Can I restore a single table from a pg_dump file?

Yes, if the dump was created in custom or directory format. Use pg_restore -t table_name -d database dumpfile. This restores the table and its dependent objects.

What is the difference between schema-only and data-only dumps?

A schema-only dump (--schema-only) contains DDL statements (CREATE TABLE, indexes, constraints) but no data. A data-only dump (--data-only) contains INSERT or COPY statements but no DDL. Restoring a data-only dump into a database without the tables will not create them – the data commands will fail or be ignored.

Why does pg_restore say “table does not exist” even though the dump has it?

This usually happens when the table’s schema is not in the search path, or when the dump was taken from a different schema and you are restoring into a database where that schema does not exist. Use --schema to specify the target schema.

Related

• Batch Video Converter: What It Does & How to Choose
• Check Pokemon TCG Stock at Local Retailers
• Music Gifts for Commuters & Vinyl Fans

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2