PostgreSQL: Using quote_all_identifiers to prevent major version upgrade failures

A technical deep dive explains how mismatched keyword lists between pg_dump and the PostgreSQL server can cause silent data integrity issues during upgrades. When a column name becomes a reserved keyword in a newer version, failing to use the --quote-all-identifiers switch can result in broken indexes, invalid check constraints, and malfunctioning row-level security policies. The feature works via a combination of a command-line switch for the client and a GUC for the server to ensure all identifiers are quoted consistently.

The risk of mismatched keyword lists

• The Mechanism: pg_dump uses fmtId() while the server uses quote_identifier(). If versions differ, their keyword lists may not match.
• The Failure Mode: If a column name (e.g., system_user) is unreserved in the old server but reserved in the new version, the server may deparse expressions without quotes.
• Silent Corruption: Unlike index failures which throw errors, CHECK constraints and Row-Level Security (RLS) policies may load successfully but behave incorrectly because the unquoted identifier is interpreted as a function or keyword.
• Scope Limitation: The switch does not protect code stored as strings, such as PL/pgSQL function bodies, which are reparsed only upon execution.

Why it matters

Database administrators performing major version upgrades should adopt the --quote-all-identifiers flag to ensure schema stability and prevent silent logic errors in security policies.