WRITELOOP

POSTGRESQL TIPS

2020 September 24

pg_restore

parameters useful for a quick restore, leaving, e.g., constraints to be ported later on:

pg_restore --section=pre-data ....  # restores the schemas first
pg_restore --section=data ........  # restores the data
pg_restore --section=post-data ...  # restores the constraints and indexes

How to get all indexes on a database

select
t.relname as table_name,
i.relname as index_name,
array_to_string(array_agg(a.attname), ', ') as column_names
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'
group by
t.relname,
i.relname
order by
t.relname,
i.relname;

Total disk space used by tables including indexes and toasted data

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 table constraints

SELECT * FROM information_schema.table_constraints WHERE table_name='my_table';

Get all tables from schema

\dt [schema-name].*;

TAGS

POSTGRESQL