X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 58726D1B273 for ; Mon, 12 Jul 2004 16:43:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12425-08 for ; Mon, 12 Jul 2004 19:43:51 +0000 (GMT) Received: from alvarezp.ods.org (unknown [200.77.196.211]) by svr1.postgresql.org (Postfix) with ESMTP id C6015D1B23F for ; Mon, 12 Jul 2004 16:43:48 -0300 (ADT) Received: (from apache@localhost) by alvarezp.ods.org (8.11.6/8.11.6) id i6CJhRI08760; Mon, 12 Jul 2004 12:43:27 -0700 From: Octavio Alvarez Piza X-Authentication-Warning: alvarezp.ods.org: apache set sender to alvarezp@alvarezp.ods.org using -f Received: from 63.84.67.25 (SquirrelMail authenticated user alvarezp) by alvarezp.ods.org with HTTP; Mon, 12 Jul 2004 12:43:26 -0700 (PDT) Message-ID: <2615.63.84.67.25.1089661406.squirrel@alvarezp.ods.org> Date: Mon, 12 Jul 2004 12:43:26 -0700 (PDT) Subject: Disk usage To: pgsql-admin@postgresql.org User-Agent: SquirrelMail/1.4.0-1.7.x MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=PRIORITY_NO_NAME X-Spam-Level: X-Archive-Number: 200407/190 X-Sequence-Number: 14258 I have a database to which I re-create daily via ODBC. I only drop the data, not the index definitions or the data definitions. I use TRUNCATE to drop the data. I REINDEX each table afterwards. For some reason, my VACUUM job was not running, so the DB was not being VACUUMed at all. Today I ran out of disk space. I could free about 12 MB so I could start doing VACUUMs. I did a VACUUM and VACUUM FULL in each database in the cluster. I opened a postgres -O -P and did a REINDEX in each database. I did a VACUUM and VACUUM FULL once more. Normally, if I dump and re-init, the database comes down to 150 MB, but currently is taking more than 1.2 GB. Version is 7.3.2. Unfortunately, dump/re-init in a short-term is not an option (and therefore, upgrading isn't either). What else could I try to deflate it? [root@pgsql data]# find -size +25000 -printf "%p \t%11s\n"; ./base/16977/106415 170672128 ./base/16977/106416 170778624 ./base/16977/106417 64020480 ./base/16977/106418 171925504 ./base/16977/106419 149831680 ./base/16977/106468 179437568 ./base/16977/106486 149012480 ./pg_xlog/00000002000000FC 16777216 ./pg_xlog/00000002000000FD 16777216 ./pg_xlog/0000000300000000 16777216 ./pg_xlog/00000002000000F9 16777216 ./pg_xlog/00000002000000FA 16777216 ./pg_xlog/00000002000000FB 16777216 ./pg_xlog/00000002000000FE 16777216 The problem seems not to be on the WAL files. tbc_bet=# select relname, reltype, relpages from pg_class order by relpages desc limit 10; relname | reltype | relpages -----------------------------------------+---------+---------- t_subjects_in_kardex_pkey | 0 | 21904 i_t_subjects_in_kardex__ext_group | 0 | 20987 i_t_subjects_in_kardex__ext_period | 0 | 20847 i_t_subjects_in_kardex__ext_student | 0 | 20834 i_t_grades__ext_subject_in_kardex | 0 | 18290 t_grades_pkey | 0 | 18190 i_t_subjects_in_kardex__ext_materia | 0 | 7815 t_subjects_in_kardex | 17051 | 747 t_students_pkey | 0 | 432 i_t_students__ext_plan | 0 | 262 (10 rows) t_* except t_*_pkey are tables. i_t___ are indexes by field (as of reltype = 0). t__pkey are primary keys indexes. field names = ext_ are foreign keys. (A query involving relfilenode matches the above files with the above relations) tbc_bet=# select count(*) from t_subjects_in_kardex; count ------- 84937 (1 row) tbc_bet=# \d t_subjects_in_kardex; Table "public.t_subjects_in_kardex" Column | Type | Modifiers --------------------+--------------+-------------------------------------------------------------------- id | integer | not null default nextval('public.t_subjects_in_kardex_id_seq'::text) old_id | integer | ext_student | integer | ext_subjet | integer | ext_period | integer | ext_group | integer | final_grade | integer | tipo | character(1) | eer | date | Indexes: t_subjects_in_kardex_pkey primary key btree (id), i_t_subjects_in_kardex__ext_student btree (ext_alumno), i_t_subjects_in_kardex__ext_period btree (ext_ciclo), i_t_subjects_in_kardex__ext_group btree (ext_grupo), i_t_subjects_in_kardex__ext_subject btree (ext_materia), i_t_subjects_in_kardex__old_id btree (old_id) Foreign Key constraints: $1 FOREIGN KEY (ext_student) REFERENCES t_students(id) ON UPDATE NO ACTION ON DELETE NO ACTION, $2 FOREIGN KEY (ext_period) REFERENCES t_periods(id) ON UPDATE NO ACTION ON DELETE NO ACTION, $3 FOREIGN KEY (ext_subject) REFERENCES t_subjects(id) ON UPDATE NO ACTION ON DELETE NO ACTION, $4 FOREIGN KEY (ext_group) REFERENCES t_groups(id) ON UPDATE NO ACTION ON DELETE NO ACTION (Field and table names are translated. I tried not to misspell any name.)