Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres run out of disk space

Postgres run out of disk space

From
Wenzhe Zhou
Date:
On one of my Linux machine, Postgres server ran out of disk space on its data partition.
To trace the problem, I turn on Postgres debug log (set debug_level = 2 in conf file).
We use all of default setting for WAL.
Accoring to the debug log (postmaster.log), there are only read operations (select query)
in one hour and twenty minutes. While in that period of time, I saw log messages like
"DEBUG:  recycled transaction log file xxxxxx" in every one minute and used disk space
increase 105 MB. It looks like the server is busy to write to DB, and transaction log files are recycled in every one
minutes.How could this happen? I can't figure out why the 
database was updated since there is no write (update/delete/insert) query in that
period of time.

We run Vacuum Full and Reindex to reclaim disk space only in pre-scheduled time.
To prevent from running out of disk space, we stop of all of clients which send
write query when disk usage hit a RED light (for example disk usage is more than 90%)
before the scheduled database maintenance time. Since Vacuum Full and Reindex need
some working disk space, how much free disk space I need to reserve?

Thanks in advance.

Wenzhe

Re: Postgres run out of disk space

From
Tom Lane
Date:
Wenzhe Zhou <wzhou@cisco.com> writes:
> Accoring to the debug log (postmaster.log), there are only read
> operations (select query) in one hour and twenty minutes. While in
> that period of time, I saw log messages like "DEBUG: recycled
> transaction log file xxxxxx" in every one minute and used disk space
> increase 105 MB. It looks like the server is busy to write to DB, and
> transaction log files are recycled in every one minutes. How could
> this happen?

AFAIK, a purely read-only stream of transactions will consume negligible
WAL space (a few bytes for every clog page == 32K transactions is the
largest drain I can think of).  I think you are missing some writing
transactions somewhere.

            regards, tom lane

Re: Postgres run out of disk space

From
"scott.marlowe"
Date:
On Mon, 30 Dec 2002, Wenzhe Zhou wrote:

> On one of my Linux machine, Postgres server ran out of disk space on its data partition.
> To trace the problem, I turn on Postgres debug log (set debug_level = 2 in conf file).
> We use all of default setting for WAL.
> Accoring to the debug log (postmaster.log), there are only read operations (select query)
> in one hour and twenty minutes. While in that period of time, I saw log messages like
> "DEBUG:  recycled transaction log file xxxxxx" in every one minute and used disk space
> increase 105 MB. It looks like the server is busy to write to DB, and transaction log files are recycled in every one
minutes.How could this happen? I can't figure out why the 

Are you sure there isn't a query like 'update table set a=a+1' kind of
statement going on somewhere?  Those queries, that touch everything so
innocently, are often the source of these kinds of problems.