ChatGPT解决这个技术问题 Extra ChatGPT

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');
What is its unit though? Is it in bytes?
It is bytes. :-)
just a side note: select pg_database_size('databaseName')/1024/1024; gives you better human readable megabytes
@Markus just a side note: select 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)

u
user664833

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?
M
Mike Sherrill 'Cat Recall'

You can get the names of all the databases that you can connect to from the "pg_datbase" 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.


Sometimes database contains indexes also. It has some storage value. I am looking for one command that will provide size of the complete database.
@user2151087: pg_database_size() includes the sizes for indexes
In 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…
x
xj9
-- Database Size
SELECT pg_size_pretty(pg_database_size('Database Name'));
-- Table Size
SELECT pg_size_pretty(pg_relation_size('table_name'));

How is that answer different to Mike's?
For 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.
The question was "What is the command to find the size of all the databases?"
C
Community

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.


For me, only \d+ * worked, plain \d+ returned Did not find any relations.
@philpirozhkov Connect to a database first (\c dbname), then do \d+.
j
jwg

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;

The 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.
Fixed: 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;
I 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
MozenRath
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;
$$

why can't it be a single query from pg_database rather than this hideous pl/pgsql?
G
GollyJer

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.


S
SPRBRN

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.


And if you click the Databases tree node (attached to a DB connection) and select the Statistics tab you will be presented with a nice summary of all the Databases and their sizes (third column).
A
Anvesh

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;

a
alexander.polomodov
du -k /var/lib/postgresql/ |sort -n |tail

You 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.
While this might be an accurate answer, it's best practice to include some explanation.
The correct command on CentOS is this one: du -k /var/lib/pgsql/ | sort -n | tail