Postgres DB Size Command

Question

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

User · Answer

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;

User · Answer

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

User · Answer

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

User · Answer

-- Database Size SELECT pg size pretty pg database size  Database Name     -- Table Size SELECT pg size pretty pg relation size  table name

User · Answer

Based on the answer here by  Hendy Irawan  Show database sizes    l   e g     gt   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     gt   d   public   tuneeca prd   table   tomcat   8192 bytes     public   tuneeca stg   table   tomcat   1464 kB         Only works in psql

User · Answer

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

User · Answer

You can get the names of all the databases that you can connect to from the  quot pg datbase quot  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

User · Answer

du -k  var lib postgresql   sort -n  tail

User · Answer

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

User · Answer

You can enter the following psql meta-command to get some details about a specified database  including its size   l   lt database name gt   And to get sizes of all databases  that you can connect to    l

[sql] Postgres DB Size Command

Examples related to sql

Examples related to postgresql

Examples related to postgresql-9.2