Reputation: 6654

Postgres DB Size Command

What is the command to find the size of all the databases?

I am able to find the size of a specific database by using following command:

select pg_database_size('databaseName');

Upvotes: 574

Answers (14)

Piotr Farbiszewski

Reputation: 81

-- calculate total size of all PostgreSQL server databases
SELECT pg_size_pretty(SUM(pg_database_size(pg_database.datname))) AS "TotalSize",  
       SUM(pg_database_size(pg_database.datname)) AS "TotalBytes"
from pg_database;

Upvotes: 1

foxiris

Reputation: 3378

Based on Stéphane Gerber's answer, the UI is a little bit different right now, The image comes from, https://www.pgadmin.org/screenshots/

Upvotes: 1

Stéphane Gerber

Reputation: 1508

Using pgAdmin (version 8.2), there is a hidden 'Statistics' menu on the right

Upvotes: 4

hamza._.ghouri

Reputation: 151

You could use the following query in order to get the sizes of all the database in descending order based on their sizes.

SELECT pg_database.datname as "databasename", 
pg_database_size(pg_database.datname)/1024/1024/1024 AS sizegb 
FROM pg_database ORDER by pg_database_size(pg_database.datname) DESC;

For better understanding, explore the following article: How to Find The Size of all Databases in PostgreSQL

Upvotes: 1

Mike Sherrill 'Cat Recall'

Reputation: 95562

You can get the names of all the databases that you can connect to from the "pg_database" system table. Just apply the function to the names, as below.

select t1.datname AS db_name,  
       pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1
order by pg_database_size(t1.datname) desc;

If you intend the output to be consumed by a machine instead of a human, you can cut the pg_size_pretty() function.

Upvotes: 365

Srinivasreddy Jakkireddy

Reputation: 2809

-- Database Size
SELECT pg_size_pretty(pg_database_size('Database Name'));

-- Table Size
SELECT pg_size_pretty(pg_relation_size('table_name'));

Upvotes: 247

Jon Carnes

Reputation: 27

du -k /var/lib/postgresql/ | sort -n | tail

Upvotes: 1

Ashish

Reputation: 6579

You can enter the following psql meta-command to get some details about a specified database, including its size:

\l+ <database_name>

And to get sizes of all databases (that you can connect to):

\l+

Upvotes: 638

GollyJer

Reputation: 26672

From the PostgreSQL wiki.

_{NOTE: Databases to which the user cannot connect are sorted as if they were infinite size.}

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS Size
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

The page also has snippets for finding the size of your biggest relations and largest tables.

Upvotes: 18

Shaun McCready

Reputation: 823

SELECT pg_size_pretty(pg_database_size('name of database'));

Will give you the total size of a particular database however I don't think you can do all databases within a server.

However you could do this...

DO
$$
DECLARE
r   RECORD;
db_size TEXT;
BEGIN
FOR r in
SELECT datname FROM pg_database
WHERE datistemplate = false
LOOP
db_size:= (SELECT pg_size_pretty(pg_database_size(r.datname)));

RAISE NOTICE 'Database:% , Size:%', r.datname , db_size;

END LOOP;
END;
$$

Upvotes: 35

Anoop Sharma

Reputation: 399

Yes, there is a command to find the size of a database in Postgres. It's the following:

SELECT pg_database.datname as "database_name", pg_size_pretty(pg_database_size(pg_database.datname)) AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;

Upvotes: 33

Anvesh

Reputation: 7693

You can use below query to find the size of all databases of PostgreSQL.

Reference is taken from this blog.

SELECT 
    datname AS DatabaseName
    ,pg_catalog.pg_get_userbyid(datdba) AS OwnerName
    ,CASE 
        WHEN pg_catalog.has_database_privilege(datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(datname))
        ELSE 'No Access For You'
    END AS DatabaseSize
FROM pg_catalog.pg_database
ORDER BY 
    CASE 
        WHEN pg_catalog.has_database_privilege(datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(datname)
        ELSE NULL
    END DESC;

Upvotes: 5

owyongsk

Reputation: 2469

Based on the answer here by @Hendy Irawan

Show database sizes:

\l+

e.g.

=> \l+
 berbatik_prd_commerce    | berbatik_prd     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 19 MB   | pg_default | 
 berbatik_stg_commerce    | berbatik_stg     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8633 kB | pg_default | 
 bursasajadah_prd         | bursasajadah_prd | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 1122 MB | pg_default |

Show table sizes:

\d+

e.g.

=> \d+
 public | tuneeca_prd | table | tomcat | 8192 bytes | 
 public | tuneeca_stg | table | tomcat | 1464 kB    |

Only works in psql.

Upvotes: 98

SPRBRN

Reputation: 2462

Start pgAdmin, connect to the server, click on the database name, and select the statistics tab. You will see the size of the database at the bottom of the list.

Then if you click on another database, it stays on the statistics tab so you can easily see many database sizes without much effort. If you open the table list, it shows all tables and their sizes.

Upvotes: 10

Postgres DB Size Command

Answers (14)

Related Questions