## Intro PostgreSQL configuration mistakes can cause outages, degrade performance, or create security gaps that are hard to trace. This guide gives you practical, version-aware procedures to observe, change, validate, and roll back PostgreSQL settings safely. It is written for developers, DevOps engineers, and startup teams who manage PostgreSQL in production or staging. Every section links a common mistake to a concrete command, the expected output, the failure signal, and the recovery path. The goal is operational safety: never change a setting without knowing how to verify the result and how to undo it if things go wrong. You will learn how to: - Inventory your PostgreSQL version and topology before touching anything. - Use read-only commands to capture the current state. - Make the smallest possible change with a clear blast radius. - Validate the change with documented checks. - Roll back quickly when validation fails. - Build a repeatable operations checklist that reduces human error. All examples use placeholders such as , , and to keep commands safe to run in your environment. Never run a command from a blog post without checking its prerequisite and expected output first. ## Version and Environment Inventory Before changing any PostgreSQL setting, identify your installed version, deployment topology, and relevant prerequisites. A mistake made on PostgreSQL 12 may be harmless on PostgreSQL 15, and vice versa. Knowing the version and environment narrows the blast radius and informs the correct syntax for validation and recovery. ### 1.1 Identify the PostgreSQL version and server details Run the following read-only command from the PostgreSQL host or a client with access: psql -U -d -c "SELECT version();" Expected output looks like: PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.0, 64-bit For a more structured view, query pg_settings : SELECT name, setting, unit, context, vartype, source FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem', 'max_connections'); Example result: name | setting | unit | context | vartype | source ----------------+---------+------+------------+---------+-------------------- max_connections| 100 | | postmaster | integer | configuration file shared_buffers | 16384 | 8kB | postmaster | integer | configuration file work_mem | 4096 | kB | user | integer | default This tells you the current value, the effective unit, and where the setting comes from (configuration file, command line, or compiled-in default). Do not skip this step: if source is not configuration file , editing postgresql.conf will not affect the running server until the correct mechanism is used. ### 1.2 Capture the deployment topology Determine whether PostgreSQL runs on bare metal, a virtual machine, a container, or a managed service: - Bare metal or VM : check the OS and package manager. - Docker : run docker inspect to see mounted volumes and environment variables. - Managed (e.g., Amazon RDS, Google Cloud SQL) : many parameters are managed through the provider's API or console, not postgresql.conf . Example for Docker: docker ps --filter "name=postgres" --format "{{.Names}}\t{{.Image}}\t{{.Ports}}" Output might be: my-postgres postgres:15.3 0.0.0.0:5432->5432/tcp ### 1.3 Check prerequisites and dependencies List extensions that may affect configuration behavior: SELECT extname, extversion FROM pg_extension ORDER BY extname; If you plan to change memory settings, check available system RAM and current PostgreSQL memory usage: free -h Example: total used free shared buff/cache available Mem: 15Gi 2.1Gi 8.4Gi 234Mi 4.9Gi 12Gi Always record these observations with timestamps. Use a tool like script or copy output into a runbook. This snapshot is your baseline for rollback and troubleshooting. ## Safe Configuration Path A safe configuration change follows a strict sequence: observe, plan, change, validate, and roll back if needed. The biggest mistake is making multiple changes at once or editing a live server without a recovery plan. This section shows a safe workflow using work_mem as an example. ### 2.1 Observe current state Query the current setting and its context: SELECT name, setting, unit, context, reset_val FROM pg_settings WHERE name = 'work_mem'; Expected output: name | setting | unit | context | reset_val ---------+---------+------+---------+----------- work_mem| 4096 | kB | user | 4096 Here context = user means the setting can be changed per session without restarting the server. reset_val is the value used when RESET is issued. This is crucial for rollback. ### 2.2 Determine the smallest justified change Suppose your application runs large sorts and you want to increase work_mem from 4 MB to 16 MB for a specific session only. This avoids a global change that could exhaust memory if many sessions run concurrently. The blast radius is one session, not the entire server. ### 2.3 Make the change in a reversible way Prefer session-level changes for tunables when possible: SET work_mem = '16MB'; Then confirm: SHOW work_mem; Expected output: work_mem ---------- 16MB If you need a change that persists across sessions but not server restart, use ALTER DATABASE or ALTER ROLE : ALTER DATABASE SET work_mem = '16MB'; Verify with: SELECT datname, datconfig FROM pg_database WHERE datname = ''; Example result: datname | datconfig --------------+------------------------- your_database| {work_mem=16MB} If the change must be global, edit postgresql.conf or use ALTER SYSTEM , which writes to postgresql.auto.conf : ALTER SYSTEM SET work_mem = '16MB'; Check the auto.conf file content (outside psql): cat /var/lib/postgresql/15/main/postgresql.auto.conf Example: # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. work_mem = '16MB' Unlike session-level changes, global changes require a server reload ( pg_ctl reload or SELECT pg_reload_conf(); ) to take effect for new sessions, and some parameters require a restart. ### 2.4 State the blast radius explicitly Before changing, ask: - Does this affect one session, one database, one role, or all connections? - Does it require a reload or a full restart? - Could it cause memory pressure, disk I/O spikes, or connection failures? Write this down in your change ticket. For the work_mem example: “Session-only increase to 16 MB; no restart needed; risk of higher memory use for one session only.” ## Verification and Diagnostics After making a change, verify that it took effect as intended and that it does not introduce new problems. Use both SQL queries and system monitoring tools. ### 3.1 Verify the effective setting For session-level changes: SHOW work_mem; For global changes, open a new session (important: existing sessions keep the old value) and run the same SHOW command. Also check the source column: SELECT name, setting, source, sourcefile, sourceline FROM pg_settings WHERE name = 'work_mem'; Example output for a change made via ALTER SYSTEM : name | setting | source | sourcefile | sourceline ---------+---------+------------+-------------------------------+------------ work_mem| 16384 | configuration file | /var/lib/postgresql/15/main/postgresql.auto.conf | 3 If source is still default or session , your change did not apply globally. Check whether you edited the right file and whether the server was reloaded. ### 3.2 Test the actual behavior For memory parameters, run a query that exercises the relevant operation. For example, test a sort that uses work_mem : EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ORDER BY ; Look at the Sort Method line in the output: Before increase (4 MB), you might see: Sort Method: external merge Disk: 8496kB After increasing to 16 MB, you might see: Sort Method: quicksort Memory: 12584kB This confirms the parameter had the desired effect. If the sort still spills to disk, your work_mem may still be too small or the planner is not using the expected plan. ### 3.3 Check for unintended side effects Monitor system metrics around the change window: - Connections : SELECT count(*) FROM pg_stat_activity; - Locks : SELECT * FROM pg_locks WHERE NOT granted; - Checkpoint activity : SELECT * FROM pg_stat_bgwriter; For Linux, use vmstat 1 or iostat -x 1 to watch memory and I/O. If you increased shared_buffers , watch for a drop in free memory or a spike in swap usage. Create a diagnostics script that captures the most relevant metrics before and after the change: #!/bin/bash # Capture PostgreSQL settings and activity snapshot psql -U -d -c "SELECT name, setting FROM pg_settings WHERE name IN ('shared_buffers','work_mem','max_connections');" > before_settings.txt psql -U -d -c "SELECT count(*) FROM pg_stat_activity;" > before_activity.txt # ... make change ... psql -U -d -c "SELECT name, setting FROM pg_settings WHERE name IN ('shared_buffers','work_mem','max_connections');" > after_settings.txt psql -U -d -c "SELECT count(*) FROM pg_stat_activity;" > after_activity.txt diff before_settings.txt after_settings.txt diff before_activity.txt after_activity.txt This gives you a clear before/after comparison. ## Failure Modes and Recovery Even with careful planning, configuration changes can fail or cause problems. This section describes common failure modes and step-by-step recovery procedures. ### 4.1 Server fails to start after a configuration change Symptoms: pg_ctl start fails, or the log shows errors like “invalid value for parameter” or “could not write lock file”. Immediate recovery: - Check the PostgreSQL log (usually in /var/log/postgresql/postgresql-15-main.log or specified by logging_collector ). - Identify the erroneous parameter. If you edited postgresql.conf , comment out the wrong line and restart. - If you used ALTER SYSTEM , remove the bad entry from postgresql.auto.conf manually (after stopping the server) or use ALTER SYSTEM RESET ; if the server is up enough to accept connections. - If the server is completely down and you cannot edit files, start with a minimal configuration: pg_ctl start -o "-c config_file=/path/to/clean_postgresql.conf" . Example error log: LOG: invalid value for parameter "shared_buffers": "999999999" FATAL: configuration file "/etc/postgresql/15/main/postgresql.conf" contains errors ### 4.2 Change causes performance regression Symptoms: queries slower, high CPU, or disk I/O spikes after changing a parameter. Recovery: revert the parameter to its previous value. If you changed it globally, use ALTER SYSTEM RESET or edit the file and reload. For session changes, simply reconnect. Example rollback to default: ALTER SYSTEM RESET work_mem; SELECT pg_reload_conf(); Verify: SHOW work_mem; -- should show default or reset_val from pg_settings If you did not record the previous value, check reset_val from pg_settings . That is the compiled-in default or the value from postgresql.conf before any session override. ### 4.3 Change causes connection issues If you changed max_connections and set it too high, the server may consume too much memory or fail to start. If too low, new connections are refused with “sorry, too many clients already”. To recover, lower or raise max_connections back to a safe value. This parameter requires a server restart. Before restarting, ensure no active critical transactions. Use pg_ctl -m smart stop to wait for all clients to disconnect, or pg_ctl -m fast stop for a cleaner shutdown. Example rollback for max_connections : ALTER SYSTEM SET max_connections = '100'; Then restart: pg_ctl restart -m fast ### 4.4 Recovering from an accidental destructive change If you accidentally dropped a table or corrupted data as a side effect of a configuration change (unlikely, but possible if you ran a command during troubleshooting), you must rely on backups. Perform a point-in-time recovery (PITR) if you have WAL archiving enabled. Basic PITR steps: - Stop the server. - Restore the base backup to the data directory. - Create a recovery.signal file in the data directory. - Set restore_command in postgresql.conf to fetch WAL files from archive. - Set recovery_target_time to just before the mistake. - Start the server and monitor recovery. This is an advanced recovery procedure. Always test your backup and recovery process regularly. ## Operations Checklist Use this checklist before and after any PostgreSQL configuration change. Fill in the concrete values for your environment. An illustrative filled example is shown. ### Pre-change checklist
CheckExample (filled)Your value
Version identifiedPostgreSQL 15.3 on Ubuntu 22.04
Topology knownBare metal, single instance
Current parameter value recordedwork_mem = 4 MB (source: default)
Blast radius statedSession-level only; no restart needed
Rollback plan readyRESET work_mem; or reconnect
Backup verified within last 24hYes, pg_dump backup at 02:00 UTC
Change ticket approvedChange #4321 approved by DB lead
### Change execution steps - Open two terminal sessions: one for the change, one for monitoring. - In monitoring session, start vmstat 1 or pg_stat_activity loop. - Make the change using the least invasive method (session, database, role, global). - Immediately verify with SHOW or SELECT from pg_settings . - Run a representative query or workload to confirm behavior. - Check for new errors in the PostgreSQL log. ### Post-change verification
CheckCommandExpected result
Effective settingSHOW work_mem;16MB
SourceSELECT source FROM pg_settings WHERE name='work_mem';configuration file or session
No new errors in logtail -n 50 /var/log/postgresql/postgresql-15-main.logno ERROR or FATAL lines
Performance normalRun EXPLAIN ANALYZE on key querySimilar or better runtime
Connections stableSELECT count(*) FROM pg_stat_activity;Within expected range
If any check fails, execute the rollback plan immediately and investigate before retrying. ### Example rollback commands Session-level: RESET work_mem; Database-level: ALTER DATABASE RESET work_mem; Global (ALTER SYSTEM): ALTER SYSTEM RESET work_mem; SELECT pg_reload_conf(); Manual file edit rollback: comment out the line in postgresql.conf , then reload: pg_ctl reload -D /var/lib/postgresql/15/main . ## Conclusion PostgreSQL configuration mistakes are avoidable when you follow a disciplined process: inventory the environment, observe current settings, make minimal and reversible changes, verify the effect with concrete queries and metrics, and have a tested recovery path. Copying a command from a forum without knowing the version, context, and rollback method is not an operations procedure. As a next step, choose one low-risk parameter in your PostgreSQL instance— work_mem is a good candidate—and run through the full workflow in this guide: record the current state, change it in a session, verify with SHOW and an EXPLAIN test, then roll back using RESET . Document the exact commands and outputs in your team runbook. Build a habit of capturing before and after states for every change, using placeholders instead of real secrets in your notes, and defining recovery verification before an incident forces the decision. With these practices, you turn PostgreSQL configuration from a source of fear into a manageable, observable engineering task.