Odd disk consumption PostgreSQL 9.6

Post #427,442 by mmoffitt 2/8/19 10:12:47 AM 2/8/19 10:12:47 AM Reply	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 ( bcnu, Mikem It's mourning in America again.
Post #427,446 by lincoln 2/8/19 3:46:00 PM 2/8/19 3:46:00 PM Reply	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
Post #427,452 by crazy 2/9/19 6:38:27 AM 2/9/19 6:38:27 AM Reply	Kind of like this post
Post #427,454 by a6l6e6x 2/9/19 10:18:01 AM 2/9/19 10:18:01 AM Reply	:) ! Alex "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
Post #427,457 by Ashton 2/9/19 4:38:45 PM 2/9/19 4:38:45 PM Reply	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.
Post #427,458 by Another Scott 2/9/19 5:37:24 PM 2/9/19 5:37:24 PM Reply	Just a guess. WAL files not being deleted? https://www.endpoint.com/blog/2014/09/25/pgxlog-disk-space-problem-on-postgres 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! Cheers, Scott.
Post #427,469 by mmoffitt 2/11/19 9:26:10 AM 2/11/19 9:26:10 AM Reply	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. bcnu, Mikem It's mourning in America again.
Post #427,472 by mmoffitt 2/11/19 9:41:37 AM 2/11/19 9:41:37 AM Reply	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. bcnu, Mikem It's mourning in America again.
Post #427,492 by crazy 2/13/19 11:58:13 AM 2/13/19 12:00:23 PM Reply	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? Edited by crazy Feb. 13, 2019, 12:00:23 PM EST Expand All History
Post #427,515 by mmoffitt 2/14/19 11:03:16 AM 2/14/19 11:03:16 AM Reply	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( bcnu, Mikem It's mourning in America again.

Welcome to IWETHEY!