[Postgresql Cheat Sheet] #database #postgresql

PostgreSQL

# Fix PGADMIN on MAC OS X
pg_ctl -D /Users/mkarrer/Library/Application\ Support/Postgres/var-9.6 -l /Users/mkarrer/Library/Application\ Support/Postgres/var-9.6/postgresql.log restart

# Show database sizes on disk
psql
\l+

# https://wiki.postgresql.org/wiki/Index_Maintenance
# Index and table size
SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

# Data Directory
show data_directory;


# Table Spaces
# https://www.dbrnd.com/2016/09/postgresql-create-or-change-default-tablespace-of-table-to-migrate-on-ssd-solid-state-drive-better-performance/
# https://www.postgresql.org/docs/9.6/sql-createtablespace.html
# http://blog.lodeblomme.be/2008/03/15/move-a-postgresql-database-to-a-different-tablespace/
# http://forums.devshed.com/postgresql-help/714581-multiple-postgres-data-files-post2481916.html
CREATE TABLESPACE ts_job_archive OWNER sosync_gui LOCATION '/opt/postgresql/data/archive/9.6/main';