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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
