Re: Compressed Backup too big

On Thu, 2007-11-15 at 20:35 +0200, Andrus wrote:
> "PostgreSQL 8.2.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
> (mingw-special)"
> Database size in disk returned by pg_database_size() is 210 MB
>
> Database compressesed backup file size is now 125 MB.
> This seems too much. I expect compression to decrease size 10 times, also
> indexes are not backed up. A year ago compressed backup size was 9 MB only.
>
> I created query returning biggest tables with and without indexes and found:
>
> 1 pg_toast_22185 95 MB 96 MB
> 2 rid 21 MB 27 MB
> 3 klient 13 MB 19 MB
> 4 mailbox 10 MB 11 MB
> 5 dok 7640 kB 12 MB
> 6 desktop 8080 kB 8200 kB
> 7 strings 5536 kB 6584 kB
> 8 pg_toast_22338 5232 kB 5368 kB
>
> ...
>
> Questions:
>
> 1. Tables are relatively small and thus cannot create 125 MB compressed
> backup file.
> Why backup file sis so big ?

Is this a pg_dump backup or a PITR style backup?
If it's a pg_dump backup, you can open it up in an editor to find out
what's taking so much space.

> 2. How to determine what data is containing in pg_toast_22185 ?
> Why this is so big ?

Could it be that you haven't been VACUUMing properly? Possibly you need
to run a VACUUM FULL if you haven't kept up. If it's a PITR style backup
on 1, that could be the same reason.

To find what table has pg_toast_22185, try:
SELECT relname FROM pg_class WHERE oid=22185

//Magnus