Atikh's DBA blog
  • Home
  • Oracle
  • MySQL
  • MongoDB
  • PostgreSQL
  • Snowflake
  • About Me
  • Contact Us

PostgreSQL : How to check size of the database

 Atikh Shaikh     PostgreSQL     No comments   

As a PostgreSQL database administrator, you may come across situations where you want to check the size of the full database, here is a quick solution for the same

Here is my PostgreSQL cluster details, there is one database called technodb, and I need to get the size of this database, here is how we can check

List all databases in postgreSQL


There is a command called pg_database_size('<database name>'), here is how we can execute it

postgres=# select pg_database_size('technodb');

 pg_database_size

------------------

          7773319

(1 row)

by default, it will result in bytes and if you want to bring a result in megabytes (MB), here is how you can do it 

postgres=# select pg_database_size('technodb')/1024/1024;

 ?column?

----------

        7

(1 row)

There is another way to check the size of the database, just execute the command \l+ <database name>

postgres=# \l+ technodb

                                                         List of databases

   Name   |  Owner   | Encoding |      Collate       |       Ctype        | Access privileges |  Size   | Tablespace | Description

----------+----------+----------+--------------------+--------------------+-------------------+---------+------------+-------------

 technodb | postgres | UTF8     | English_India.1252 | English_India.1252 |                   | 7591 kB | pg_default |

(1 row)

and if you want to get the size of all databases together, here is how you can fetch details using pg_database view

 

postgres=# select datname as Database , (pg_database_size(datname))/1024/1024 as Database_size_MB from pg_database;

 database  | database_size_mb

-----------+------------------

 postgres  |                7

 mydb      |                7

 template1 |                7

 template0 |                7

 technodb  |                7

 

[Also read - Types of shutdown in PostgreSQL database]


Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit

Snowflake : show databases command optimized

 Atikh Shaikh     Snowflake     No comments   

In snowflake, we use the "show databases" command to see details about default and created databases, this command shows a lot of details about databases, what if we just need the names of the database not whole details about them, after digging out I found this "databases" is table present in INFORMATION_SCHEMA schema, so we can query the details from "databases" tables. 

To fetch details you need to select the data warehouse, database, and schema 

 

technosnow#(no warehouse)@(no database).(no schema)>use WAREHOUSE TECHNO_WS;

+----------------------------------+

| status                           |

|----------------------------------|

| Statement executed successfully. |

+----------------------------------+

1 Row(s) produced. Time Elapsed: 0.644s

technosnow#TECHNO_WS@(no database).(no schema)>use SNOWFLAKE;

+----------------------------------+

| status                           |

|----------------------------------|

| Statement executed successfully. |

+----------------------------------+

1 Row(s) produced. Time Elapsed: 0.810s

technosnow#TECHNO_WS@SNOWFLAKE.(no schema)>use schema INFORMATION_SCHEMA;

+----------------------------------+

| status                           |

|----------------------------------|

| Statement executed successfully. |

+----------------------------------+

1 Row(s) produced. Time Elapsed: 0.313s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>desc databases;

+----------------+-------------------+--------+-------+---------+-------------+------------+-------+------------+-----------------------------------------------------------------+-------------+

| name           | type              | kind   | null? | default | primary key | unique key | check | expression | comment                                                         | policy name |

|----------------+-------------------+--------+-------+---------+-------------+------------+-------+------------+-----------------------------------------------------------------+-------------|

| DATABASE_NAME  | VARCHAR(16777216) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | Name of the database                                            | NULL        |

| DATABASE_OWNER | VARCHAR(16777216) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | Name of the role that owns the schema                           | NULL        |

| IS_TRANSIENT   | VARCHAR(3)        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Whether this is a transient table                               | NULL        |

| COMMENT        | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Comment for this database                                       | NULL        |

| CREATED        | TIMESTAMP_LTZ(9)  | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Creation time of the database                                   | NULL        |

| LAST_ALTERED   | TIMESTAMP_LTZ(9)  | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Last altered time of the database                               | NULL        |

| RETENTION_TIME | NUMBER(9,0)       | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Number of days that historical data is retained for Time Travel | NULL        |

+----------------+-------------------+--------+-------+---------+-------------+------------+-------+------------+-----------------------------------------------------------------+-------------+

7 Row(s) produced. Time Elapsed: 0.402s

 

Here you see the columns for the "databases" table, now you can fetch anything you want

 

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>select database_name from databases;

+-----------------------+

| DATABASE_NAME         |

|-----------------------|

| EXERCISE_DB           |

| SNOWFLAKE_SAMPLE_DATA |

| TECHNODB              |

+-----------------------+

3 Row(s) produced. Time Elapsed: 1.093s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>


[Also Read - Different Editions of Snowflake]

[Also Read - How to Undrop database]

 

Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit
Newer Posts Older Posts Home

Author

Atikh Shaikh
View my complete profile

Categories

  • MongoDB (18)
  • Oracle 12c (30)
  • Oracle12cR2 New Feature (3)
  • PostgreSQL (20)
  • RMAN (10)
  • Snowflake (8)
  • mysql (23)
  • oracle (74)

Blog Archive

  • ►  2018 (38)
    • ►  November (25)
    • ►  December (13)
  • ►  2019 (33)
    • ►  January (15)
    • ►  February (6)
    • ►  March (2)
    • ►  April (5)
    • ►  May (5)
  • ►  2020 (5)
    • ►  April (1)
    • ►  May (2)
    • ►  July (2)
  • ►  2021 (8)
    • ►  June (3)
    • ►  July (3)
    • ►  August (1)
    • ►  December (1)
  • ▼  2022 (33)
    • ►  May (3)
    • ►  June (10)
    • ►  July (3)
    • ►  August (4)
    • ►  September (8)
    • ►  October (3)
    • ▼  November (2)
      • Snowflake : show databases command optimized
      • PostgreSQL : How to check size of the database
  • ►  2023 (14)
    • ►  February (1)
    • ►  April (5)
    • ►  May (2)
    • ►  June (1)
    • ►  September (1)
    • ►  October (1)
    • ►  December (3)
  • ►  2024 (5)
    • ►  January (2)
    • ►  March (3)
  • ►  2025 (6)
    • ►  March (1)
    • ►  April (3)
    • ►  May (2)

Popular Posts

  • ORA-29283: invalid file operation: unexpected "LFI" error (1509)[29437]
    I was trying to export the schema in my windows PC, it got stuck with below error    C:\Users\shaik\Videos\technodba exp>expdp userid...
  • PostgreSQL : How to get data directory location for PostgreSQL instance
    Sometimes, you start working on a PostgreSQL instance but forget about the data directory, here we will discuss different methods to know th...
  • Oracle Dataguard Broker Configuration (DGMGRL)
    Data Guard Broker is a command-line interface that makes managing primary and standby databases easy. DBA can use a single command to switch...
  • ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
    In previous articles, we have learned about user creation and grants  in MySQL in detail, but there are a few privileges called global priv...
  • Oracle 23ai : Use of NOVALIDATE Constraints in IMPDP
    While performing impdp operations in the Oracle database, Oracle performs validation checks for every constraint on the imported table, that...

Labels

oracle Oracle 12c mysql PostgreSQL MongoDB oracle 19c Oracle23c oracle19c Orale PDB-CDB oracle12c python AWS Oracle ASM Virtualbox pluggable database storage engine

Pages

  • Disclaimer
  • Privacy Policy

Follow TechnoDBA

Copyright © Atikh's DBA blog | Powered by Blogger