How to get the percentage of Postgre database storage space through EF Core in Asp.net Core

Question

How to get the percentage of Postgre database storage space through EF Core in Asp.net Core

Navigation

#1 by (0 votes)

1

I need to create in my application a feature that checks the database ( PostGreSQL ) for the percentage of storage space used to be displayed on a chart.

I have a generic repository, and I thought about creating a GetStorage() function, but I have no idea how this should be done, because I do not know if EF Core has this feature, should that be done using Script SQL ?

Something similar to the code below, but returning the percentage:

public decimal GetStorage()
{
    var valor = Db.FromSql("SELECT pg_size_pretty(pg_database_size('SistemaComercial'));";
    return valor;
}

asp.net entity-framework postgresql asp.net-mvc-5

asked by anonymous 19.03.2018 / 00:07

1 answer

Unit Testing - How to test a try-with-resources? External site connection for data search [closed]

score 0 · Accepted Answer

The physical space, in bytes, occupied by the database SistemaComercial can be calculated using the pg_database_size() function:

SELECT pg_database_size( 'SistemaComercial' );

To get the free space of a given partition and / or drive, you can write a stored procedure using the PL/Python , let's see:

CREATE FUNCTION calcular_espaco_livre( path TEXT )
RETURNS BIGINT AS
$BODY$
    import os
    info = os.statvfs( path )
    return info.f_bsize * info.f_bavail
$BODY$
LANGUAGE plpythonu;

So you can write another stored procedure in PL/PgSQL to solve your problem:

CREATE FUNCTION obter_espaco_ocupado( dbnome TEXT )
RETURNS REAL AS
$BODY$
DECLARE
    diretorio TEXT;
    livre BIGINT;
    ocupado BIGINT;
BEGIN
    -- Recupera o diretorio onde a database está instalada
    SELECT setting FROM pg_settings WHERE name = 'data_directory' INTO diretorio;

    -- Calcula o espaco livre desse diretorio
    SELECT calcular_espaco_livre( diretorio ) INTO livre;

    -- Calcula o espaco ocupado pela database
    SELECT pg_database_size( dbnome ) INTO ocupado;

    -- Calcula a porcentagem ocupada
    RETURN (100.0 / livre) * ocupado;
END;
$BODY$
LANGUAGE plpgsql;

Logo:

SELECT obter_espaco_ocupado( 'SistemaComercial' );