PostgreSQL

-- enable formated output
\x on
 
-- show databass
\l
 
-- show tables
\dt
 
-- show user
\du
 
-- drop database
drop database foo;
 
-- exit
\q
 
-- Display tables structure
\d+
 
-- Display tables size
\dT+
select
    relname as "table",
    pg_size_pretty(pg_total_relation_size(relid)) as "size",
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "external size"
from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc;
 
 
-- change database
\c dbname
 
-- login to db
sudo -u postgres psql 
 
-- delete database
drop database bar;
 
-- delete user
drop role foo;
 
-- delete all tables
psql zentras -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname='public'" | psql
 
# dump database
pg_dump -Fc -c db1 > /tmp/$(hostname -f).postgresql.db1.$(date +%Y-%m-%d_%H%M%S).dmp.gz
 
pg_dump -c db1 > /tmp/db1-postgres.sql
zip -9mq /tmp/postgres.sql.zip /tmp/postgres.sql
 
pg_dump -c foo | zip -9q > /tmp/$(hostname -f).foo.sql.zip
 
 
# Ansible
https://opensource.com/article/17/6/ansible-postgresql-operations
 
# Links
https://www3.ntu.edu.sg/home/ehchua/programming/sql/PostgreSQL_GetStarted.html
https://www.niwi.nz/2013/02/17/postgresql-database-table-indexes-size/ - SHOW DATABASE, TABLE AND INDEXES SIZE ON POSTGRESQL
https://www.postgresql.org/docs/9.0/static/app-vacuumdb.html
https://www.postgresql.org/docs/9.1/static/app-reindexdb.html