Pebellslearn by building
0%
RoadmapsMatrixCoursesSandboxSign in
Installation, Setup and Configuring  ›  Capstone Project

Configuration, Logging and Statistics

Reporting Logging & Statistics 15 minChange settings through postgresql.conf or ALTER SYSTEM, check each setting's context to know whether it needs a reload or restart, and use logs and pg_stat views to see what the server is doing.

Configuration, Logging and Statistics

Riders complain that ending a ride sometimes takes five seconds. The query is fine in testing. You need the server to tell you which statements are slow, which means changing a setting, reloading it and reading the log. This is the everyday loop of a DBA.

The rule

Set it, check its context, reload or restart, then confirm.

Settings live in postgresql.conf. ALTER SYSTEM writes to a second file, postgresql.auto.conf, which is read last and so wins:

ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name IN ('log_min_duration_statement', 'shared_buffers');

The context column says how a setting can change:

  • postmaster: only at server start. shared_buffers, max_connections, listen_addresses, shared_preload_libraries. Needs a restart; pending_restart shows t until then.
  • sighup: re-read on reload.
  • superuser / user: can also be set per session with SET, and per database or role with ALTER DATABASE pedal SET … or ALTER ROLE analyst SET ….

Logging settings every server should consider:

  • logging_collector = on to capture logs into files under log_directory (needs a restart).
  • log_min_duration_statement to log any statement slower than a threshold.
  • log_line_prefix to stamp each line with time, process id, user and database.
  • log_connections, log_lock_waits and log_autovacuum_min_duration to see connections, lock waits and vacuum work.

Statistics views are updated continuously by the server:

  • pg_stat_activity: one row per connection, with its state and current query.
  • pg_stat_user_tables: scans, inserts, dead tuples and last vacuum per table.
  • pg_stat_database: commits, rollbacks and cache hits per database.

For per-query totals, add the pg_stat_statements extension. Extensions are optional features you add with CREATE EXTENSION; this one must also be loaded at startup, so it goes in shared_preload_libraries and needs one restart. pg_available_extensions lists what is installed and ready to add.

The shortcut

Before changing anything, look up the setting's context. It tells you in one word whether the change is instant, needs a reload, or needs a planned restart.

Catching the slow ride endingsStep 1 of 4

  1. Set it

    ALTER SYSTEM SET log_min_duration_statement = '500ms';

The trap is editing postgresql.conf for a setting that was earlier changed with ALTER SYSTEM. The value in postgresql.auto.conf silently wins. pg_settings.sourcefile shows which file supplied the value.

Drills

Answer fast — trust the rule.

quick drill

pg_settings shows context = postmaster for a setting. What does changing it require?

quick drill

Where does ALTER SYSTEM SET write its value?

quick drill

Which view shows every current connection and what it is running?

quick drill

Why does enabling pg_stat_statements need a restart?

quick drill

The analyst role should never run queries longer than 60 seconds, but other roles should be unaffected. What fits best?

Remember

  • Set it, check its context, reload or restart, then confirm.
  • postgresql.auto.conf beats postgresql.conf; pg_settings shows the truth.
  • Slow-query logging and the pg_stat views are your first diagnostic tools.

Practice — level up

0/1 solved

Write your answer, then compare it with a rubric and a model answer.

ExplainHunt the slow queries

Describe, step by step, how you would find which statements are making ride endings slow, from changing settings to reading results. Say whether each change needs a reload or a restart and how you know.