Обсуждение: SOS ---- Could you help me with postgresql.....????

Hi,

Few months ago you helped me with a little problem that i had while trying
to make pg_dumpall...

Now I'm gonna ask your help with this really big problem that I have,
here's the thing:

Recently We Update our Postgresql database server by installing Postgresql
7.4.1, we followed the basic installation steps that appear in the install
file, everything was fine till this morning when we realize that the
partition (/var) where we storage the data (/var/lib/pgsql/data) was full,
this partition is 30GB size!!! could you belive it????. When we checkout
the size of the data directory it was 28GB, at the end it was imposible
for us to startup the database normally so we have to remove the
/var/lib/pgsql/data directory, and restore the db from a previous backup
made with (pg_dumpall) and this time the /var/lib/pgsql/data was 8GB size
that is a more accurate size for our database.

Well till now the story goes to good..

BUT I've been the whole morning monitoring (df -v or df -h) the size of
the partition (/var) and is growing up every moment by a rate of 6kb to
8kb at 11:00 am it was 8GB and now 14:00 is 8.9GB (what a hell is going
on????) if things goes like this next week I'm gonna have the same problem
that today ... please help me

Mr Tom lane ask about which are the files that are growing so fast, well I
can't tell you that all the files whose sizes change appear like 170624,
16384 or 1247 and so on ...

I think I made something stupid I increased the max_connections to 150
(/var/lib/pgsql/data/postgresql.conf), could this be the the source of the
problem???.

Thanks a lot for your help. I'm sorry to bother you but your the only one
"I know" that knows more about Postgresql than I (I'm lost...don't you
think??).

bye

Alvaro Arcila

On Thu, 18 Mar 2004 alvaro@audifarma.com.co wrote:

> Recently We Update our Postgresql database server by installing Postgresql
> 7.4.1, we followed the basic installation steps that appear in the install
> file, everything was fine till this morning when we realize that the
> partition (/var) where we storage the data (/var/lib/pgsql/data) was full,
> this partition is 30GB size!!! could you belive it????. When we checkout
> the size of the data directory it was 28GB, at the end it was imposible
> for us to startup the database normally so we have to remove the
> /var/lib/pgsql/data directory, and restore the db from a previous backup
> made with (pg_dumpall) and this time the /var/lib/pgsql/data was 8GB size
> that is a more accurate size for our database.
>
> Well till now the story goes to good..
>
> BUT I've been the whole morning monitoring (df -v or df -h) the size of
> the partition (/var) and is growing up every moment by a rate of 6kb to
> 8kb at 11:00 am it was 8GB and now 14:00 is 8.9GB (what a hell is going
> on????) if things goes like this next week I'm gonna have the same problem
> that today ... please help me

Does a vacuum full verbose lower the space taken?  If so, can you post its
output?

 Mr. Szabo this is part of the output of the vacuumdb command


 DETAIL:   dead row versions cannot be removed yet.
There were  unused item pointers.
  pages are entirely empty.
 CPU 0.00s/0.00u sec elapsed 0.02 sec.
 INFO:  vacuuming "public.tbl_fact_consumos"
 INFO:  index "ind_fact_consumos" now contains 233211 row versions in 1174
 pages
 DETAIL:   index pages have been deleted,  are currently reusable.
 CPU 0.06s/0.03u sec elapsed 0.53 sec.
 INFO:  "tbl_fact_consumos": found  removable, 233211 nonremovable row
 versions in 5220 pages
 DETAIL:   dead row versions cannot be removed yet.
 There were 1846 unused item pointers.
  pages are entirely empty.
