How to RESET DATABASE Parameters in PostgreSQL

Galaxy Glossary

How do I reset database-level parameters in PostgreSQL?

ALTER DATABASE ... RESET removes database-level parameter overrides, returning settings to their default values.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does ALTER DATABASE ... RESET do?

It deletes custom parameter settings stored in pg_db_role_setting for a specific database, forcing PostgreSQL to fall back to cluster defaults defined in postgresql.conf.

When should I reset database parameters?

Use it after testing feature flags, lowering work_mem for heavy reports, or undoing per-database tweaks before promoting to production.

How do I reset a single parameter?

Run ALTER DATABASE dbname RESET parameter_name; The change is instant for new sessions; existing connections need reconnecting.

How do I reset multiple parameters at once?

List them comma-separated inside parentheses: ALTER DATABASE dbname RESET (work_mem, maintenance_work_mem);

Can I wipe all overrides?

Yes—use ALTER DATABASE dbname RESET ALL; This leaves the database entirely dependent on cluster-wide settings.

Example: reverting query planner tweaks

Suppose you lowered random_page_cost to speed up ad-hoc analytics on the analytics database. To undo:

ALTER DATABASE analytics RESET random_page_cost;

Example: ecommerce staging database

Your staging DB ecom_stg runs with extremely low shared_buffers and effective_cache_size. Before load tests:

ALTER DATABASE ecom_stg RESET ALL;

This ensures staging mimics production defaults.

Best practices for safe resets

Always record current settings with pg_settings or pg_dumpall --globals-only. Schedule resets during low-traffic windows for apps that maintain persistent connections.

How to verify the reset worked?

Connect again and query pg_settings: SELECT name, setting, source FROM pg_settings WHERE source = 'configuration file'; The parameter should no longer show database as the source.

Troubleshooting: reset has no effect

If the value stays the same, check for role-level overrides with ALTER ROLE ... SET, or session-level SET commands inside your application pooler.

Why How to RESET DATABASE Parameters in PostgreSQL is important

How to RESET DATABASE Parameters in PostgreSQL Example Usage


--Ecommerce case: Undo aggressive memory settings before Black Friday
ALTER DATABASE orders_db RESET (work_mem, maintenance_work_mem);

--Confirm
SELECT name, setting, source
FROM pg_settings
WHERE name IN ('work_mem','maintenance_work_mem');

How to RESET DATABASE Parameters in PostgreSQL Syntax


--Reset one parameter
ALTER DATABASE database_name RESET parameter_name;

--Reset several parameters
ALTER DATABASE database_name RESET (param1, param2, ...);

--Reset all parameters for a database
ALTER DATABASE database_name RESET ALL;

--Ecommerce example: restore default work_mem & random_page_cost
ALTER DATABASE shoppers_db RESET (work_mem, random_page_cost);

Common Mistakes

Mistake: Forgetting to reconnect existing sessions. Why wrong: old connections keep the overridden value. Fix: restart app pods or issue pg_terminate_backend for idle sessions.
Mistake: Using RESET ALL on production without backups. Why wrong: you lose deliberate optimizations. Fix: dump current pg_db_role_setting rows first, then apply RESET selectively.