Signiant Support

How Do I Determine the Database Size From the Command Line Interface? Print


Problem

How can I determine the size of the Signiant database from the command line interface?

Resolution

To determine the database size and tables that are used the most, execute the following queries. (Note, these queries do not use the Signiant Health Checker report.)

To determine the database size:
Linux:
# psql -U postgres -d DTM_DB -c "SELECT pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database WHERE pg_database.datname = 'DTM_DB'";

Windows:
C:\> psql.exe -U postgres -d DTM_DB -c "SELECT pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database WHERE pg_database.datname = 'DTM_DB'";
 
To determine the tables which use the most space:
Linux: 
# psql -U postgres -d DTM_DB -c "SELECT relname,relpages,reltuples,relpages*8/1024 as size_in_MB FROM pg_class ORDER BY relpages DESC LIMIT 25";
 
Windows
C:\> psql.exe -U postgres -d DTM_DB -c "SELECT relname,relpages,reltuples,relpages*8/1024 as size_in_MB FROM pg_class ORDER BY relpages DESC LIMIT 25";
 
The psql application is installed as part of the Signiant Manager. By default on Linux it is located in "/usr/signiant/dds/db/pgsql/bin" and on Windows in "C:\>Program Files\Signiant\Mobilize\db\pgsql\bin" directory.
 

Applies To

Signiant Manager version 8.x.0+.