Connect

psql -h localhost -p 5432 -d search -U searchuser

Using oc cli

oc rsh $(oc get pods -n open-cluster-management|grep 'search-postgres'|awk '{print $1}') psql -d search -U searchuser

Queries

Count resources and edges

SELECT count(*) from search.resources;
SELECT count(*) from search.edges;

Count resources by cluster

SELECT count(*) as count, cluster FROM search.resources GROUP BY cluster HAVING count(*)>1 order by count desc;

Debug Managed Clusters

SELECT count(*) FROM search.resources WHERE data->>'kind' = 'ManagedCluster';
SELECT uid, cluster, data->>'name' FROM search.resources WHERE data->>'kind' = 'ManagedCluster';

Debug Applications

SELECT * FROM search.resources WHERE data->>'kind' = 'Application';
SELECT * from search.edges WHERE sourcekind='Application' LIMIT 10;
SELECT * from search.edges WHERE cluster='cluster1' AND sourcekind='Application' LIMIT 5;

Delete data for cluster (sno00002)

DELETE from search.resources WHERE cluster = 'sno00002' AND uid != 'cluster__sno00002';

Drop tables

DROP TABLE search.resources;
DROP TABLE search.edges;
DROP SCHEMA search CASCADE;

Indexes

List all index

\dS search.resources
\dS search.edges
SELECT * from pg_indexes where tablename not like 'pg%';

Create an index

CREATE INDEX IF NOT EXISTS edges_cluster_idx ON search.edges USING btree (cluster)
CREATE INDEX IF NOT EXISTS edges_edgetype_idx ON search.edges USING btree (edgetype)

Drop an index

DROP INDEX search.edges_cluster_idx CASCADE;

Show 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;

Show index usage

with table_stats as (
select psut.relname,
  psut.n_live_tup,
  1.0 * psut.idx_scan / greatest(1, psut.seq_scan + psut.idx_scan) as index_use_ratio
from pg_stat_user_tables psut
order by psut.n_live_tup desc
),
table_io as (
select psiut.relname,
  sum(psiut.heap_blks_read) as table_page_read,
  sum(psiut.heap_blks_hit)  as table_page_hit,
  sum(psiut.heap_blks_hit) / greatest(1, sum(psiut.heap_blks_hit) + sum(psiut.heap_blks_read)) as table_hit_ratio
from pg_statio_user_tables psiut
group by psiut.relname
order by table_page_read desc
),
index_io as (
select psiui.relname,
  psiui.indexrelname,
  sum(psiui.idx_blks_read) as idx_page_read,
  sum(psiui.idx_blks_hit) as idx_page_hit,
  1.0 * sum(psiui.idx_blks_hit) / greatest(1.0, sum(psiui.idx_blks_hit) + sum(psiui.idx_blks_read)) as idx_hit_ratio
from pg_statio_user_indexes psiui
group by psiui.relname, psiui.indexrelname
order by sum(psiui.idx_blks_read) desc
)
select ts.relname, ts.n_live_tup, ts.index_use_ratio,
  ti.table_page_read, ti.table_page_hit, ti.table_hit_ratio,
  ii.indexrelname, ii.idx_page_read, ii.idx_page_hit, ii.idx_hit_ratio
from table_stats ts
left outer join table_io ti
  on ti.relname = ts.relname
left outer join index_io ii
  on ii.relname = ts.relname
order by ti.table_page_read desc, ii.idx_page_read desc;

Triggers

List triggers

\dS search.resources
SELECT  event_object_table AS table_name ,trigger_name FROM information_schema.triggers GROUP BY table_name , trigger_name ORDER BY table_name ,trigger_name;

Drop triggers

DROP TRIGGER resources_delete on search.resources;
DROP TRIGGER resources_upsert on search.resources;

Show trigger function

\sf search.intercluster_edges

Configuration

SHOW <config-setting>;

Change configuration

SET max_parallel_workers_per_gather = 0;	-- Default 2
SET work_mem = 	'32MB';				-- Default 16MB  - MUST RESTART
SET shared_buffers = '8GB' ;			-- Default 64MB - MUST RESTART

psql -d search -U searchuser -c "ALTER ROLE searchuser set work_mem='128MB'"

Logs

Get logs from postgres pod

cd /var/lib/pgsql/data/userdata/log

Database Stats and debug

Show database size

select datname, pg_size_pretty(pg_database_size(datname)) from pg_database order by pg_database_size(datname) desc;

Show query activity by state

select count(*),state FROM pg_stat_activity group by 2;

Show running queries

SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'  ORDER BY query_start desc;

Show idle queries

SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE state = 'idle' AND query NOT ILIKE '%pg_stat_activity%'  ORDER BY query_start desc;

Cancel a running query

SELECT pg_cancel_backend(pid);

Kill an idle query

SELECT pg_terminate_backend(pid);

Patch the search-postgres configmap

POSTGRESQL_CONF="ssl = 'on' \n ssl_cert_file = '/sslcert/tls.crt' \n ssl_key_file = '/sslcert/tls.key' \n max_connections = 120"  
oc patch cm search-postgres --type merge -p '{"data":{"postgresql.conf": "'${POSTGRESQL_CONF}'" }}' 

oc patch cm search-postgres -n open-cluster-management --type merge -p '{"data":{"postgresql.conf": "'"ssl = 'on' \n ssl_cert_file = '/sslcert/tls.crt' \n ssl_key_file = '/sslcert/tls.key' \n max_connections = 120"'" }}'