In Below article you will find some very good postgres queries that might find useful
and can use in your daily work
Create table from another table with conditions
CREATE TABLE new_table AS
SELECT
*
FROM
main_table e2
WHERE
e2.eventname in ('Register', 'Profile');
Insert records in new table from main table with conditions
INSERT INTO new_table
SELECT * FROM main_table
where eventname = 'Register';
Check the size (as in disk space) of all databases:
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END;
Check the size (as in disk space) of each table:
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC;
Get particular database size
SELECT pg_size_pretty( pg_database_size('TestDatabase') );
Get particular table size
SELECT pg_size_pretty( pg_total_relation_size('testtable') );
show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
FROM pg_stat_activity
WHERE current_query != '' AND current_query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
kill running query
SELECT pg_cancel_backend(procpid);
kill idle query
SELECT pg_terminate_backend(procpid);
vacuum command
VACUUM (VERBOSE, ANALYZE);
all database users
select * from pg_stat_activity where current_query not like '<%';
all databases and their sizes
select * from pg_user;
all tables and their size, with/without indexes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;
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;
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;
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;
Dump database on remote host to file
pg_dump -U username -h hostname databasename > dump.sql
Import dump into existing database
psql -d newdb -f dump.sql