PostgreSQL System Administration Functions

REINDEX is a heavyweight operation that locks the table. brin_summarize_new_values() only processes new, unsummarized ranges without locking.

✓ Instead: Use brin_summarize_new_values() for incremental updates after bulk inserts; reserve REINDEX for BRIN corruption or major schema changes.

current_setting('myapp.setting') raises an error if the setting doesn't exist. In application code that checks for optional custom settings, this causes uncaught exceptions.

✓ Instead: Use current_setting('myapp.setting', true) with missing_ok=true — it returns NULL instead of raising an error when the setting is absent. Use COALESCE to provide a default: COALESCE(current_setting('myapp.setting', true), 'default').

Disabling the pending list with gin_pending_list_limit=0 makes every insert directly update the main GIN structure — dramatically slowing inserts on high-write tables.

✓ Instead: Keep the default or tuned gin_pending_list_limit and call gin_clean_pending_list() periodically (or let autovacuum handle it).

Session-level advisory locks persist until explicitly released or the session ends. A crashed or pooled connection that holds advisory locks can block other sessions indefinitely.

✓ Instead: Use transaction-level advisory locks (pg_advisory_xact_lock()) which auto-release on commit/rollback. For session locks, always pair with pg_advisory_unlock() in exception handlers or use pg_advisory_unlock_all() in connection pool reset queries.

Advisory locks are purely cooperative — they only work if all code paths acquire and release them correctly. Any process that skips the lock protocol bypasses coordination entirely.

✓ Instead: Use advisory locks for high-level coordination (job scheduling, cache invalidation); use SELECT ... FOR UPDATE for row-level data concurrency where automatic enforcement is needed.

pg_advisory_unlock() returns false (not an error) if you try to release a lock you do not hold. Mismatched lock/unlock calls in complex workflows can leave locks held longer than intended or release another worker's lock if keys collide.

✓ Instead: Always track which advisory locks your session holds. Consider using a table to log acquired locks so cleanup code can release exactly the right ones. Use pg_try_advisory_lock() return value to confirm acquisition.

Session-level advisory locks (pg_advisory_lock) persist across transaction boundaries until explicitly released or the session ends. They do NOT roll back with ROLLBACK.

✓ Instead: For transaction-scoped advisory locks (auto-released on commit/rollback), use pg_advisory_xact_lock() instead of pg_advisory_lock().

pg_advisory_unlock() will not release a lock acquired with pg_advisory_xact_lock(). The unlock function only works on session-level locks.

✓ Instead: Transaction advisory locks are designed to auto-release. Simply commit or rollback the transaction. If you need manual release, use pg_advisory_lock() and pg_advisory_unlock() instead.

pg_start_backup() is deprecated in PostgreSQL 15 (exclusive backup mode removed) and removed in PostgreSQL 17. It is also dangerous in high-concurrency setups.

✓ Instead: Use pg_backup_start() (non-exclusive) in PostgreSQL 15+ for base backups. For production, prefer pg_basebackup which handles this automatically.

The labelfile returned by pg_backup_stop() is NOT automatically written anywhere. Ignoring it produces an unusable backup that cannot be recovered from.

✓ Instead: Always capture the labelfile and spcmapfile values and write them to the backup directory as backup_label and tablespace_map respectively.

pg_terminate_backend() drops the entire connection. pg_cancel_backend() only cancels the current query, leaving the connection and transaction state intact. Terminating unnecessarily causes reconnect overhead.

✓ Instead: Prefer pg_cancel_backend(pid) for stuck queries — the connection stays open and the client can retry. Reserve pg_terminate_backend() for when you specifically need to close the connection.

pg_column_size() measures the compressed size of a single value, not the space it occupies in a table row. Row headers, alignment padding, and NULL bitmaps add overhead that pg_column_size() does not include.

✓ Instead: Use pg_relation_size() for table-level storage. For per-row estimates use: SELECT sum(pg_column_size(t.*)) FROM t LIMIT 1 as a rough guide, but note alignment padding is not captured.

An unconsumed logical replication slot prevents WAL cleanup — the primary retains all WAL from the slot's `restart_lsn` forward. A forgotten slot can fill the pg_wal directory and cause the primary to PANIC. Always monitor `pg_replication_slots WHERE active = false`.

✓ Instead: SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots WHERE NOT active;

Logical replication slots are database-scoped. You must be connected to the same database as the slot to drop it. Connecting to a different database and calling `pg_drop_replication_slot` will raise 'replication slot ... does not exist'.

✓ Instead: \c target_database SELECT pg_drop_replication_slot('slot_name');

pg_export_snapshot() in READ COMMITTED mode produces a snapshot that advances with each statement — workers may see different data than expected.

✓ Instead: Always use REPEATABLE READ or SERIALIZABLE isolation: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT pg_export_snapshot();

