-
Notifications
You must be signed in to change notification settings - Fork 9
PostgreSQL Cheat Sheet
Jorge Padilla edited this page Oct 19, 2023
·
2 revisions
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
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;
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;
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
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'"
Get logs from postgres pod
cd /var/lib/pgsql/data/userdata/log
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);
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"'" }}'