Difference between revisions of "PostgreSQL"
From Alessandro's Wiki
Line 8: | Line 8: | ||
* Get list of databases and their sizes: | * 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; | 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; | ||
<source lang=bash> | |||
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 | |||
</source> |
Revision as of 13:39, 11 December 2013
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