Re: backup getting larger and larger

Ivan Sergio Borgonovo wrote:
> I still have to investigate if the tables are getting really
> larger... but at a first guess there shouldn't be any good reason to
> see tables getting so large so fast... so I was wondering if
> anything could contribute to make a backup much larger than it was
> other than table containing more records?
>
> The only thing that should have been really changed is the number of
> concurrent connections during a backup.
>
Can we assume that by backup you mean pg_dump/pg_dumpall? If so, then
the change is likely due to increasing data in the database. I have a
daily report that emails me a crude but useful estimate of table
utilization based on this query:

select
relname as table,
to_char(8*relpages, '999,999,999') as "size (kB)",
(100.0*relpages/(select sum(relpages) from pg_class where
relkind='r'))::numeric(4,1) as percent
from
pg_class
where
relkind = 'r'
order by
relpages desc
limit 20;

If, however, you are doing a filesystem backup then table and index
bloat could, indeed, increase your backup size. But more importantly,
you will likely be in for a rude surprise should you ever need to restore.

Cheers,
Steve