psql: Ein paar nützliche Befehle und Queries

create user myuser with password 'very-secure-password';

create database newdatabase with owner myuser;

SELECT t.tablename,
    foo.indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename::text)::regclass)) AS table_size,
    pg_size_pretty(pg_total_relation_size(s.relid::regclass)) AS table_total_size,
    pg_size_pretty(pg_total_relation_size(s.relid::regclass) - pg_relation_size(s.relid::regclass)) AS table_external_size,
    pg_size_pretty(pg_relation_size(quote_ident(foo.indexrelname::text)::regclass)) AS index_size,
        CASE
            WHEN foo.indisunique THEN 'Y'::text
            ELSE 'N'::text
        END AS "index_unique",
    foo.idx_scan AS number_of_scans,
    foo.idx_tup_read AS tuples_read,
    foo.idx_tup_fetch AS tuples_fetched
   FROM pg_tables t
     LEFT JOIN pg_class c ON t.tablename = c.relname
     LEFT JOIN pg_statio_user_tables s ON s.relname = t.tablename
     LEFT JOIN ( SELECT c_1.relname AS ctablename,
            ipg.relname AS indexname,
            x.indnatts AS number_of_columns,
            psai.idx_scan,
            psai.idx_tup_read,
            psai.idx_tup_fetch,
            psai.indexrelname,
            x.indisunique
           FROM pg_index x
             JOIN pg_class c_1 ON c_1.oid = x.indrelid
             JOIN pg_class ipg ON ipg.oid = x.indexrelid
             JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid) foo ON t.tablename = foo.ctablename
  WHERE t.schemaname = 'public'::name
  ORDER BY t.tablename, foo.indexname

select 'ALTER TABLE '|| tablename ||' OWNER TO {newOwner};' FROM pg_tables WHERE schemaname = 'public';

SELECT 'ALTER FUNCTION '
            || quote_ident(n.nspname) || '.' 
            || quote_ident(p.proname) || '(' 
            || pg_catalog.pg_get_function_identity_arguments(p.oid)
            || ') OWNER TO {newOwner};'
          , E'\n' AS _sql
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public';

SELECT 'ALTER SEQUENCE '
            || quote_ident(n.nspname) || '.'
            || quote_ident(c.relname)
            || ' OWNER TO {newOwner};'
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  n.nspname = 'public' AND c.relkind = 'S';