Useful PostgreSQL Queries and Commands

PostgreSQL Queries and Commands: Export_Import.sql

-- Import(FROM) / Export(TO) CSV file from/into a table
-- Ref: https://www.postgresql.org/docs/current/sql-copy.html
-- If processing all columns, no need column specification
-- If CSV file don't include header, remove 'HEADER' from below query
-- For Export, can also specify Query, instead of Table & Column name
COPY table_name (column_1, column_2, column_3, column_5) 
[FROM/TO] 'csv_file_location' DELIMITER ',' CSV HEADER QUOTE '"' ESCAPE '"'

-- Dump database on remote host to file
-- Ref: https://www.postgresql.org/docs/current/app-pgdump.html
-- For schema only dumping, use parameter `-s` (`-s` or `--schema-only`)
-- For data only dumping, use parameter `-a` (`-a` or `--data-only`)
-- For specifiying some of the tables, use parameter `-t table_01 -t table_02 ......` (`-t` or `--table`)
-- For excluding some of the tables, use parameter `-T table_01 -T table_02 ......` (`-T` or `--exclude-table`)
-- For faster process run parallal operation (`-j` or `--jobs`) [Caution: CPU intensive]
$ pg_dump --dbname=database_name --port=port_address --host=host_name --username=user_name --jobs=number_of_jobs > backup.sql

-- Import dump into existing database using pg_restore
-- Ref: https://www.postgresql.org/docs/current/app-pgrestore.html
-- Supports options as above in pg_dump
$ pg_restore --dbname=database_name --port=port_address --host=host_name --username=user_name --jobs=number_of_jobs backup.sql

-- Import dump into existing database using psql
-- Ref: https://www.postgresql.org/docs/current/app-psql.html
$ psql  --dbname=database_name --port=port_address --host=host_name --username=user_name < backup.sql

-- Dump & Import in same command (like copy)
$ pg_dump <pg_dump command parameter> | psql <psql command parameter>

PostgreSQL Queries and Commands: Functions.sql

-- updated_at column function
CREATE FUNCTION update_updated_at_column() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
  BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
  END;
$$;
-- add 'updated_at' column in any table,
updated_at timestamp without time zone DEFAULT now()
-- then create trigger to attach to table 'table_name'
CREATE TRIGGER table_name_updated_at_modtime
    BEFORE UPDATE 
    ON public.table_name
    FOR EACH ROW
    EXECUTE PROCEDURE public.update_updated_at_column();
------------------------------------------------------------------
------------------------------------------------------------------
-- Dump whole database to CSV
-- Ref: https://stackoverflow.com/questions/17463299/export-postgres-database-into-csv-file
CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
declare
   tables RECORD;
   statement TEXT;
begin
FOR tables IN 
   SELECT (table_schema || '.' || table_name) AS schema_table
   FROM information_schema.tables t INNER JOIN information_schema.schemata s 
   ON s.schema_name = t.table_schema 
   WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')
   AND t.table_type NOT IN ('VIEW')
   ORDER BY schema_table
LOOP
   statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';
   EXECUTE statement;
END LOOP;
return;  
end;
$$ LANGUAGE plpgsql;
-- use it as (this will create one csv file per table, in /path/to/dir/)
SELECT db_to_csv('/path/to/dir/');
------------------------------------------------------------------
------------------------------------------------------------------

PostgreSQL Queries and Commands: Keys.sql

-- Sync your table sequence (it happens mostly when you restore from a backup)
SELECT SETVAL(
  (SELECT PG_GET_SERIAL_SEQUENCE('"table_name"', 'id')), 
  COALESCE((SELECT (MAX("id") + 1) FROM table_name), 1), 
  FALSE);
--or
SELECT SETVAL(
  'table_name_id_seq', 
  COALESCE((SELECT (MAX("id") + 1) FROM table_name), 1), 
  FALSE);

-- Get all primary keys & Columns
select kcu.table_schema,
       kcu.table_name,
       tco.constraint_name,
       kcu.ordinal_position as position,
       kcu.column_name as key_column
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu 
     on kcu.constraint_name = tco.constraint_name
     and kcu.constraint_schema = tco.constraint_schema
     and kcu.constraint_name = tco.constraint_name
where tco.constraint_type = 'PRIMARY KEY'
order by kcu.table_schema,
         kcu.table_name,
         position;


-- Get the total count of rows for a given table_name (when count gets real costly)
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables where relname ='table_name'

PostgreSQL Queries and Commands: Operations.sql

-- Ref: https://www.postgresql.org/docs/current/functions-admin.html
-- Kill all running connection
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()  
  AND pid <> pg_backend_pid();

-- Kill all the Idle sessions
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'DatabaseName'
AND pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND state_change < current_timestamp - INTERVAL '15' MINUTE;

-- Kill running query
SELECT pg_cancel_backend(procpid);

-- Kill idle query
SELECT pg_terminate_backend(procpid);

-- Drop all tables/sequences/enums
DO $$ DECLARE
    r RECORD;