`pg_wal_replay_pause()` is meant for brief maintenance windows (e.g., taking a consistent read of the standby). Leaving replay paused indefinitely causes WAL to accumulate on the primary, eventually exhausting `wal_keep_size` and potentially breaking replication entirely.

✓ Instead: Always set a timeout or monitoring alert when pausing replay: `SELECT pg_wal_replay_pause(); -- do work --; SELECT pg_wal_replay_resume();` in a single session that will terminate on disconnect.

In HA setups with load balancing, a connection may be routed to a standby. Write without checking pg_is_in_recovery() causes errors on read-only standbys.

✓ Instead: Check pg_is_in_recovery() = false before executing write statements in monitoring or admin scripts that may connect to any node in the cluster.

Pausing WAL replay causes the standby to fall behind the primary. WAL files accumulate on the primary until pg_wal fills up and the server stops.

✓ Instead: Always set a timeout or reminder: PERFORM pg_wal_replay_pause(); -- critical window -- PERFORM pg_wal_replay_resume(); Never leave paused in automation.

Transactional logical messages appear in WAL and are consumed by replication subscribers. They are not a substitute for `LISTEN/NOTIFY` for real-time in-process notifications. Using them for high-frequency event notification inflates WAL size unnecessarily.

✓ Instead: Use NOTIFY for real-time application notifications; use pg_logical_emit_message for cross-cluster metadata or migration markers consumed by CDC pipelines.

Calling `pg_logical_slot_get_changes` and discarding the results (e.g., inside a DO block without capturing the output) still advances the slot's confirmed LSN. The changes are permanently consumed and lost. Always process or store the output before calling this function.

✓ Instead: INSERT INTO change_log SELECT * FROM pg_logical_slot_get_changes('my_slot', NULL, 1000); -- Store before consuming

If a CDC pipeline uses `peek_changes` in a loop without ever calling `get_changes`, the slot position never advances. WAL continues to accumulate indefinitely until pg_wal fills up and the server panics. Peek is for inspection only — production consumers must use `get_changes`.

✓ Instead: Always use pg_logical_slot_get_changes in production consumers. Use peek_changes only for ad-hoc debugging sessions.

Manually deleting `.ready` files from `pg_wal/archive_status` silently abandons archiving of those WAL segments. The segments themselves remain but will never be archived. This creates gaps in the WAL archive that make point-in-time recovery impossible past those gaps.

✓ Instead: Fix the archive_command failure first. If you must skip archiving for specific segments, use `pg_switch_wal` after fixing the underlying issue and verify archive continuity before relying on the archive for recovery.

pg_ls_dir() is restricted to subdirectories of the PostgreSQL data directory for security. It cannot list /tmp, /home, or other OS paths.

✓ Instead: Use pg_ls_logdir() for log files, pg_ls_waldir() for WAL files — these are specialised, safer alternatives with additional metadata.

Reading log files through `pg_read_file` and `pg_ls_logdir` inside the database is not a reliable log streaming mechanism. Large log entries can be read mid-write, and there is no cursor position — each call rereads from byte 0 (or a given offset). Use `pg_lsclusters`, syslog, or a log shipping solution for real-time monitoring.

✓ Instead: Use log_destination = 'csvlog' + COPY FROM to bulk-load log data, or use an external log agent (Vector, Fluentd) that reads from the filesystem.

WAL directory size reflects all retained segments (for archiving, replication slots, and recycling) — not just replication lag. Use `pg_wal_lsn_diff(sent_lsn, replay_lsn)` from `pg_stat_replication` for accurate per-replica lag measurement.

✓ Instead: SELECT application_name, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag FROM pg_stat_replication;

Calling `pg_read_binary_file` without offset/length on a large WAL segment or table file reads the entire file into memory (up to 16MB for WAL, potentially gigabytes for data files). This can cause OOM conditions or timeouts.

✓ Instead: Always use `pg_stat_file(filename).size` to check file size first, then read in chunks: `pg_read_binary_file(filename, 0, 65536)` for the first 64KB.

pg_read_file() is restricted to files within the data directory or log_directory for security. Attempting to read /etc/passwd or other system files will fail.

✓ Instead: Use pg_read_file() only for PostgreSQL config and log files. For general file access, use server-side PL/Python or PL/Perl extensions if available and authorised.

pg_reload_conf() sends SIGHUP but returns immediately. Some settings (requiring restart) silently do not apply until a full restart; others may fail validation.

✓ Instead: After pg_reload_conf(), query pg_settings WHERE pending_restart = true to check if any settings still need a restart. Check pg_log for configuration errors.

Advancing a slot's LSN permanently discards all changes before that point. The subscriber will not see those changes and will silently diverge from the publisher's data. Always coordinate with the subscriber team before advancing slots in production.

