The Postgres pg_database_size(name) built-in system function retunrs the disk space used by the database with the specified name.
Although YugabyteDB is Postgres compatable and does also have this function, it is not yet supported.
Note: There is an open Github issue (#19158) to add support.
In the mean time, you can create your own user defined function that basically does the same thing.
Code for the function:
CREATE OR REPLACE FUNCTION my_pg_database_size()
RETURNS BIGINT
AS
$$
DECLARE
sql_statement RECORD;
ObjectSize BIGINT := 0;
DataBaseSize BIGINT := 0;
BEGIN
-- Tables
FOR sql_statement IN
SELECT 'SELECT pg_table_size(''' || schemaname || '.' || tablename || ''');' AS ddl FROM pg_tables WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
LOOP
EXECUTE sql_statement.ddl INTO ObjectSize;
DataBaseSize := DataBaseSize + ObjectSize;
END LOOP;
-- Indexes
FOR sql_statement IN
SELECT 'SELECT pg_table_size(''' || n.nspname || '.' || c.relname || ''');' AS ddl FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND c.relkind IN ('i','I','') AND NOT i.indisprimary
LOOP
EXECUTE sql_statement.ddl INTO ObjectSize;
DataBaseSize := DataBaseSize + ObjectSize;
END LOOP;
RETURN DataBaseSize;
END;
$$
LANGUAGE plpgsql;
Essentially, the user function calls the pg_table_size function for all of the tables and indexes in the current database and returns the total size (in bytes) as a sum of each.
Example:
yugabyte=# \c some_db
You are now connected to database "some_db" as user "yugabyte".
some_db=# \d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | test1 | table | yugabyte
public | test2 | table | yugabyte
public | test3 | table | yugabyte
(3 rows)
some_db=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-------------+-------+----------+-------
public | test1_pkey | index | yugabyte | test1
public | test2_c2 | index | yugabyte | test2
public | test2_pkey | index | yugabyte | test2
public | test3_c2_c3 | index | yugabyte | test3
public | test3_pkey | index | yugabyte | test3
(5 rows)
some_db=# SELECT my_pg_database_size();
my_pg_database_size
---------------------
80540743
(1 row)
some_db=# SELECT my_pg_database_size() / 1048576 as total_size_mb;
total_size_mb
---------------
76
(1 row)
Have Fun!
