ALTER_SYSTEM(7pg18)

✨ ALTER SYSTEM ✨

okay bestie so this is a page straight out of the PostgreSQL 18.6 docs, and it's about the ALTER SYSTEM command — the glow-up way to change your server config. let's get into it 💅

---

🎀 NAME 🎀

ALTER_SYSTEM — change a server configuration parameter

fr fr that's the whole vibe: tweak a server config param, no manual file editing required 🫶

---

🌸 SYNOPSIS 🌸

ALTER SYSTEM SET configuration_parameter { TO | = } { value [, ...] | DEFAULT }

ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL

---

💖 DESCRIPTION 💖

So ALTER SYSTEM is the icon for changing server configuration parameters across your entire database cluster. It's literally so much more convenient than the old-school method of manually editing the postgresql.conf file. not me opening a text editor in 2026 😭

What it actually does: ALTER SYSTEM writes the given parameter setting into the postgresql.auto.conf file, which gets read in addition to postgresql.conf. and if you set a parameter to DEFAULT, or you use the RESET variant, it removes that configuration entry from postgresql.auto.conf entirely. RESET ALL clears out all of those configuration entries. slay, clean slate ✨

Now the timeline of when your changes actually hit:

• Values set with ALTER SYSTEM are effective after the next server configuration reload
• OR after the next server restart if it's a parameter that can only be changed at server start (some params are just like that, main character behavior only 🎀)

Want to trigger a reload? You've got three options, pick your fighter:

• calling the SQL function pg_reload_conf()
• running pg_ctl reload
• sending a SIGHUP signal to the main server process

Permission check 💅: only superusers and users who've been granted ALTER SYSTEM privilege on a parameter can change it using ALTER SYSTEM.

🚨 Also — and this is important — since this command acts directly on the file system and cannot be rolled back, it is not allowed inside a transaction block or a function. no undo button, no take-backs, bestie. periodt.

---

🌈 PARAMETERS 🌈

• configuration_parameter — the name of a settable configuration parameter. the available ones are documented in Chapter 19.
• value — the new value of the parameter. values can be written as string constants, identifiers, numbers, or comma-separated lists of those, whichever fits the particular parameter. Values that are neither numbers nor valid identifiers must be quoted. and you can write DEFAULT to specify removing the parameter and its value from postgresql.auto.conf.
  • okay this next bit is niche but lowkey important: for some list-accepting parameters, quoted values will produce double-quoted output, which is how whitespace and commas get preserved. for others, you have to use double-quotes inside single-quoted strings to get that same effect. iykyk 🦋

---

🧚 NOTES 🧚

• This command can't be used to set data_directory, allow_alter_system, nor parameters that are not allowed in postgresql.conf (e.g., preset options). not up for discussion 💀
• See Section 19.1 for other ways to set the parameters.
• ALTER SYSTEM can be disabled by setting allow_alter_system to off, but heads up: this is not a security mechanism (it's explained in detail in the documentation for that parameter, go read it).

---

⭐ EXAMPLES ⭐

Set the wal_level:

ALTER SYSTEM SET wal_level = replica;

Undo that, restoring whatever setting was effective in postgresql.conf:

ALTER SYSTEM RESET wal_level;

see? iconic. ate and left no crumbs 👑

---

💜 COMPATIBILITY 💜

The ALTER SYSTEM statement is a PostgreSQL extension.

(translation: this is our thing, not standard SQL, we made it ourselves 💅)

---

🔥 SEE ALSO 🔥

SET(7), SHOW(7)

---

and that's the whole page, bestie!! this one was shipped in the PostgreSQL 18.6 docs and dated 2026. go forth and configure responsibly 🫶✨💖