Re: [Gnumed-devel] Help menu > About database � size

[Busser, Jim]

On 2013-08-02, at 12:51 AM, Karsten Hilbert <address@hidden> wrote:

> Easily done but it gives no indication whatsoever of the
> size of a backup.
> 
> Space needed for restoration will be the size of the backup
> plus some slack plus the in-PostgreSQL database size which
> one won't know at the time of restoration.

Given the limitations you describe, maybe better is pg_total_relation_size which

        'sums total disk space used by the table including indexes and toasted 
data'

which can be gained from the following adapted from 
http://stackoverflow.com/a/2596678

        SELECT pg_size_pretty(CAST(SUM(size) AS bigint)) AS foo1 FROM (SELECT 
table_schema || '.' || table_name AS table_full_name,
        pg_total_relation_size('"' || table_schema || '"."' || table_name || 
'"') AS size
        FROM information_schema.tables AS foo3
        ORDER BY pg_total_relation_size ('"' || table_schema || '"."' || 
table_name || '"') DESC
        ) AS foo2 ;

on which I kept tripping up, until I

1) added aliases foo1, foo2 and foo3 and

2) figured out that pg_size_pretty needed an integer in place of a numeric
        
-- Jim