Обсуждение: Orphan Temp Table After PITR
Dear All,
We encountered this for the 3rd time after completing a PITR.
The database starts up as usual and we could carry out the read and write transactions. After a while the following errors start appearing in the log. So we tried running vacuumdb with options All,Freeze,Analyze to see if that can clear the logs. That is why we get the entries "checkpoints are occurring too frequently (11 seconds apart)" and so on. However during and after the vacuumdb is completed the "orphan temp table" entry keeps on printing in the log. Anyone has any idea why this is happening?
2011-04-19 12:12:31 MYT LOG: connection received: host=[local]
2011-04-19 12:12:31 MYT LOG: connection authorized: user=postgres database=np
2011-04-19 12:12:46 MYT LOG: checkpoints are occurring too frequently (11 seconds apart)
2011-04-19 12:12:46 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2011-04-19 12:12:48 MYT LOG: checkpoints are occurring too frequently (2 seconds apart)
2011-04-19 12:12:48 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2011-04-19 12:12:49 MYT LOG: checkpoints are occurring too frequently (1 second apart)
2011-04-19 12:12:49 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_108"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_19"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_35"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_18"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_21"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_6"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_42"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_12"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_30"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_17"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_8"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_44"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_46"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_25"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_5"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_22"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_120"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_36"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_47"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_13"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_14"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_15"."tmp_swsv" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_69"."session_id" in database "np"
2011-04-19 12:13:05 MYT LOG: checkpoints are occurring too frequently (16 seconds apart)
2011-04-19 12:13:05 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
We encountered this for the 3rd time after completing a PITR.
The database starts up as usual and we could carry out the read and write transactions. After a while the following errors start appearing in the log. So we tried running vacuumdb with options All,Freeze,Analyze to see if that can clear the logs. That is why we get the entries "checkpoints are occurring too frequently (11 seconds apart)" and so on. However during and after the vacuumdb is completed the "orphan temp table" entry keeps on printing in the log. Anyone has any idea why this is happening?
2011-04-19 12:12:31 MYT LOG: connection received: host=[local]
2011-04-19 12:12:31 MYT LOG: connection authorized: user=postgres database=np
2011-04-19 12:12:46 MYT LOG: checkpoints are occurring too frequently (11 seconds apart)
2011-04-19 12:12:46 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2011-04-19 12:12:48 MYT LOG: checkpoints are occurring too frequently (2 seconds apart)
2011-04-19 12:12:48 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2011-04-19 12:12:49 MYT LOG: checkpoints are occurring too frequently (1 second apart)
2011-04-19 12:12:49 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_108"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_19"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_35"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_18"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_21"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_6"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_42"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_12"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_30"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_17"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_8"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_44"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_46"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_25"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_5"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_22"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_120"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_36"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_47"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_13"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_14"."session_id" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_15"."tmp_swsv" in database "np"
2011-04-19 12:12:59 MYT LOG: autovacuum: found orphan temp table "pg_temp_69"."session_id" in database "np"
2011-04-19 12:13:05 MYT LOG: checkpoints are occurring too frequently (16 seconds apart)
2011-04-19 12:13:05 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
Hi all, I am attempting to set up slony-i and have run into a minor glitch...apparently whoever designed the database I have inherited didn't consistently build indexes to go along with Primary Keys, or at least that's the error message I have been getting. I am far from confident in my own sqlfu in the catalog tables. Does anyone have a script for identifying tables without indexes that correspond to their PKs? I'm just trying to avoid re-inventing the wheel if I can help it. Thanks, John John P Weatherman Sr Database Administrator Centerstone
Selva manickaraja <mavles78@gmail.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? -Kevin
John P Weatherman <jweatherman91@alumni.wfu.edu> writes: > I am attempting to set up slony-i and have run into a minor > glitch...apparently whoever designed the database I have inherited > didn't consistently build indexes to go along with Primary Keys, or at > least that's the error message I have been getting. I am far from > confident in my own sqlfu in the catalog tables. Does anyone have a > script for identifying tables without indexes that correspond to their > PKs? I'm just trying to avoid re-inventing the wheel if I can help it. At least in Postgres, there is no such animal as a PK (or unique) constraint without an underlying index. Maybe you'd better be more clear about what problem you're having. regards, tom lane
John P Weatherman <jweatherman91@alumni.wfu.edu> writes: > Hi all, > > I am attempting to set up slony-i and have run into a minor > glitch...apparently whoever designed the database I have inherited > didn't consistently build indexes to go along with Primary Keys, or at > least that's the error message I have been getting. I am far from > confident in my own sqlfu in the catalog tables. Does anyone have a > script for identifying tables without indexes that correspond to their > PKs? I'm just trying to avoid re-inventing the wheel if I can help it. Here's an example for you... begin; create schema foo; set search_path to foo; create table haspk (a int primary key); create table missingpk (a int); select relname from pg_class c join pg_namespace n on c.relnamespace = n.oid where nspname = 'foo' and relkind = 'r' and c.oid not in ( select conrelid from pg_constraint where contype = 'p' ); abort; HTH > > Thanks, > > John > > > John P Weatherman > Sr Database Administrator > Centerstone -- Jerry Sievers Postgres DBA/Development Consulting e: gsievers19@comcast.net p: 305.321.1144
--- On Tue, 4/19/11, Jerry Sievers <gsievers19@comcast.net> wrote: > From: Jerry Sievers <gsievers19@comcast.net> > Subject: Re: [ADMIN] PKs without indexes > To: jweatherman91@alumni.wfu.edu > Cc: pgsql-admin@postgresql.org > Date: Tuesday, April 19, 2011, 11:19 AM > John P Weatherman <jweatherman91@alumni.wfu.edu> > writes: > > > Hi all, > > > > I am attempting to set up slony-i and have run into a > minor > > glitch...apparently whoever designed the database I > have inherited > > didn't consistently build indexes to go along with > Primary Keys, or at > > least that's the error message I have been > getting. I am far from > > confident in my own sqlfu in the catalog tables. > Does anyone have a > > script for identifying tables without indexes that > correspond to their > > PKs? I'm just trying to avoid re-inventing the > wheel if I can help it. > > Here's an example for you... > > begin; > > create schema foo; > set search_path to foo; > > create table haspk (a int primary key); > create table missingpk (a int); > > select relname > from pg_class c > join pg_namespace n on c.relnamespace = n.oid > where nspname = 'foo' > and relkind = 'r' > and c.oid not in ( > select conrelid > from pg_constraint > where contype = 'p' > ); > > abort; > > HTH Slony will use any unique index on a table for replication purposes, so the list of tables should come from: select relname from pg_class c join pg_namespace n on c.relnamespace = n.oid where nspname = current_schema() and relkind = 'r' and c.oid not in ( select indrelid from pg_index where indisprimary or indisunique ) order by 1; Bob Lunney
Awesome! Thanks Bob. On Tue, 2011-04-19 at 12:51 -0700, Bob Lunney wrote: > > Slony will use any unique index on a table for replication purposes, so the list of tables should come from: > > select relname > from pg_class c > join pg_namespace n on c.relnamespace = n.oid > where nspname = current_schema() > and relkind = 'r' > and c.oid not in ( > select indrelid > from pg_index > where indisprimary or indisunique > ) > order by 1; > > Bob Lunney > >
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. What needs to be done to counter this unnecessary logging? This machine will be the production secondary DB and nominated as the primary to fail-over. So we need to ensure it is clear of any issue.
Thank you.
Regards,
Selvam
Thank you.
Regards,
Selvam
On Tue, Apr 19, 2011 at 10:00 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
Selva manickaraja <mavles78@gmail.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> [for many different temp tables]
> "pg_temp_108"."session_id" in database "np"There were a lot of temp tables open on the master when you made the
> Anyone has any idea why this is happening?
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?
-Kevin
[Rearranging to eliminate top-posting. List style in embedded responses.] Selva manickaraja < mavles78@gmail.com > wrote: > Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >> Selva manickaraja <mavles78@gmail.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: 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