> CPU 0.24s/0.04u sec elapsed 2.19 sec.
 INFO:  vacuuming "public.tbl_reposiciones"
 INFO:  index "uc_tbl_reposiciones" now contains 385590 row versions in
 2727 pages
 DETAIL:  2 index pages have been deleted, 2 are currently reusable.
 CPU 0.24s/0.04u sec elapsed 1.39 sec.
 INFO:  "tbl_reposiciones": found  removable, 385590 nonremovable row
 versions in 7905 pages
 DETAIL:   dead row versions cannot be removed yet.
 There were 3828 unused item pointers.
  pages are entirely empty.
 CPU 0.68s/0.15u sec elapsed 3.73 sec.
 INFO:  vacuuming "public.tbl_saldos_caf"
 INFO:  index "uc_tbl_saldos_caf1" now contains 1045711 row versions in
 7116 pages
 DETAIL:   index pages have been deleted,  are currently reusable.
 CPU 0.66s/0.07u sec elapsed 4.50 sec.
 INFO:  "tbl_saldos_caf": found  removable, 1045711 nonremovable row
 versions in 9942 pages
 DETAIL:   dead row versions cannot be removed yet.
 There were 305538 unused item pointers.
  pages are entirely empty.
 CPU 0.99s/0.22u sec elapsed 12.45 sec.
 INFO:  vacuuming "public.tbl_kardex"

 I have to tell you that we made a cron that goes like this:

 echo Inicio vacum `date` >> /bk/scripts/vacuum.log
 su postgres -c '/var/lib/pgsql/bin/vacuumdb -d dbsa'
 echo Fin vacum `date` >> /bk/scripts/vacuum.log

 This cron activates each day at 23:45, when I look at the log file
 (/bk/scripts/vacuum.log), it looks that it started ok, sometimes this
 operation takes jues few minutes and some others It can take hours, I
 don´t pay to much attention to this because som me day our database has to
 serve too many operations that involves deleting and updating rows in
 several tables and I consider this differences norma. Am I wrong...?

 Here's part of the log file (/bk/scripts/vaccum.log):

 Inicio vacum Thu Mar 11 23:45:00 COT 2004
 Fin vacum Fri Mar 12 01:35:55 COT 2004
 Inicio vacum Fri Mar 12 23:45:00 COT 2004
 Fin vacum Sat Mar 13 01:35:46 COT 2004
 Inicio vacum Sat Mar 13 23:45:00 COT 2004
 Fin vacum Sun Mar 14 00:44:01 COT 2004
 Inicio vacum Sun Mar 14 23:45:00 COT 2004
 Fin vacum Mon Mar 15 00:24:07 COT 2004
 Inicio vacum Mon Mar 15 23:45:00 COT 2004
 Fin vacum Tue Mar 16 01:34:52 COT 2004
 Inicio vacum Tue Mar 16 23:45:00 COT 2004
 Fin vacum Wed Mar 17 02:32:23 COT 2004
 Inicio vacum Wed Mar 17 23:45:00 COT 2004
 Fin vacum Thu Mar 18 01:46:20 COT 2004
 Inicio vacum Fri Mar 19 23:45:00 COT 2004
 Fin vacum Sat Mar 20 00:02:49 COT 2004
 Inicio vacum Sat Mar 20 23:45:00 COT 2004
 Fin vacum Sun Mar 21 00:00:27 COT 2004
 Inicio vacum Sun Mar 21 23:45:01 COT 2004
 Fin vacum Sun Mar 21 23:59:19 COT 2004

>
> On Thu, 18 Mar 2004 alvaro@audifarma.com.co wrote:
>>
>> Recently We Update our Postgresql database server by installing
>> Postgresql
>> 7.4.1, we followed the basic installation steps that appear in the
>> install
>> file, everything was fine till this morning when we realize that the
>> partition (/var) where we storage the data (/var/lib/pgsql/data) was
>> full,
>> this partition is 30GB size!!! could you belive it????. When we
>> checkout
>> the size of the data directory it was 28GB, at the end it was imposible
>> for us to startup the database normally so we have to remove the
>> /var/lib/pgsql/data directory, and restore the db from a previous
>> backup
>> made with (pg_dumpall) and this time the /var/lib/pgsql/data was 8GB
>> size
>> that is a more accurate size for our database.
>>
>> Well till now the story goes to good..
>>
>> BUT I've been the whole morning monitoring (df -v or df -h) the size of
>> the partition (/var) and is growing up every moment by a rate of 6kb to
>> 8kb at 11:00 am it was 8GB and now 14:00 is 8.9GB (what a hell is going
>> on????) if things goes like this next week I'm gonna have the same
>> problem
>> that today ... please help me
>
> Does a vacuum full verbose lower the space taken?  If so, can you post
> its
> output?
>

Hi,

Sorry to bother but what means whe the following error appears

ERROR:  could not access status of transaction 3145728
DETAIL:  could not open file "/var/lib/pgsql/datos/pg_clog/0003": No such
file or directory

I know that the file /var/lib/pgsql/datos/pg_clog/0003 doesn't exist but I
restart postmaster several times and postmaster still iniocates that
condition how can I fix this?


Thanks a lot for your help,

Alvaro