Skip navigation

Category Archives: postgre

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..

SELECT pg_size_pretty(sum(pg_total_relation_size(‘archival.’||table_name))::bigint)
FROM information_schema.tables
WHERE table_schema=’archival’;

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:
SELECT pg_database_size(‘user’);
pg_database_size
——————
58241934

Oh.. thats in Bytes.. Make it nicer.. Get db size in MBs.

SELECT pg_size_pretty(pg_database_size(‘user_table’));
pg_size_pretty
—————-
60 MB

Getting Table size:

SELECT pg_size_pretty(pg_total_relation_size(‘user_table’));
pg_size_pretty
—————-
55 MB

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..