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');
asked Sep 20, 2013 at 0:22
13 Answers
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+
-
and if i want to know only one schema size? is there a way to do that instead by using this approach? Commented Oct 19, 2020 at 1:51
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.
answered Sep 20, 2013 at 0:39
Mike Sherrill 'Cat Recall'Mike Sherrill 'Cat Recall'
94.2k18 gold badges128 silver badges190 bronze badges
-
Sometimes database contains indexes also. It has some storage value. I am looking for one command that will provide size of the complete database. Commented Sep 20, 2013 at 4:12
-
13@user2151087:
pg_database_size()includes the sizes for indexes– user330315Commented Sep 20, 2013 at 6:00 -
4In case anyone was wondering, this query provides exactly the same values as
\l+. The output format is easier to read, though (less columns). Tradeoff between writability and readability… Commented May 28, 2020 at 10:07
-- Database Size
SELECT pg_size_pretty(pg_database_size('Database Name'));
-- Table Size
SELECT pg_size_pretty(pg_relation_size('table_name'));
answered Sep 20, 2013 at 6:55
-
5How is that answer different to Mike's?– user330315Commented Sep 20, 2013 at 6:58
-
10For future me and others landing here, I'll save you the trouble: This one is shorter and for named database/table where Mike's is for all databases on the server of which the latter answers better the original question. Commented Feb 28, 2019 at 8:46
-
2The question was "What is the command to find the size of all the databases?" Commented May 24, 2022 at 18:17
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.
-
2For me, only
\d+ *worked, plain\d+returnedDid not find any relations.Commented Mar 14, 2018 at 10:14 -
5@philpirozhkov Connect to a database first (
\c dbname), then do\d+.– chappjcCommented Jan 15, 2019 at 15:53
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;
-
6The ordering is wrong in this function. It can't tell the difference between human readable formats. For example database of size 7151 KB comes before database of size 7 GB. Commented Apr 25, 2018 at 5:30
-
1Fixed:
SELECT database_name, pg_size_pretty(size) from (SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname) AS size FROM pg_database ORDER by size DESC) as ordered;– MichaelCommented Apr 26, 2019 at 7:12 -
3I think you need the "raw" size for sorting only. I used this instead of a sub-query
SELECT pg_database.datname AS "DB Name", pg_size_pretty(pg_database_size(pg_database.datname)) AS "Size" FROM pg_database ORDER BY (pg_database_size(pg_database.datname)) DESC;.– M-DahabCommented Mar 10, 2020 at 9:50
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;
$$
-
3why can't it be a single query from
pg_databaserather than this hideous pl/pgsql? Commented Jul 1, 2019 at 22:09
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.
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.
-
4And if you click the
Databasestree node (attached to a DB connection) and select theStatisticstab you will be presented with a nice summary of all the Databases and their sizes (third column).– zlosterCommented Feb 15, 2016 at 14:22 -
2The same technique works when one wants to know the Table size. Also works as of today (2023) and on pgAdmin 4 Commented Apr 20, 2023 at 21:50
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;
Using pgAdmin (version 8.2), there is a hidden 'Statistics' menu on the right
answered Jan 22 at 10:51
du -k /var/lib/postgresql/ | sort -n | tail
-
4You might want to add more context about the assumptions this makes re where the database is storing its data, what the output of this will look like, etc. Commented May 16, 2018 at 17:27
-
4While this might be an accurate answer, it's best practice to include some explanation.– sniperdCommented May 16, 2018 at 17:27
-
The correct command on CentOS is this one:
du -k /var/lib/pgsql/ | sort -n | tail– FerimanCommented Mar 9, 2020 at 14:24
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
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/
select pg_database_size('databaseName')/1024/1024;gives you better human readable megabytesselect pg_size_pretty(pg_database_size('databaseName'));gives you better human readable sizes, no matter the magnitude. (It's a bit hard to read MB when you're in the TB)