Postgresql

• List databases:

\l

• Connect to database:

\c database

• Show tables:

\dt

#!/bin/bash
echo 'Database,Size (MB)' > /root/database_sizes.csv
for database in $(sudo -u postgres psql -c 'SELECT datname FROM pg_database;'|grep -v '\---'|grep -v rows|grep -v datname);
do
#sudo -u postgres psql -c "SELECT pg_size_pretty( pg_database_size('$i') );" |grep -v '\---'|grep -v row|grep -v pg_size
size=$(sudo -u postgres psql -c "SELECT pg_database_size('$database')/1024/1024 ;" |grep -v '\---'|grep -v row|grep -v column|sed 's/[[:blank:]]//g')
echo $database
echo $size
echo "$database,$size" >> /root/database_sizes.csv
done