Database

From Mosuma
Jump to: navigation, search

PostgreSQL

Binary Backup/Restore
pg_dump -Fc database_name_here > database.bak # compressed binary format
pg_restore -Fc -C database.bak # create and restore from compressed binary format
pg_restore -Fc database.bak # restore into existing DB from compressed binary format 
Streaming Replication

Tips

From https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database

  • listing size of all databases
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
  • listing size of all tables
SELECT
    relname AS objectname, 
    relkind AS objecttype, 
    reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
    FROM pg_class 
    WHERE relpages >= 8
    ORDER BY relpages DESC;


  • finding largest table
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
  • finding size of a table
SELECT pg_size_pretty(pg_relation_size(‘users’));