Re: Orphan Temp Table After PITR

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <mavles78(at)gmail(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Orphan Temp Table After PITR
Date: 2011-04-20 14:49:05
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-admin

[Rearranging to eliminate top-posting. List style in embedded

Selva manickaraja < mavles78(at)gmail(dot)com > wrote:
> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Selva manickaraja <mavles78(at)gmail(dot)com> 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:

> 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.


Browse pgsql-admin by date

  From Date Subject
Next Message raghu ram 2011-04-20 15:52:09 Re: [GENERAL] How to uninstall PostgreSQL 8.4 on both Windows XP and Windows 7
Previous Message raghu ram 2011-04-20 14:31:19 Re: [GENERAL] How to uninstall PostgreSQL 8.4 on both Windows XP and Windows 7