redsfyre

-- Get long queries
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  state,
  datname,
  query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds' and state != 'idle' order by duration desc;

redsfyre

CREATE OR REPLACE VIEW public.active_locks AS 
 SELECT t.schemaname,
    t.relname,
    l.locktype,
    l.page,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted
   FROM pg_locks l

redsfyre

SELECT
    pt.tablename AS TableName
    ,t.indexname AS IndexName
    ,to_char(pc.reltuples, '999,999,999,999') AS TotalRows
    ,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
    ,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
    ,to_char(t.idx_scan, '999,999,999,999') AS TotalNumberOfScan
    ,to_char(t.idx_tup_read, '999,999,999,999') AS TotalTupleRead
    ,to_char(t.idx_tup_fetch, '999,999,999,999') AS TotalTupleFetched
FROM pg_tables AS pt

redsfyre

#!/bin/bash
# Copyright (C) 2015, Wazuh Inc.
# Created by Wazuh, Inc. <[email protected]>.
# This program is free software; you can redistribute it and/or modify it under the terms of GPLv2

WPYTHON_BIN="framework/python/bin/python3"

SCRIPT_PATH_NAME="$0"

DIR_NAME="$(cd $(dirname ${SCRIPT_PATH_NAME}); pwd -P)"

redsfyre

-- Gets tables with their auto generated sequences
select table_name,data_default from user_tab_cols where identity_column='YES';

-- Gets table and its ID column details, like sequence name if its auto generated sequence
COLUMN table_name FORMAT A50
COLUMN column_name FORMAT A15
COLUMN sequence_name format A15          
COLUMN generation_type FORMAT A10          
COLUMN identity_options FORMAT A75         
                                           

redsfyre

select index_name, column_name from user_ind_columns where table_name='&tablename';
-- Enter table name after running this query

-- dba_ind_columns : This is to used if login with user having DBA role
-- all_ind_columns : This is to used if login with user having normal role
-- user_ind_columns : This is to used if login with user having normal role

-- OK nvm, this one is better;

set pagesize 50000 verify off echo off

redsfyre

select sequence_name, (xpath('/row/last_value/text()', xml_count))[1]::text::int as last_value
from (
    select sequence_schema,
            sequence_name,
            query_to_xml(format('select last_value from %I.%I', sequence_schema, sequence_name), false, true, '') as xml_count
    from information_schema.sequences
    where sequence_schema = 'public'
) new_table order by sequence_name asc;

redsfyre

Kubernetes list all deployments and their specs (requests/limits)

kubectl get deployments -o jsonpath='{range .items[*]}{"NAME:  "}{.metadata.name}{"\nSPEC:  \n  LIMITS  : "}{.spec.template.spec.containers[*].resources.limits}{"\n  REQUESTS: "}{.spec.template.spec.containers[*].resources.requests}{"\n\n"}{end}'

Kubernetes list all cronjobs and their specs (requests/limits)

kubectl get cronjobs -o jsonpath='{range .items[*]}{"NAME:  "}{.metadata.name}{"\nSPEC:  \n  LIMITS  : "}{.spec.jobTemplate.spec.template.spec.containers[*].resources.limits}{"\n  REQUESTS: "}{.spec.jobTemplate.spec.template.spec.containers[*].resources.requests}{"\n\n"}{end}'

redsfyre

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT 'DROP INDEX ' || string_agg(indexrelid::regclass::text, ', ')
   FROM   pg_index  i
   LEFT   JOIN pg_depend d ON d.objid = i.indexrelid
                          AND d.deptype = 'i'
   WHERE  i.indrelid = 'TABLE_NAME'::regclass  -- Replace the TABLE_NAME with your table's name 

redsfyre

postgres_test_db=# select last_value from old_table_id_seq;
 last_value 
------------
    2084740
(1 row)

postgres_test_db=# select last_value from new_table_id_seq;
 last_value 
------------
          1