quote_all_identifiers is half of a pg_dump option. The other half is the --quote-all-identifiers command-line switch, and the reason the feature comes in two pieces is that a dump file is written by two different programs, each with its own opinion about which words are keywords.
Default off, context user, so any session can turn it on, and pg_dump does exactly that: give it the switch and one of the first things it sends the server is SET quote_all_identifiers = true. It lives in the “Previous PostgreSQL Versions” compatibility bucket alongside array_nulls, backslash_quote and escape_string_warning, but it is not one of the vestigial ones. It faces the other direction in time: the version you are about to upgrade to.
Two programs, two keyword lists
Whenever PostgreSQL turns a catalog entry back into SQL text (pg_get_viewdef(), pg_get_indexdef(), pg_get_constraintdef(), pg_get_expr() for defaults, generated columns, partition bounds and policies, and EXPLAIN while it is at it), every identifier passes through quote_identifier() in ruleutils.c. The rule is short. Quote the name if it starts with anything other than a lowercase letter or an underscore, if it contains anything other than lowercase letters, digits and underscores, or if it is a keyword in any category other than “unreserved”. Otherwise leave it bare, because SELECT "id" FROM "orders" is harder to read than SELECT id FROM orders, and view definitions get read by humans more often than you would think. With quote_all_identifiers = on, the first test fails for everything and every identifier comes out quoted.
pg_dump carries its own copy of that function (fmtId(), in fe_utils) with its own copy of the flag, and uses it for the parts of the dump it writes itself: CREATE TABLE, column lists, COPY headers, ALTER ... OWNER TO. The parts it does not write itself, it asks the server for. A view body is pg_get_viewdef(). An index is pg_get_indexdef(). A CHECK constraint is pg_get_constraintdef(). So a single CREATE TABLE in a dump file is a collaboration: pg_dump writes the column definitions and the server writes the constraint expressions in between them, and each side quotes keywords from its own list. When pg_dump and the server are the same major version the lists match and nothing is visible. When they are not, you get this:
1 CREATE TABLE public.audit ( 2 id integer NOT NULL, 3 "system_user" text NOT NULL, 4 action text, 5 CONSTRAINT audit_system_user_check CHECK ((system_user <> ''::text)) 6 );
That is PostgreSQL 18’s pg_dump dumping a table from a PostgreSQL 15 server. SYSTEM_USER became a reserved keyword in 16, so the 18 pg_dump quotes the column name on the third line. The CHECK expression two lines later came from the 15 server, which has never heard of it. Same statement, two keyword lists. The same table’s index came out as USING btree (system_user), and its row-level security policy as USING ((system_user = CURRENT_USER)).
Load that into 18 and three things happen. The index fails, loudly, because system_user is now parsed as a function call and functions in index expression must be marked IMMUTABLE. The constraint and the policy load without a murmur. They have simply been rewritten:
1 Check constraints: 2 "audit_system_user_check" CHECK (SYSTEM_USER <> ''::text) 3 Policies: 4 POLICY "own_rows" FOR SELECT 5 TO app_reader 6 USING ((SYSTEM_USER = CURRENT_USER))
The constraint now compares the authenticated identity of whoever is inserting (something like scram-sha-256:alice, or null over a trust connection) to the empty string, which is either true or null, and a CHECK lets both through, so the rule that the column may not be blank has quietly stopped existing. The policy now compares scram-sha-256:alice to current_user, which is alice, and a string with a colon in it never equals a bare role name, so nobody matches, and the application user who could see her own rows on 15 sees none on 18. No error, no warning, and the first symptom is a support ticket. Dump the same database with --quote-all-identifiers and all three objects arrive intact, because "system_user" is a column name on every version PostgreSQL has ever shipped and will be on every version it ever ships.
Why it is not the default
The feature exists because of bug #5488, June 2010: a table with a column named window could not be restored into 8.4, which had just reserved the word for window functions. The reporter asked that pg_dump quote every column name, always. Tom Lane declined: “Readability of the dump script is something that we put a nonzero value on.” What came out of the thread instead was an opt-in, committed by Robert Haas for 9.1 with the note “Design by Tom Lane”, and the design is the two-piece one above: a switch for the client half and a GUC for the server half, with the switch setting both.
That left two ways to get bitten. The one in the bug report is a word that a newer version reserves. The other is a word that a newer version un-reserves, in which case the newer pg_dump leaves it bare and the older server, which still thinks it is a keyword, rejects the dump’s own queries. Bug #8128 in 2013 was a schema named old, which 8.4 required to be quoted and which 9.0 had stopped treating as a keyword, so a 9.1 pg_dump left it bare and the 8.4 server rejected its queries. The reporter had hit it through Debian’s pg_upgradecluster and asked how to make pg_upgrade pass the switch, and Tom Lane made it unconditional there, on the stated grounds that nobody cares much about the readability of the dump an upgrade makes, with a back-patch to 9.1. If you upgrade with pg_upgrade you have been using this parameter all along. Bug #14152 in 2016 was a column named over, dumped from 9.3 by a 9.5 pg_dump, which fell over at COPY public.test (foo, over, bar) TO stdout because 9.4 had demoted the word. Turning the switch on automatically whenever the versions differ was floated in that thread, by Tom Lane himself, and left to drop; the outcome was the sentence in the pg_dump documentation recommending the switch for any dump that crosses a major version.
The un-reserving direction has gone quiet. over in 9.4 is the last time PostgreSQL demoted a word that either side would have quoted (a few unreserved keywords have been dropped since, which neither side quotes anyway), and every change since has been a promotion, lately for SQL/JSON: system_user and the json_array/json_object family in 16, json_table, json_value, json_query, json_exists and merge_action in 17, and json itself promoted in 17 from unreserved to the category the documentation calls “non-reserved (cannot be function or type)”. A json_value(jsonb, text) helper you wrote on 15 is a perfectly legal function and, called bare on 17, a syntax error or a successful call to something else entirely. Most of that family is harmless inside a dump, because since the 2018 CVE-2018-1058 fixes pg_dump runs its session with an empty search_path, which makes the server schema-qualify every user function and type it deparses, and public.json_value(...) is not a keyword no matter what follows the dot. What a dump cannot qualify is a column name standing alone in an expression, and in the currently supported range there is exactly one word that turns a bare column name into something else: system_user. That is why the example above is built around it. (The one before it was tablesample, in 9.5. If you are dragging a 9.4 database forward, hello, and the switch is for you too.)
What it cannot reach
The switch protects what the server deparses. It does not protect what the server stores as a string, and the big one there is PL/pgSQL. A function body is dumped verbatim, keywords and all, and reparsed on the new server at first call. This function does the right thing on 15 and silently returns the connecting user’s authentication identity on 18, with or without the switch:
1 create function who_did(p_id int) returns text language plpgsql stable as $$ 2 declare v text; 3 begin 4 select system_user into v from audit where id = p_id; 5 return v; 6 end $$;
The same function written with a BEGIN ATOMIC body (14 and later) comes through correctly, because that body is parsed at creation and lives in the catalog as a tree, so pg_get_function_sqlbody() deparses it with the rest and the switch quotes it like the rest. The application’s own SQL is in the same position as the PL/pgSQL body, obviously; nothing in a dump file can quote a query that has not been sent yet.
Nor should you set this anywhere but on a pg_dump invocation. Turn it on in postgresql.conf or with ALTER DATABASE ... SET and \d starts showing "text" and "jsonb" as column types (while integer stays bare, because "integer" is not a type, int4 is), index definitions become USING "btree" ("lower"("user")), every EXPLAIN reads Seq Scan on "orders", and quote_ident(), format('%I') and ::regclass::text all change their output. Anything that compares deparsed definitions between two servers will report that most of the database has changed. There is no benefit to any of that, because pg_dump sets the GUC for itself when it needs it.
So: pass --quote-all-identifiers to pg_dump and pg_dumpall whenever the output will be restored into a different major version, which for a logical upgrade is always, and leave it off for the same-version dumps you intend to read. Tom Lane was right that readability of a dump is worth something; the trick is to spend it on the dumps that anyone will read.