Scripts

How to List Installed Extensions in PostgreSQL PostgreSQL extensions package additional functionality — data types, functions, operators, and index methods — that can be installed into a database with CREATE EXTENSION. Common examples include pg_stat_statements for query performance tracking, uuid-ossp for UUID …

How to List PostgreSQL Roles and Privileges Which accounts can log in, which can create databases or other roles, and which are quietly superusers? PostgreSQL answers all three from pg_roles, a view over the role catalog that every user can read. Joined to pg_auth_members it also shows who belongs to which group, and …

Scrubbing Email PII from Your PostgreSQL Database When you copy a production database to a development or test environment, you must remove or anonymize personally identifiable information (PII). Email addresses are among the most common PII fields that need to be scrubbed. Leaving real email addresses in …

Audit Single vs Multi-Column Indexes in PostgreSQL Not all indexes are created equal. A single-column index covers one column and is simple to reason about. A multi-column (composite) index covers two or more columns and can serve a wider range of queries — but only when the leading columns match the query filter. As a …

Monitor PostgreSQL Databases with pg_stat_database pg_stat_database is the database-level statistics view in PostgreSQL. It holds one row per database, and each row carries cumulative counters for transactions, block reads, row activity, temporary files and deadlocks. It is a quick first stop when you want to compare …

Cancel vs Terminate PostgreSQL Backends: What Every DBA Should Know PostgreSQL gives you two tools for dealing with problem backends: pg_cancel_backend() and pg_terminate_backend(). They sound similar but behave very differently. Using the wrong one can disrupt users unnecessarily, or leave a problematic connection in …

How to List All Schemas in a PostgreSQL Database information_schema.schemata is the SQL-standard view for listing schemas, and it returns one row per schema in the current database that the connected role owns or holds a privilege on. Behind it sits pg_namespace, the system catalog that stores every schema with its …

List Partitioned and Ordinary Tables in PostgreSQL Before you change a retention job, plan a migration, or add a partition for next month, you need a plain list of which tables are partitioned and which are not. pg_class joined to pg_namespace gives that list for the whole database in one query, and a count from …

How to Find PostgreSQL Tables Without a Primary Key A missing primary key is one of the most common and damaging database design oversights. Without a primary key, PostgreSQL has no reliable way to uniquely identify a row. This causes problems with logical replication, ORM frameworks, and application-level updates or …

How to List All Views in a PostgreSQL Database Views are saved SQL queries stored in the database. A production database can accumulate dozens or hundreds of views over time — many created by developers, some by tools, and some that are no longer used. Knowing what views exist, which schema they belong to, and what …