✓ Instead: Prefer fixing the slow consumer, increasing `wal_keep_size`, or adding disk space. Only advance the slot if you accept data loss on the subscriber and have a resync plan.

pg_rotate_logfile() only creates a new log file. Without periodic deletion of old files, log directories fill up. On systems with log_rotation_size or log_rotation_age set, rotation happens automatically.

✓ Instead: Set log_rotation_age and log_rotation_size in postgresql.conf for automatic rotation. Use pg_rotate_logfile() only for on-demand rotation before taking a log archive.

If `pg_start_backup()` is called but `pg_stop_backup()` is never called (e.g., due to a backup script crash), the server remains in backup mode: WAL files are retained indefinitely, filling the pg_wal directory. Always use a try/finally pattern to guarantee pg_stop_backup is called.

✓ Instead: Use pg_backup_start()/pg_backup_stop() on PG15+, or ensure pg_stop_backup() is always called, even on error. Consider using tools like pgBackRest or pg_basebackup that handle this automatically.

Like other server file functions, pg_stat_file() is restricted to the PostgreSQL data directory. You cannot use it to check disk space or arbitrary file sizes outside the cluster.

✓ Instead: Use pg_database_size(), pg_tablespace_size(), pg_relation_size(), or pg_total_relation_size() for storage monitoring within PostgreSQL.

Calling `pg_stop_backup(false, false)` returns immediately without waiting for WAL segments to be archived. If you then immediately test the backup, the WAL needed for recovery to the backup end LSN may not yet be archived, making the backup unrecoverable.

✓ Instead: Use `pg_stop_backup(false, true)` (default) to wait for archive completion, or verify archive with `pg_walfile_name(end_lsn)` and check the archive directory.

Forcing WAL switches creates more WAL files to archive. Each switch causes I/O and archive overhead. Call it only when explicitly needed (after backup stops, before planned downtime).

✓ Instead: Let PostgreSQL switch WAL segments naturally via wal_rotation_size / archive_timeout. Use pg_switch_wal() only for explicit archiving needs.

Blindly terminating connections that appear stuck may mask underlying issues (long-running queries, deadlocks, missing indexes). The same problem will recur.

✓ Instead: First query pg_stat_activity to understand what the backend is doing and why. Use pg_cancel_backend() for queries (leaves connection open); use pg_terminate_backend() only when the connection itself must be closed.

pg_try_advisory_lock() is non-blocking by design — it returns false immediately if the lock is taken. Polling it in a loop wastes CPU and creates thundering herd problems.

✓ Instead: Use pg_advisory_lock() when you must wait (it blocks cleanly). Use pg_try_advisory_lock() only when you want to skip processing if another worker already has the lock — not as a polling mechanism.

LSNs are formatted as hex segments separated by a slash (e.g., '0/4A2B3C4D'). Comparing them as strings gives lexicographic order, not numeric WAL position order. For any arithmetic or magnitude comparison, use `pg_wal_lsn_diff`.

✓ Instead: SELECT pg_wal_lsn_diff('0/4A2B3C4D', '0/4A2B1234') AS bytes_apart; -- Numeric comparison, not string

If a script that calls pg_wal_replay_pause() crashes or exits without calling pg_wal_replay_resume(), the standby stays paused indefinitely, growing the replication lag.

✓ Instead: Wrap in a DO block with exception handling, or use advisory locks with a monitoring alert that fires if pg_is_wal_replay_paused() is true for more than N seconds.

pg_wal_replay_resume() is idempotent — calling it when replay is already running does nothing harmful. This makes it safe to call unconditionally in cleanup code.

✓ Instead: Call pg_wal_replay_resume() unconditionally in FINALLY/EXCEPTION blocks without wrapping in a pg_is_wal_replay_paused() check.

WAL filenames change with every segment switch and restart. Scripts that hardcode WAL filenames become stale within seconds on active servers.

✓ Instead: Always derive WAL filenames dynamically: pg_walfile_name(pg_current_wal_lsn()) or pg_walfile_name(specific_lsn).

On a standby server, `pg_walfile_name_offset` converts LSNs relative to the standby's current timeline. If the standby has diverged from the primary's timeline after a failover, the WAL file names will differ from those on the primary.

✓ Instead: Run pg_walfile_name_offset on the server whose WAL files you are working with. Do not translate primary LSNs on a standby or vice versa after a timeline change.

set_config(name, value, true) is transaction-local — it rolls back on ROLLBACK. set_config(name, value, false) is session-level but doesn't persist across reconnects.

✓ Instead: For permanent changes use ALTER SYSTEM SET or edit postgresql.conf + pg_reload_conf(). For per-role defaults use ALTER ROLE user SET parameter = value. Use set_config() only for session or transaction scoping.