postgresql utils

dump_db.sh

#!/bin/bash
pg_dump -h my.host.com -p 5432 -U db_name db_user | gzip > ~/backups/db_name.`date +"%Y.%m.%d_%H-%M"`.sql.gz

load_db.sh

#!/bin/bash
gzip -cd $1 | psql -h 127.0.0.1 -U db_user db_name

tables_size.sql

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;