Postgres Dba
Grant SELECT on All Tables in PostgreSQL
Mar 27, 2026 / · 4 min read · postgresql database administration sql queries security access control permissions etl postgres dba pg_default_acl ·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 …
Read MoreKill Idle PostgreSQL Sessions with SQL
Mar 24, 2026 / · 5 min read · postgresql database administration sql queries connection management performance tuning pg_stat_activity postgres dba ·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 …
Read MoreCount PostgreSQL Sessions by State with SQL
Mar 23, 2026 / · 4 min read · postgresql database administration sql queries connection management monitoring pg_stat_activity postgres dba ·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 …
Read MorePostgreSQL Miscellaneous Settings Query Guide
Mar 22, 2026 / · 5 min read · postgresql database administration sql queries configuration system views pg_settings postgres dba ·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 …
Read MorePostgreSQL Preset Options Settings Query Guide
Mar 21, 2026 / · 4 min read · postgresql database administration sql queries configuration system views pg_settings postgres dba ·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 …
Read MorePostgreSQL Query Planning Settings Query Guide
Mar 20, 2026 / · 5 min read · postgresql database administration sql queries configuration query planning performance tuning pg_settings postgres dba pg_stat_activity pg_stat_statements pg_stat_user_tables ·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 …
Read MorePostgreSQL Replication Settings Query Guide
Mar 19, 2026 / · 5 min read · postgresql database administration sql queries replication configuration high availability pg_settings postgres dba ·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 …
Read MorePostgreSQL Resource Settings Query Guide
Mar 18, 2026 / · 5 min read · postgresql database administration sql queries configuration performance tuning resource management pg_settings postgres dba pg_stat_database ·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 …
Read MorePostgreSQL SSL Settings Query Guide
Mar 17, 2026 / · 4 min read · postgresql database administration sql queries security ssl configuration pg_settings postgres dba pg_stat_ssl ·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 …
Read MorePostgreSQL 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 …
Read More