Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 1071F1337B93 for ; Wed, 20 Apr 2011 11:49:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 53339-05 for ; Wed, 20 Apr 2011 14:49:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 7F5781337B68 for ; Wed, 20 Apr 2011 11:49:08 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Wed, 20 Apr 2011 09:49:08 -0500 Message-Id: <4DAEAC11020000250003CA70@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Wed, 20 Apr 2011 09:49:05 -0500 From: "Kevin Grittner" To: Cc: Subject: Re: Orphan Temp Table After PITR References: <4DAEA094020000250003CA5C@gw.wicourts.gov> <4DAEAC11020000250003CA70@gw.wicourts.gov> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/191 X-Sequence-Number: 37372 [Rearranging to eliminate top-posting. List style in embedded responses.] Selva manickaraja < mavles78@gmail.com > wrote: > Kevin Grittner wrote: >> Selva manickaraja wrote: >> >>> We encountered this for the 3rd time after completing a PITR. >> >>> 2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp >>> table "pg_temp_108"."session_id" in database "np" >>> [for many different temp tables] >> >>> Anyone has any idea why this is happening? >> >> There were a lot of temp tables open on the master when you made >> the base backup? It would seem autovacuum is cleaning them up >> when it comes across them. >> >> Do you have some reason to believe that these "LOC" level entries >> represent some problem? > The problem is, the DB keeps on printing these entries the whole > day and bloats the log file. It's 11AM now and already 14,700 > entries of this message in the log and it keeps on increasing. I'm assuming the 1 MB or so of disk space used by those lines isn't a problem. If they're getting in the way of spotting log entries you need to notice, you might consider grep -v as a workaround, but the big question is why you're getting so many. To that end: (1) In previous posts you didn't show any duplicates. Are the 14,700 entries for 14,700 different files, or are files showing up more than once? (2) If you look under your production server's data directory, how many such files can you find? (3) If you still have a copy of the base backup, how many such files were in it? (4) Check the replica to see how many such files are still there. Get a list. Do any log entries show up after that for files which weren't there? (5) Could you show us your configuration? The easiest way is to run the query on this page: http://wiki.postgresql.org/wiki/Server_Configuration > What needs to be done to counter this unnecessary logging? I'm not sure I would want my server to delete such files without mentioning it. It is just LOG level, not even WARNING. Rather than considering the logging to be the problem, I'd first be concerned that it might be providing a valuable clue to something that's a problem with current operations on the master. -Kevin