Groovy DBA

 to get a set point for vacuum and freeze:   vacuum (analyze,freeze); 

 

  SELECT   *,   pg_size_pretty(table_bytes) AS table,   pg_size_pretty(toast_bytes) AS toast,   pg_size_pretty(index_bytes) AS index,   pg_size_pretty(total_bytes) AS total FROM (   SELECT     *, total_bytes - index_bytes - COALESCE(toast_bytes, 0) AS table_bytes   FROM (     SELECT       c.oid,       n.nspname AS table_schema,       c.relname AS table_name,       c.reltuples AS row_estimate,       pct.relname AS toast_table_name,       pg_total_relation_size(c.oid) AS total_bytes,       pg_indexes_size(c.oid) AS index_bytes,       pg_total_relation_size(c.reltoastrelid) AS toast_bytes     FROM       pg_class c       JOIN pg_class pct ON (c.reltoastrelid = pct.oid)       LEFT JOIN pg_namespace n ON n.oid = c.relnamespace     WHERE c.relkind = 'r'...

  WITH y AS (     SELECT         pg_catalog.format('%I.%I', n1.nspname, c1.relname) AS referencing_tbl,         pg_catalog.quote_ident(a1.attname) AS referencing_column,         t.conname AS existing_fk_on_referencing_tbl,         pg_catalog.format('%I.%I', n2.nspname, c2.relname) AS referenced_tbl,         pg_catalog.quote_ident(a2.attname) AS referenced_column,         pg_relation_size(pg_catalog.format('%I.%I', n1.nspname, c1.relname)) AS referencing_tbl_bytes,         pg_relation_size(pg_catalog.format('%I.%I', n2.nspname, c2.relname)) AS referenced_tbl_bytes,         pg_catalog.format($$CREATE INDEX %I_idx ON %I.%I(%I);$$, t.conname, n1.nspname, c1.relname, a1.attname) AS suggestion     FROM         pg_catalog.pg_constraint t         JOIN pg_catalog.pg_attribut...

 select   now(),   query_start as started_at,   now() - query_start as query_duration,   format('[%s] %s', a.pid, a.query) as pid_and_query,   index_relid::regclass as index_name,   relid::regclass as table_name,   (pg_size_pretty(pg_relation_size(relid))) as table_size,   nullif(wait_event_type, '') || ': ' || wait_event as wait_type_and_event,   phase,   format(     '%s (%s of %s)',     coalesce((round(100 * blocks_done::numeric / nullif(blocks_total, 0), 2))::text || '%', 'N/A'),     coalesce(blocks_done::text, '?'),     coalesce(blocks_total::text, '?')   ) as blocks_progress,   format(     '%s (%s of %s)',     coalesce((round(100 * tuples_done::numeric / nullif(tuples_total, 0), 2))::text || '%', 'N/A'),     coalesce(tuples_done::text, '?'),     coalesce(tuples_total::text, '?')   ) as tuples_progress,   current_locker_pid, ...

 SELECT     pid,usename,client_addr,current_timestamp - state_change time_in_idle,     rank() over (partition by client_addr order by backend_start DESC) as rank FROM     pg_stat_activity WHERE     -- Exclude the thread owned connection (ie no auto-kill)     pid <> pg_backend_pid( ) AND     -- Exclude known applications connections     application_name !~ '(?:psql)|(?:pgAdmin.+)' AND     -- Include connections to the same database the thread is connected to     datname = current_database() AND     -- Include inactive connections only     state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') AND     -- Include old connections (found with the state_change field)     current_timestamp - state_change > interval '5 minutes';

  WITH table_scans as (     SELECT relid,         tables.idx_scan + tables.seq_scan as all_scans,         ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,                 pg_relation_size(relid) as table_size         FROM pg_stat_all_tables as tables         WHERE schemaname not in ('pg_toast','pg_catalog','partman') ), all_writes as (     SELECT sum(writes) as total_writes     FROM table_scans ), indexes as (     SELECT idx_stat.relid, idx_stat.indexrelid,         idx_stat.schemaname, idx_stat.relname as tablename,         idx_stat.indexrelname as indexname,         idx_stat.idx_scan,         pg_relation_size(idx_stat.indexrelid) as index_bytes,         indexdef ~* 'USING btree' AS idx_is_btree   ...

 SELECT     c.relname AS relname,     pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,     (array_agg(idx))[1] AS idx1,     (array_agg(idx))[2] AS idx2,     (array_agg(idx))[3] AS idx3,     (array_agg(idx))[4] AS idx4,     (array_agg(idx))[5] AS idx5,     (array_agg(idx))[6] AS idx6,     (array_agg(idx))[7] AS idx7,     (array_agg(idx))[8] AS idx8,     (array_agg(idx))[9] AS idx9,     (array_agg(idx))[10] AS idx10 FROM (     SELECT         indrelid,         indexrelid::regclass AS idx,         (indrelid::text || E'\n' || indclass::text || E'\n' || indkey::text || E'\n' || coalesce(indexprs::text, '') || E'\n' || coalesce(indpred::text, '')) AS key     FROM         pg_index) sub     JOIN pg_class c ON (c.oid = sub.indrelid) GROUP BY    ...

 /* find options like search_path assigned to users or databases */ SELECT coalesce(role.rolname, 'database wide') as role,         coalesce(db.datname, 'cluster wide') as database,         setconfig as what_changed FROM pg_db_role_setting role_setting LEFT JOIN pg_roles role ON role.oid = role_setting.setrole LEFT JOIN pg_database db ON db.oid = role_setting.setdatabase; /* or from psql */ \drds

  SELECT     oid,     s.schemaname,     oid::regclass table_name,     substr(unnest(reloptions), 1, strpos(unnest(reloptions), '=') - 1) option,     substr(unnest(reloptions), 1 + strpos(unnest(reloptions), '=')) value FROM     pg_class c     JOIN pg_stat_all_tables s ON (s.relid = c.oid) WHERE     reloptions IS NOT NULL     AND (s.schemaname,s.relname) IN (         SELECT             t.table_schema,             t.table_name         FROM             information_schema.tables t             JOIN pg_catalog.pg_class c ON (t.table_name = c.relname)             JOIN pg_catalog.pg_user u ON (c.relowner = u.usesysid)         WHERE             t.table_schema LIKE '%...

 select     round(EXTRACT(EPOCH FROM (clock_timestamp() - query_start))::numeric, 5) as query_age ,       round(EXTRACT(EPOCH FROM (clock_timestamp() -  xact_start))::numeric, 5) as xact_age,     pid,      pg_blocking_pids(PID),      wait_event,      substr(query , 1, 80)     query ,     *      --,  pg_terminate_backend(PID) from pg_stat_activity a,         (select  unnest( string_to_array(replace(replace(pg_blocking_pids(PID)::text,'{',''),'}',''), ','))  as bpids,                   lower(query)             from pg_stat_activity          --where lower(query) like '%drop%' or lower(query) like  '%alter%'          ) b where    PID = b.bpids::integer ;

 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,     blocked_activity.query AS blocked_statement,     blocked_activity.wait_event AS blocked_wait_event,     blocking_activity.wait_event AS blocking_wait_event,     blocking_activity.query AS current_statement_in_blocking_process 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 blockin...

WITH RECURSIVE activity AS (     SELECT         pg_blocking_pids (pid) blocked_by,         *,         age(clock_timestamp(), xact_start)::interval(0) AS tx_age,         -- "pg_locks.waitstart" – PG14+ only; for older versions:  age(clock_timestamp(), state_change) as wait_age         age(clock_timestamp(), (             SELECT                 max(l.waitstart)             FROM pg_locks l             WHERE                 a.pid = l.pid))::interval(0) AS wait_age     FROM         pg_stat_activity a     WHERE         state IS DISTINCT FROM 'idle' ), blockers AS (     SELECT         array_agg(DISTINCT c ORDER BY c) AS pids ...

 -- Copyright 2024 shaneborden --  -- Licensed under the Apache License, Version 2.0 (the "License"); -- you may not use this file except in compliance with the License. -- You may obtain a copy of the License at --  --     https://www.apache.org/licenses/LICENSE-2.0 --  -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. SELECT     current_setting('max_parallel_workers')::integer AS max_workers,     count(*) AS active_workers FROM     pg_stat_activity WHERE     backend_type = 'parallel worker';

SELECT     pid,     usename,     state,     client_addr,     application_name,     CURRENT_TIMESTAMP - state_change time_in_idle,     rank() OVER (PARTITION BY client_addr ORDER BY backend_start DESC) AS rank FROM     pg_stat_activity WHERE -- Exclude the thread owned connection (ie no auto-kill) pid <> pg_backend_pid()     AND     -- Exclude known applications connections     application_name !~ '(?:pgAdmin.+)'     AND     -- Include connections to the same database the thread is connected to     datname = current_database()     AND     -- Include inactive connections only     state NOT IN ('idle', 'disabled');     ------------------------ select * from pg_stat_activity where datname='ccpaprod'; 

 -- Copyright 2024 shaneborden --  -- Licensed under the Apache License, Version 2.0 (the "License"); -- you may not use this file except in compliance with the License. -- You may obtain a copy of the License at --  --     https://www.apache.org/licenses/LICENSE-2.0 --  -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. /* Vaccum Stats Script */ WITH tbl_reloptions AS ( SELECT     oid,     oid::regclass table_name,     substr(unnest(reloptions), 1,  strpos(unnest(reloptions), '=') -1) option,     substr(unnest(reloptions), 1 + strpos(unnest(reloptions), '=')) value FROM     pg_class c WHERE reloptions is NOT null) SELECT   ...

 WITH tbl_reloptions AS ( SELECT     oid,     oid::regclass table_name,     substr(unnest(reloptions), 1,  strpos(unnest(reloptions), '=') -1) option,     substr(unnest(reloptions), 1 + strpos(unnest(reloptions), '=')) value FROM     pg_class c WHERE reloptions is NOT null) SELECT     --to_char(now(), 'YYYY-MM-DD HH:MI'),     current_database() ||'.'|| s.schemaname ||'.'|| s.relname as relname,     n_live_tup live_tup,     n_dead_tup dead_dup,     n_tup_hot_upd hot_upd,     n_mod_since_analyze mod_since_stats,     n_ins_since_vacuum ins_since_vac,     case        when avacinsscalefactor.value is not null and avacinsthresh.value is not null         then ROUND(((n_live_tup * avacinsscalefactor.value::numeric) + avacinsthresh.value::numeric),0)       when avacinsscalefactor.value is null and avacin...

 DO $$ DECLARE     parent_table text := 'public.my_parent_table'; -- Change to your parent table     new_owner    text := 'new_owner_role';         -- Change to your desired owner     r record; BEGIN     -- Loop through all partitions of the given parent table     FOR r IN         SELECT inhrelid::regclass AS partition_name         FROM pg_inherits         WHERE inhparent = parent_table::regclass     LOOP         EXECUTE format('ALTER TABLE %s OWNER TO %I;', r.partition_name, new_owner);         RAISE NOTICE 'Changed owner of % to %', r.partition_name, new_owner;     END LOOP; END$$;

  To verify that COBOL is running, this process must be running: root       19075       1  0 12:13 ?        00:00:00  /var/microfocuslicensing/bin/mfcesd To start COBOL if that process is not running:   1) Sign in as root. 2) Please run /var/microfocuslicensing/bin/mfcesdchk to check if the license daemon is running. 3) If it is not running, please start the license daemon via  /var/microfocuslicensing/bin/startmfcesd.sh Or you can use restartmfcesd.sh - restarts the license daemon. (Aside: restartboth.sh - restarts both the license server and the daemon) 4) Test cobol program compile via $PS_HOME/setup/pscbl.mak this is on pshrtstapp601 and pshrtstapp602:  ./var/microfocuslicensing/bin/mfcesd These are the steps  to beceome root pstest-pshrtstapp602.tmw.com:hrtest:/var/microfocuslicensing/bin> su - psoft Password: Last login: Thu Nov 13 10:16:39 CST 2025 from 172.26.7.95 on pts/3 --------------...