PostgreSQL System Administration Functions

Many `postgresql.conf` settings can be changed without a server restart — just reload with `pg_reload_conf()`. Check `pg_settings.context = 'sighup'` to identify reloadable parameters.

SELECT name, setting FROM pg_settings WHERE context = 'sighup' AND name LIKE 'log_%';

`pg_cancel_backend` sends SIGINT (cancels the current query but keeps the connection). `pg_terminate_backend` sends SIGTERM (kills the entire connection). Try cancel first, then terminate if needed.

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE query_start < now() - interval '5 min' AND state = 'active';

`pg_cancel_backend` only interrupts the current query — the connection persists and the application can retry. `pg_terminate_backend` kills the entire connection. Use cancel for runaway queries in production, terminate for stuck/zombie connections.

-- Add to a monitoring script:
SELECT pid, query_start, query FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '1 min' AND query NOT LIKE 'SELECT pg_%';

Call `pg_rotate_logfile()` before long maintenance windows to start a clean log file. Makes it easier to extract maintenance-period logs for analysis.

SELECT pg_rotate_logfile(); -- Start fresh log before VACUUM FULL or migration

Use `pg_column_size` to compare how much space different representations use. For example, `jsonb` vs normalized table columns, or different numeric precision levels.

SELECT pg_column_size(42::int), pg_column_size(42::bigint), pg_column_size(42::numeric);

Use custom GUC variables (with `app.` prefix) set via `set_config('app.current_user_id', id, true)` to pass application context to RLS policies and triggers — without changing the database role.

-- In application connection setup:
SELECT set_config('app.user_id', $1::text, false);
-- In RLS policy:
CREATE POLICY user_rows ON data USING (user_id = current_setting('app.user_id')::bigint);

Use `current_setting('app.key', true)` with `missing_ok=true` in RLS policies and functions to safely read application context set by `set_config`. The `true` parameter prevents errors when the setting hasn't been set yet.

CREATE FUNCTION get_current_tenant() RETURNS bigint AS $ SELECT coalesce(current_setting('app.tenant_id', true), '0')::bigint; $ LANGUAGE SQL STABLE;

Advisory locks are ideal for preventing duplicate job execution across multiple application servers. Use `pg_try_advisory_lock` for non-blocking acquisition — if it returns false, another process has the lock.

-- Non-blocking: skip if already locked
IF pg_try_advisory_lock(hashtext('process_invoices')) THEN
  -- do work
  PERFORM pg_advisory_unlock(hashtext('process_invoices'));
END IF;

Use `pg_try_advisory_lock(id)` in a SELECT to implement a concurrent job queue: `SELECT id FROM jobs WHERE status = 'pending' AND pg_try_advisory_lock(id) LIMIT 1 FOR UPDATE SKIP LOCKED`. Multiple workers can safely claim different jobs.

SELECT id FROM pending_jobs WHERE pg_try_advisory_lock(id) ORDER BY created_at LIMIT 1;

Session-level advisory locks persist until explicitly released or the session ends. Always pair `pg_advisory_lock` with `pg_advisory_unlock`. Use transaction-level `pg_advisory_xact_lock` if you want automatic release at transaction end.

-- Transaction-level: auto-released at commit/rollback
SELECT pg_advisory_xact_lock(42);