PostgreSQL

Potente e storico server SQL by RedHat

• Query al volo su un database Postgres

export PGPASSWORD="password"
psql -t -A -h <hostname> <database> <utente> -c "SELECT nome FROM ladri WHERE categoria LIKE '%mafioso%'";
Berlusconi

• Get list of databases and their sizes:

 SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size DESC;

tables=`$PCOMMAND -c "select tablename from pg_tables;"`

host_src=10.1.1.1
host_dst=10.1.1.2
PCOMMAND="psql -t -A  -Upostgres ipaylater_db"

tables=`$PCOMMAND -h$host_src -c "SELECT * FROM information_schema.tables WHERE table_type != 'VIEW'"|awk -F"|" '{print $2"."$3}'`

IFS=$'\n'
for TABLE in $tables ;do
    unset IFS
    count_src=`$PCOMMAND -h$host_src -c "select count(*) from $TABLE;"`
    count_dst=`$PCOMMAND -h$host_dst -c "select count(*) from $TABLE;"`
    if [ $count_src -ne $count_dst ];then
      echo -e "different record count betwen $host_src ($count_src)\t and $host_dst ($count_dst)\t in table $TABLE"
    fi
done