PostgreSQL 9.4 – System Tables – Get Database/Table number, size and other general info for your database

Get information about your existing databases

select * from pg_database;

Show uptime of Postgres server (Not uptime of OS)

select date_trunc ('second', current_timestamp - pg_postmaster_start_time());

View how much space a database consumes

select pg_size_pretty(pg_database_size(current_database()));

View the largest tables in your database

\x
select relname, relpages
from pg_class 
order by relpages desc;

view how large a table is

select pg_size_pretty(pg_relation_size('name_of_table'));

or

\dt+ name_of_table

view how many tables a user owns

select  count(*)  from pg_tables where tableowner = 'seeker';

View how many tables a schema of your database has.

select count(*) from information_schema.tables where table_schema not in ('pg_catalog', 'information_schema');

View how many databases a server has.

select count(*)  from pg_database;

View size of all databases

select sum(pg_database_size(datname)) from pg_database;

List all roles

select * from information_schema.enabled_roles;

List all schemas

select schema_name from information_schema.schemata;
Share Button

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload the CAPTCHA.