IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User

Welcome to IWETHEY!

New Odd disk consumption PostgreSQL 9.6
Hey folks,

I thought I'd post this here because I've not seen this before. One of our dev db servers ran almost completely out of disk yesterday. Being a dev box, it's meant to have only tiny databases on it (

It's mourning in America again.
New details please

Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous.
- - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
New Kind of like this post
New :) !

"There is a cult of ignorance in the United States, and there has always been. The strain of anti-intellectualism has been a constant thread winding its way through our political and cultural life, nurtured by the false notion that democracy means that "my ignorance is just as good as your knowledge."

-- Isaac Asimov
New While whelmed by the intricate technical matters expressed,
I see a verisimilitude with "E G B" koans (like Esher, Gödel, Bach) Eternal-Golden-Braid etc. wherein space-time really Isn't. Your Disk is simply sick&tired of being told:
"just sit there and spin; do Nothing until you hear back".

(It cannot even send--> Nothing --> dev-null.) so, it has found something clever:
how to send Nothing somewhere, while filling the space with ... Nothing.

HTH ..gotta run;
there's a Sinking Ship of State out there; someone forgot to install its ballast on launch ... so it's about to turn down-side-^up^

..You 'Humans!' ... don't know the /half of your suckiness.
New Just a guess.
WAL files not being deleted?


Before we can look at how to fix things, a little background will help. When Postgres is running normally, there is a finite number of WAL files (roughly twice the value of checkpoint_segments) that exist in the pg_xlog directory. Postgres deletes older WAL files, so the total number of files never climbs too high. When something prevents Postgres from removing the older files, the number of WAL files can grow quite dramatically, culminating in the out of space condition seen above. Our solution is therefore two-fold: fix whatever is preventing the old files from being deleted, and clear out enough disk space to allow Postgres to start up again.

The first step is to determine why the WAL files are not being removed. The most common case is a failing archive_command. If this is the case, you will see archive-specific errors in your Postgres log. The usual causes are a failed network, downed remote server, or incorrect copying permissions. You might see some errors like this:

HTH a little - it really is just a guess (I'm not a PGSQLer). Good luck!

New Jesus. Sorry about that.
I don't know how I did that. I'd written up three paragraphs describing this in detail (including confirming that WAL files were being deleted - thanks, Scott) but I must have inadvertently hit the delete key or something because there's *nothing* following the first open parenthesis in my original post! What followed that open parenthesis was the first of very many details.

I'm pressed for time at the moment, but will update later. In brief, vacuum full and reindex did nothing. A pg_dump directly to another host had one db shrink from 48Gig to approx 432MB. In the pg_data directory for that db, fully 43 Gig was consumed and bytes consumed did not change even after truncating tables and re-vacuum. Nothing untoward in any of the log files, the only database on this cluster displaying this goofiness was the aforementioned, nothing out of place, etc. I know that's not much better than no details, but it's all I have time for at the moment. I'm inclined to believe (since Java developers with little to no database experience were allowed to manage this server) that somebody did something in that data directory they thought was smart.

It's mourning in America again.
New Mystery solved, I suppose.
Our SA said he spent the weekend on the postgresql mailing list. He said that after a lot of discussion, other folks had observed this when their database servers ran out of disk and that what I'd done (pg_dump and restore) was the only way to make things nice again.

That was another detail that got erased from my op. This was brought to my attention only *after* the server had run out of disk.

Thanks all.

It's mourning in America again.
New As I was reading I thought: exp/imp
In my oracle days the only thing that would fix this is a "reorg", ie: exp to file, destroy/recreate, and imp back.

Is this a highly updated database? Do records get bigger? Is there a lot of fragmented records and chaining?
Expand Edited by crazy Feb. 13, 2019, 12:00:23 PM EST
New I know very little about this db server.
It's managed by another group who only calls me when they can't figure something out. There is a lot of updating that goes on I'm told, but I have no metrics. I'd never so much as logged into that host until this problem came up. There are a lot of little fiefdoms around here. ;0(

It's mourning in America again.
     Odd disk consumption PostgreSQL 9.6 - (mmoffitt) - (9)
         details please -NT - (lincoln)
         Kind of like this post -NT - (crazy) - (1)
             :) ! -NT - (a6l6e6x)
         While whelmed by the intricate technical matters expressed, - (Ashton)
         Just a guess. - (Another Scott)
         Jesus. Sorry about that. - (mmoffitt)
         Mystery solved, I suppose. - (mmoffitt) - (2)
             As I was reading I thought: exp/imp - (crazy) - (1)
                 I know very little about this db server. - (mmoffitt)

Powered by gravity!
101 ms