Well, so my requirement this time was to get the size of all tables in a particular schema. HOW?
AFAIK, there was no direct way to fulfill my requirement. So wrote my own query..
Where ‘archival’ is the schema name. Checkout the type-casting.
Short explanation: sum() returns int type, but pg_size_pretty() takes bigint as the param data type. Hence the type casting.
NB:- when copying the query, take care of the quotes.
Getting Database size:
Oh.. thats in Bytes.. Make it nicer.. Get db size in MBs.
Getting Table size:
Ok.. so the problem was taking a sql dump in PostgreSQL db of only selected entries in a particular table. Google’d it.. but all in vain.. then came up with a simple workaround…
First create a temporary table in the database as follows:
create table <temporary_table_name> as select * from <table_name> where <some condition>;
This will create a table.
Well.. thats it.. now u can take a dump of that table and then delete it subsequently.
./pg_dump -U <username> -p <port> -d <db_name>
-t <temp_table_name> -f <file_name>
N.B. MySql has a ‘where’ option in mysqldump.. so its quite straight forward there..