Useful PostgreSQL Queries and Commands

postgres_queries_and_commands.sql

-- show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- show running queries (>=9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

-- detailed info on running / idle queries
SELECT * FROM pg_stat_activity WHERE query NOT LIKE '<%';

-- all database users
SELECT 
    *
FROM
    pg_user;

-- all databases and their sizes
SELECT
    datname,
    pg_size_pretty(pg_database_size(datname))
FROM
    pg_database
ORDER BY
    pg_database_size(datname) DESC;

-- all tables and their size, ordered by schema then size
SELECT
    schemaname || '.' || tablename AS "tableName",
    pg_size_pretty(pg_table_size(schemaname || '.' || tablename)) AS "tableSize"
FROM
    pg_tables
ORDER BY
    schemaname ASC,
    pg_table_size(schemaname || '.' || tablename) DESC;

-- better size query - get tables, indexes, views, materialized views
SELECT relname   AS objectname
     , relkind   AS objecttype
     , reltuples AS entries
     , pg_size_pretty(pg_table_size(oid)) AS size  -- depending - see below
FROM   pg_class
WHERE  relkind IN ('r', 'i', 'm', 'v')
ORDER  BY pg_table_size(oid) DESC;

-- cache hit rates (should not be less than 0.99)
SELECT 
    pg_size_pretty(sum(heap_blks_read)) as heap_read, 
    pg_size_pretty(sum(heap_blks_hit))  as heap_hit, 
    pg_size_pretty((sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit)) as ratio
FROM pg_statio_user_tables;
                                                                    
-- table index usage rates (should not be less than 0.99)
SELECT 
    relname, 
    100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
    n_live_tup rows_in_table
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC,  percent_of_times_index_used DESC;

-- how many indexes are in cache
SELECT 
   pg_size_pretty(sum(idx_blks_read)) as idx_read, 
   pg_size_pretty(sum(idx_blks_hit))  as idx_hit, 
   pg_size_pretty((sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit)) as ratio
FROM pg_statio_user_indexes;

-- DB temporary files - size and count
SELECT
    db.temp_files                       AS "Temporary files"
    , pg_size_pretty(db.temp_bytes)     AS "Size of temporary files"
    , db.datname
FROM
    pg_stat_database db
ORDER BY
    db.temp_bytes DESC;

-- DROP ALL INDEXES ON A TABLE (courtesy of Erwin Brandstetter - https://stackoverflow.com/a/34011744)
-- also handle if no indexes exist
DO
$$
    DECLARE

        cmd TEXT;

    BEGIN
            SELECT
                'DROP INDEX ' || string_agg(indexrelid::REGCLASS::TEXT, ', ')
            INTO cmd
            FROM
                pg_index i
                LEFT JOIN pg_depend d ON d.objid = i.indexrelid
                    AND d.deptype = 'i'
            WHERE
                i.indrelid = 'patient_documents'::REGCLASS -- possibly schema-qualified
              AND d.objid IS NULL; -- no internal dependency
        
        IF ( cmd IS NOT NULL ) THEN
            EXECUTE cmd;
        END IF;
    END;
$$;

-- DROP and then RECREATE indexes + DISABLE the TRIGGERs
DO $$
DECLARE

    drop_cmd TEXT;
    create_cmd TEXT;

BEGIN

    SELECT
        'DROP INDEX ' || STRING_AGG(indexrelid::REGCLASS::TEXT, ', ') || ';',
        STRING_AGG(pg_get_indexdef(indexrelid::regclass), '; ') || ';'
    INTO
        drop_cmd,
        create_cmd
    FROM
        pg_index i
    LEFT JOIN pg_depend d
        ON d.objid = i.indexrelid AND d.deptype = 'i'
    WHERE
        i.indrelid = 'studies'::REGCLASS
        AND d.objid IS NULL;

    IF ( drop_cmd IS NOT NULL ) THEN
        EXECUTE drop_cmd;
    END IF;

    ALTER TABLE studies 
        DISABLE TRIGGER ALL;

    -- ... do expensive stuff

    IF ( create_cmd IS NOT NULL ) THEN
        EXECUTE create_cmd;
    END IF;

    ALTER TABLE studies 
        ENABLE TRIGGER ALL;

END;
$$;

-- Find and reset (to max value) ID sequences on all tables, where applicable (that match [ROLE] and have been used at least once)
DO
$$
    DECLARE

        cmd TEXT;

    BEGIN
        SELECT
            'SELECT (' ||
            STRING_AGG('(SELECT setval(''' || s.sequencename::REGCLASS::TEXT || ''', max(id), true) FROM ' ||
                       i.schemaname :: TEXT || '.' || i.tablename :: TEXT ||
                ')', ', ') ||
            ')'
        INTO cmd
        FROM
            pg_tables i
        JOIN pg_sequences s
            ON s.schemaname = i.schemaname AND s.sequencename = (i.tablename :: TEXT || '_id_seq')
        WHERE
            s.sequenceowner :: TEXT = '[ROLE]' -- 'postgres'
            AND NULLIF(s.last_value, 0) IS NOT NULL;

        IF ( cmd IS NOT NULL ) THEN
            EXECUTE cmd;
        END IF;
    END;
$$;

-- Find the sequences not attached to ID columns (for manual adjustment where needed)
SELECT *
FROM
    pg_sequences s
WHERE
    NOT EXISTS (
        SELECT 1
        FROM pg_tables i
        WHERE s.sequencename = ( i.tablename :: TEXT || '_id_seq' )
    )
    AND s.sequenceowner :: TEXT = '[ROLE]' -- 'postgres'
    AND NULLIF(s.last_value, 0) IS NOT NULL;

-- Dump database on remote host to file
$ pg_dump -U username -h hostname databasename > dump.sql

-- Import dump into existing database
$ psql -d newdb -f dump.sql

-- Get locked queries
  SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
        -- trim(blocked_activity.query)    AS blocked_statement,
         trim(regexp_replace(blocked_activity.query, E'[\\r\\n ]+', ' ', 'g' )) AS blocked_statement,
       --trim(blocking_activity.query)   AS current_statement_in_blocking_process
         trim(regexp_replace(blocking_activity.query, E'[\\r\\n ]+', ' ', 'g' )) AS current_statement_in_statement  
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.granted;

-- Long runners
WITH q2k AS (
    SELECT
        now() - query_start                                  as duration,
        trim(regexp_replace(query, E'[\\r\\n ]+', ' ', 'g')) as sql_query,
        application_name,
        *
    FROM
        pg_stat_activity
    WHERE
        state <> 'idle'
--         AND pg_stat_activity.datname = 'DATABASE_NAME'
--         AND application_name in ( 'APPLICATION_NAME' )
        AND now() - query_start > '1 minutes'::INTERVAL
        AND pid <> pg_backend_pid()
    ORDER BY
        1 DESC
)
SELECT
    *
FROM
    q2k;


-- what was changed in config files?
-- thanks amir
SELECT
    name
  , CASE setting
      WHEN '0'  THEN setting
      WHEN '-1' THEN setting
      ELSE
        CASE unit
          WHEN '16MB' THEN pg_size_pretty(setting::numeric * 16 * 1024 * 1024)
          WHEN '8kB'  THEN pg_size_pretty(setting::numeric * 8 * 1024)
          WHEN 'kB'   THEN pg_size_pretty(setting::numeric * 1024)
          ELSE             setting || '' || coalesce(unit, '')
        END
    END
    AS current_setting
  --, setting || ' ' || coalesce(unit, '') AS current_setting_raw
  , CASE boot_val
      WHEN '0'  THEN boot_val
      WHEN '-1' THEN boot_val
      ELSE
        CASE unit
          WHEN '16MB' THEN pg_size_pretty(boot_val::numeric * 16 * 1024 * 1024)
          WHEN '8kB'  THEN pg_size_pretty(boot_val::numeric * 8 * 1024)
          WHEN 'kB'   THEN pg_size_pretty(boot_val::numeric * 1024)
          ELSE             boot_val || '' || coalesce(unit, '')
        END
    END AS default_setting
  --, boot_val || ' ' || coalesce(unit, '') AS default_setting_raw
  , category
  --, source
  --, sourcefile
  --, sourceline
  , CASE source
      WHEN 'configuration file' THEN substring(sourcefile, length(sourcefile) - position('/' in (reverse(sourcefile))) + 2) || ':' || sourceline
      ELSE sourcefile
    END AS sourcefile
FROM
  pg_settings
WHERE
  setting <> boot_val AND source = 'configuration file'
ORDER BY
  category DESC, name;

-- ====
-- Not sure where these came from, pretty sure was PG wiki:
-- ====

-- Create a function that always returns the first non-NULL value:
CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT $1';

-- Then wrap an aggregate around it:
CREATE AGGREGATE public.first (anyelement) (
  SFUNC    = public.first_agg
, STYPE    = anyelement
, PARALLEL = safe
);

-- Create a function that always returns the last non-NULL value:
CREATE OR REPLACE FUNCTION public.last_agg (anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT $2';

-- Then wrap an aggregate around it:
CREATE AGGREGATE public.last (anyelement) (
  SFUNC    = public.last_agg
, STYPE    = anyelement
, PARALLEL = safe
);