PostgreSQL Utils

• Database
  • Activity
  • Killing Active connections
• TOP 10
  • Time Consuming
  • MEM Consumption
• Listing databases
  • Active databases per Owner
• Indexes
  • Listing Indexes
  • Non Used Indexes
  • Missing Indexes
  • Report Index/ReIndex Creation Progress
• Extensions
  • Create
  • Update
  • Delete
  • List

Database

Activity

  SELECT distinct datname, count(*)
    FROM pg_stat_activity
   WHERE datname = 'database_name'
GROUP BY datname
ORDER BY 1;

Killing Active connections

SELECT pg_terminate_backend(pg_stat_activity.pid)
  FROM pg_stat_activity
 WHERE pg_stat_activity.datname = 'database_name'
   AND pid <> pg_backend_pid();

TOP 10

Time Consuming

  SELECT userid::regrole as "user",
         datname as database,
         calls,
         round(mean_exec_time::numeric, 2) as "Avg Time (ms)",
         round(total_exec_time::numeric, 2) as "Total Time (ms)",
         query
    FROM pg_stat_statements join pg_database
            on pg_stat_statements.dbid = pg_database.oid
ORDER BY mean_exec_time DESC
   LIMIT 10;

For PG v12 and below, remove exec from mean_exec_time and total_exec_time

MEM Consumption

  SELECT userid::regrole as "user",
         datname as database,
         calls,
         pg_size_pretty((shared_blks_hit + shared_blks_dirtied)) as mem,
         queryid,
         query
    FROM pg_stat_statements join pg_database
           on pg_stat_statements.dbid = pg_database.oid
ORDER BY (shared_blks_hit + shared_blks_dirtied) DESC
   LIMIT 10;

Listing databases

SELECT * FROM pg_database pd WHERE datallowconn;
SELECT datname FROM pg_database pd WHERE datallowconn AND datname NOT IN ('rdsadmin', 'template1');

Active databases per Owner

  SELECT d.datname AS database, u.usename AS owner
    FROM pg_database d JOIN pg_user u ON d.datdba = u.usesysid
   WHERE datname IN (SELECT datname FROM pg_stat_activity WHERE state is not null)
ORDER BY datname;

Version

SELECT version();

User

SELECT current_user;

Upgrading

Upgrading the PostgreSQL DB engine for Amazon RDS
Troubleshooting PostGIS Extensions issues when upgrading Postgres

Indexes

Listing indexes

SELECT * FROM pg_indexes WHERE schemaname ='public'

To find non-used indexes

• Consider deleting the Index if index_scans_count is very low

   SELECT idxstats.schemaname as schema_name,
          idxstats.relname AS table_name,
          indexrelname AS index_name,
          idxstats.idx_scan AS index_scans_count,
          pg_size_pretty(pg_relation_size(idxstats.indexrelid)) AS index_size
     FROM pg_stat_all_indexes AS idxstats
            JOIN pg_index i ON idxstats.indexrelid = i.indexrelid
    WHERE idxstats.schemaname = 'public'
  AND NOT i.indisunique   -- is not a UNIQUE index
 ORDER BY idxstats.idx_scan;

Missing indexes

• seq_scan_count: A high number of sequential scans may indicate that appropriate indexes are missing or underutilized.

  SELECT tabstats.schemaname AS schema_name,
         tabstats.relname AS table_name,
         tabstats.seq_scan AS seq_scan_count,
         tabstats.idx_scan AS index_scan_count,
         pg_size_pretty(pg_total_relation_size(tabstats.relid)) AS table_size
    FROM pg_stat_all_tables AS tabstats
   WHERE tabstats.schemaname = 'public'
ORDER BY seq_scan_count DESC;

Report progress on creating reindexing indexes

To follow the progress of Index/ReIndex

Extensions

Create

CREATE EXTENSION extension_name;

In a Schema

CREATE EXTENSION IF NOT EXISTS extension_name WITH SCHEMA schema_name;

Specific version

CREATE EXTENSION IF NOT EXISTS extension_name VERSION '0.0.1';

In a Schema and Specific version

CREATE EXTENSION IF NOT EXISTS extension_name WITH SCHEMA schema_name VERSION '0.0.1';

Update

Upgrading Path

SELECT * FROM pg_extension_update_paths('extension_name') WHERE source='current_version_number' AND target NOT LIKE '%next%' AND source<target AND path LIKE '%--%';

To Specific version

ALTER EXTENSION extension_name UPDATE TO 'version_number';

To Last available version

ALTER EXTENSION extension_name UPDATE;

Delete

DROP EXTENSION extension_name;

List Extensions

Installed

SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL order by 1;

Installed outdated

SELECT * FROM pg_available_extensions WHERE default_version > installed_version;

Available versions

SELECT * FROM pg_available_extension_versions WHERE name LIKE '%pg_stat_statements%';

By Owner

SELECT e.extname AS "Extensions", n.nspname AS schema_name, u.usename AS "Owner"
  FROM pg_extension e
    JOIN pg_namespace n ON e.extnamespace = n.oid
    JOIN pg_user u ON e.extowner = u.usesysid;

By Schema

 SELECT e.extname AS "Name",
        e.extversion AS "Version",
        n.nspname AS "Schema",
        c.description AS "Description"
   FROM pg_catalog.pg_extension e
          LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
          LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
          AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
   WHERE e.extname LIKE '%postgis%'
ORDER BY 1;

PostGIS

• Support Matrix
• Obsolete Versions

postgis_raster

• Check: https://repost.aws/knowledge-center/rds-postgresql-upgrade-postgis

-- Installation Path
SELECT probin FROM pg_proc WHERE proname = 'postgis_raster_lib_version';

-- If installation Path is lower than new version, run:
SELECT postGIS_extensions_upgrade();