SELECT relname, reltuples, pg_relation_size(relname) FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid) WHERE relkind = 'r' AND n.nspname = 'public' ORDER BY relname;

postgresql SQL to show count of ALL tables (relations) including relation-size

Postgresql specific SQL - to show count of ALL tables including relation-size (pg_relation_size = used space on filesystem) - might need a VACUUM ANALYZE before showing all counts correctly !

1
By: alvinx
2009-08-17 11:46:34

These Might Interest You

  • This command drops all the tables of the 'public' schema from the database. First, it constructs a 'drop table' instruction for each table found in the schema, then it pipes the result to the psql interactive command. Useful when you have to recreate your schema from scratch in development for example. I mainly use this command in conjunction with a similar command which drop all sequences as well. Example : psql -h <pg_host> -p <pg_port> -U <pg_user> <pg_db> -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname='public'" | psql -h <pg_host> -p <pg_port> -U <pg_user> <pg_db> psql -h <ph_host> -p <pg_port> -U <pg_user> <pg_db> -t -c "select 'drop sequence \"' || relname || '\" cascade;' from pg_class where relkind='S'" | psql -h <ph_host> -p <pg_port> -U <pg_user> <pg_db> See it scripted here : https://gist.github.com/cuberri/6868774#file-postgresql-drop-create-sh


    0
    psql -h <pg_host> -p <pg_port> -U <pg_user> <pg_db> -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname='public'" | psql -h <pg_host> -p <pg_port> -U <pg_user> <pg_db>
    cuberri · 2013-12-11 15:39:56 0
  • bs = buffer size (basically defined the size of a "unit" used by count and skip) count = the number of buffers to copy (16m * 32 = 1/2 gig) skip = (32 * 2) we are grabbing piece 3...which means 2 have already been written so skip (2 * count) i will edit this later if i can to make this all more understandable


    2
    dd if=inputfile of=split3 bs=16m count=32 skip=64
    jearsh · 2010-02-21 10:09:46 0
  • Pulls all instances of table out of information schema, executes a SELECT COUNT(*) on each table/database instance, and then strips out any empty tables. Show Sample Output


    -1
    TABLE_NAME=YYZ ; for DATABASE in $(echo "SELECT TABLE_SCHEMA FROM information_schema.tables WHERE TABLE_NAME='$TABLE_NAME'" | mysql -N) ; do echo -n "$DATABASE: " ; echo "SELECT COUNT(*) FROM $TABLE_NAME" | mysql $DATABASE -N ; done | fgrep -v ': 0'
    cathdrwg · 2011-12-08 16:31:36 0
  • SIZE is the number of gigabytes and the file name is at the end. Random data is generated by encrypting /dev/zero, similar to other techniques posted on here.


    2
    SIZE=1; dd if=/dev/zero bs=1M count=$((SIZE*1024)) | pv -pters $((SIZE*1024*1024*1024)) | openssl enc -aes-256-ctr -pass pass:"$(dd if=/dev/urandom bs=128 count=1 2>/dev/null | base64)" -nosalt > randomfile
    kennethjor · 2014-08-30 01:46:51 0
  • Sometimes jittery data hides trends, performing a rolling average can give a clearer view.


    3
    awk 'BEGIN{size=5} {mod=NR%size; if(NR<=size){count++}else{sum-=array[mod]};sum+=$1;array[mod]=$1;print sum/count}' file.dat
    mungewell · 2009-05-29 00:07:24 0
  • Finds all tables that need optimising and loops through them, running optimise against them. This works server-wide, on all databases and tables.


    0
    for table in $(echo "select concat(TABLE_SCHEMA, '.', TABLE_NAME) from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free > 0" | mysql --skip-column-names); do echo "optimize table ${table}" | mysql; done;
    Xiol · 2011-06-28 21:09:57 0

What do you think?

Any thoughts on this command? Does it work on your machine? Can you do the same thing with only 14 characters?

You must be signed in to comment.

What's this?

commandlinefu.com is the place to record those command-line gems that you return to again and again. That way others can gain from your CLI wisdom and you from theirs too. All commands can be commented on, discussed and voted up or down.

Share Your Commands



Stay in the loop…

Follow the Tweets.

Every new command is wrapped in a tweet and posted to Twitter. Following the stream is a great way of staying abreast of the latest commands. For the more discerning, there are Twitter accounts for commands that get a minimum of 3 and 10 votes - that way only the great commands get tweeted.

» http://twitter.com/commandlinefu
» http://twitter.com/commandlinefu3
» http://twitter.com/commandlinefu10

Subscribe to the feeds.

Use your favourite RSS aggregator to stay in touch with the latest commands. There are feeds mirroring the 3 Twitter streams as well as for virtually every other subset (users, tags, functions,…):

Subscribe to the feed for: