pg_dump Produces Incomplete Dump

Scenario

A team uses pg_dump nightly for their application database. After a schema corruption incident, they restore from the dump. During restore, psql reports ERROR: relation "payments" does not exist and ERROR: role "app_user" does not exist. Post-restore, the application cannot connect because sequences are missing, permissions are gone, and extension-created objects are absent.

How to Identify

Conditions:

• pg_dump excluded certain schemas via --exclude-schema or missed tables owned by other roles
• pg_dump used --no-owner (drops owner info) or --no-privileges (drops ACLs)
• Restore user lacks CREATE privileges — some objects silently skipped
• Extension tables and functions missing because --exclude-schema=public excluded them
• Sequences missing because dump was --data-only but schema had SERIAL/BIGSERIAL columns
• pg_dump version mismatch: dumped with older pg_dump, restoring to newer PostgreSQL

Analysis Steps

-- Check what was actually dumped (list contents of dump file):
-- pg_restore --list backup.dump | grep -c "TABLE"    -- count table entries
-- pg_restore --list backup.dump | grep "SEQUENCE"    -- check sequences
-- pg_restore --list backup.dump | grep "ACL"         -- check permissions
-- pg_restore --list backup.dump | grep "EXTENSION"   -- check extensions

-- After restore: check what's missing
SELECT tablename FROM pg_tables WHERE schemaname = 'public'
EXCEPT
SELECT 'expected_table1' AS tablename UNION ALL SELECT 'expected_table2';

-- Check missing roles
SELECT rolname FROM pg_roles WHERE rolname IN ('app_user', 'reporting_user');

-- Check missing sequences
SELECT sequencename FROM pg_sequences WHERE schemaname = 'public';

-- Check extensions
SELECT extname, extversion FROM pg_extension;

-- Check database-level permissions
SELECT has_database_privilege('app_user', current_database(), 'CONNECT');

Pitfalls

• pg_dump only dumps the specified database — it does NOT dump roles, tablespaces, or other databases. Use pg_dumpall for complete cluster-level dumps including global objects.
• --no-owner and --no-privileges (often used for portability) strip all ownership and ACL information — objects will be owned by the restoring user only.
• pg_dump with --schema=public excludes all other schemas. Application objects in app schema will be missing.
• Extensions must be created before restoring extension-dependent objects. pg_dump includes CREATE EXTENSION but only if the restoring user has superuser privileges.
• pg_dump is transactionally consistent — it’s not an issue — but the dump only captures the state at the start of the dump, not after any concurrent DDL.

Resolution Approach

Use pg_dumpall for complete cluster backups including global objects (roles, tablespaces). Use --format=directory or --format=custom for pg_restore flexibility. Always restore with a superuser to ensure all permissions apply correctly.