BEGIN
    -- drop tables
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
	-- drop any leftover/custom sequences
	FOR r IN (SELECT sequencename from pg_sequences WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP SEQUENCE IF EXISTS ' || quote_ident(r.sequencename) || ' CASCADE';
    END LOOP;
	-- drop enums
	FOR r IN (SELECT distinct typname FROM pg_type JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid) LOOP
        EXECUTE 'DROP TYPE IF EXISTS ' || quote_ident(r.typname) || ' CASCADE';
    END LOOP;
END $$;

PostgreSQL Queries and Commands: Stats - Indexes & Caches.sql

-- cache hit rates (should not be less than 0.99)
SELECT 
SUM(heap_blks_read) as heap_read, 
SUM(heap_blks_hit)  as heap_hit, 
(SUM(heap_blks_hit) - SUM(heap_blks_read)) / SUM(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

-- Cache Hit Ratio (perfectly hit_ration should be > 90%)
SELECT sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio 
FROM pg_stat_database;

-- table index usage rates (should not be less than 0.99)
SELECT relname, 
  CASE WHEN (seq_scan + idx_scan) != 0
    THEN 100 * idx_scan / (seq_scan + idx_scan) 
    ELSE 0
  END AS percent_of_times_index_used,
  n_live_tup AS rows_in_table
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

-- Write Activity(index usage)(hot_rate should be close to 100)
select s.relname, 
pg_size_pretty(pg_relation_size(relid)), 
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) - coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes, 
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0 then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate, 
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(d+)') as r(v) limit 1) AS fillfactor 
from pg_stat_all_tables s join pg_class c ON c.oid=relid 
ORDER BY total_writes DESC LIMIT 50;

-- Does table needs an Index
SELECT relname, 
seq_scan-idx_scan AS too_much_seq, 
CASE WHEN seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, 
pg_relation_size(relname::regclass) AS rel_size, 
seq_scan, idx_scan 
FROM pg_stat_all_tables 
WHERE schemaname='public' AND pg_relation_size(relname::regclass)>80000 
ORDER BY too_much_seq DESC;

-- Get indexes by tables
select
    t.relname as table_name,
    i.relname as index_name,
    string_agg(a.attname, ',') as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname not like 'pg_%'
group by  
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;

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

-- Unused Indexes
SELECT relname AS table_name, 
indexrelname AS index_name, 
idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass))
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
AND idx_scan = 0
AND idx_tup_read = 0
AND idx_tup_fetch = 0
ORDER BY pg_relation_size(indexrelname::regclass) DESC;

PostgreSQL Queries and Commands: Stats - Locks.sql

-- See all locks
SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted 
FROM pg_locks l, pg_stat_all_tables t 
WHERE l.relation = t.relid ORDER BY relation asc;

-- Get all the locking query
select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

-- Get all the locking queries with session
SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

PostgreSQL Queries and Commands: Stats - Others.sql

-- Ref: https://www.postgresql.org/docs/current/monitoring-stats.html
-- Get current DB Sessions
SELECT count(*) as stat_count, state 
FROM pg_stat_activity 
WHERE "query"::text not ilike '%pg_stat_activity%' 
GROUP BY "state"

-- Anomalies
-- c_commit_ratio should be > 95%
-- c_rollback_ratio should be < 5%
-- deadlocks should be close to 0
-- conflicts should be close to 0
-- temp_files and temp_bytes watch out for them
SELECT datname, (xact_commit)/nullif(xact_commit+xact_rollback,0) as c_commit_ratio, 
(xact_rollback)/nullif(xact_commit+xact_rollback, 0) as c_rollback_ratio, 
deadlocks, conflicts, temp_files, pg_size_pretty(temp_bytes) ss
FROM pg_stat_database;

-- Get Replication Stat
SELECT * FROM "pg_stat_replication"

-- Dirty Pages(maxwritten_clean and buffers_backend_fsync better be = 0)
SELECT buffers_clean, maxwritten_clean, buffers_backend_fsync 
FROM pg_stat_bgwriter;

-- Sequential Scans(seq_tup_avg should be < 1000)
SELECT relname, 
pg_size_pretty(pg_relation_size(relname::regclass)) as size, 
seq_scan, seq_tup_read, 
seq_scan / seq_tup_read as seq_tup_avg 
FROM pg_stat_user_tables 
WHERE seq_tup_read > 0 
ORDER BY 3,4 
DESC LIMIT 5;

-- Checkpoints
SELECT 'bad' as checkpoints 
FROM pg_stat_bgwriter 
WHERE checkpoints_req > checkpoints_timed;

PostgreSQL Queries and Commands: Stats - Sizes.sql

-- all database users
SELECT * FROM pg_stat_activity WHERE query not like '<%';

-- Get size for all tables
SELECT table_name, pg_size_pretty(pg_relation_size(quote_ident(table_name))) 
FROM "information_schema"."tables" 
WHERE "table_schema" = 'public' order by pg_relation_size(quote_ident(table_name)) DESC; 

-- Get all DB sizes
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
order by pg_database_size(datname) desc;

-- Get list of schema with sizes (relative and absolute)
SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint) as absolute_size,
       (sum(table_size) / pg_database_size(current_database())) * 100 as relative_size
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name

PostgreSQL Queries and Commands: Stats - SlowQueries.sql

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

-- Queries which are running for more than '9 seconds'
SELECT now() - query_start as "runtime", usename, datname, state, query 
FROM pg_stat_activity 
WHERE now() - query_start > '9 seconds'::interval 
ORDER BY runtime DESC;

PostgreSQL Queries and Commands: Vacuuming.sql

-- Last Vacuum and Analyze time
select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

-- Total number of dead tuples need to be vacuumed per table
select n_dead_tup, schemaname, relname from pg_stat_all_tables;

-- Total number of dead tuples need to be vacuumed in DB
select sum(n_dead_tup) from pg_stat_all_tables;

-- vacuum command
-- Ref: https://www.postgresql.org/docs/current/sql-vacuum.html
VACUUM (VERBOSE, ANALYZE);