PostgreSQL Scripts for Database Administration

Grant SELECT on All Tables in PostgreSQL This PostgreSQL script creates a read-only user and grants SELECT privileges on all existing tables in a schema, plus ensures the same access is automatically applied to any future tables created in that schema. Purpose and Overview Third-party ETL tools, reporting tools, and …

Kill Idle PostgreSQL Sessions with SQL These PostgreSQL scripts terminate idle sessions that have been inactive for more than 15 minutes. Two variants are provided: one that targets idle sessions across all databases on the server, and one scoped to only the currently connected database. Purpose and Overview Idle and …

Count PostgreSQL Sessions by State This PostgreSQL query returns a count of all server sessions grouped by their current state. It gives a fast at-a-glance view of how connections are distributed across active, idle, and problematic states. Purpose and Overview PostgreSQL tracks the state of every backend connection in …

PostgreSQL Miscellaneous Settings Query Guide This PostgreSQL query retrieves configuration settings that fall outside all standard category groups — including customized options, process title settings, and any settings added by extensions or custom builds. It works as a catch-all complement to the other …

PostgreSQL Preset Options Settings Query Guide This PostgreSQL query retrieves preset option settings from the pg_settings system view. Preset options are read-only, compiled-in values that describe fundamental characteristics of the PostgreSQL installation — they cannot be changed at runtime. Purpose and Overview …

PostgreSQL Query Planning Settings Query Guide This PostgreSQL query retrieves all query tuning and statistics collection settings from the pg_settings system view. These parameters control how the query planner selects execution strategies, estimates costs, and collects runtime statistics. Purpose and Overview The …

PostgreSQL Replication Settings Query Guide This PostgreSQL query retrieves all replication-related configuration settings from the pg_settings system view. These parameters control streaming replication, logical replication, standby server behaviour, and WAL sender configuration. Purpose and Overview Replication is …

PostgreSQL Resource Settings Query Guide This PostgreSQL query retrieves all resource usage configuration settings from the pg_settings system view. Resource settings control how PostgreSQL allocates memory, disk, and CPU resources across queries, background processes, and maintenance operations. Purpose and Overview …

PostgreSQL SSL Settings Query Guide This PostgreSQL query retrieves all SSL and TLS configuration settings from the pg_settings system view. It surfaces whether SSL is enabled, which certificates and keys are in use, what cipher suites are allowed, and all other SSL-related parameters. Purpose and Overview Encrypting …

PostgreSQL Statistics Settings Query Guide This PostgreSQL query retrieves all statistics collection configuration settings from the pg_settings system view. These parameters control what runtime data PostgreSQL collects about query execution, table activity, and performance, feeding into the pg_stat_* monitoring …