Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Manuel Sugawara wrote: > Ok, will do that. Thanks a lot Alvaro. Want me to send more details to > debug the problem?. Yes, it would be good to know why the shared catalogs were not being vacuumed, if you can find that out. I would have guessed that they weren't being vacuumed due to the fact that they can only be vacuumed by a superuser, but in a standalone backend you are always superuser. So if you ran a database-wide vacuum, they should have been processed. What happens if you try "vacuum pg_database", etc? Does the age(relfrozenxid) change? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Manuel Sugawara wrote: > >> A friend is doing the queries right now but it takes time :-(, mean >> time I was wondering if it will be safe to apply the following patch >> just to get the database up and be able to run pg_dumpall: > > Yes, it is safe. Just make sure to get a copy of the database out in > 500k transactions ... Ok, will do that. Thanks a lot Alvaro. Want me to send more details to debug the problem?. Regards, Manuel. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Manuel Sugawara wrote: > A friend is doing the queries right now but it takes time :-(, mean > time I was wondering if it will be safe to apply the following patch > just to get the database up and be able to run pg_dumpall: Yes, it is safe. Just make sure to get a copy of the database out in 500k transactions ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Manuel Sugawara wrote: >> Alvaro Herrera <[EMAIL PROTECTED]> writes: >> >> > Hmm, nope -- take away the relnamespace check, because there is a >> > different namespace for each backend (pg_temp_2, pg_temp_3, etc). >> >> Still no luck, changed the query to: >> >> select relname, age(relfrozenxid) from pg_class join pg_namespace n on >> (n.oid = relnamespace) where nspname ~ '^pg_temp' >> >> and no temp tables showed in any database :-(. Any other idea?. > > None :-( Is there any table with a large age value, regardless of > temp-ness? Does the age of the oldest table correspond to the age of > pg_database.datfrozenxid? The interesting database is the one with the > largest age(pg_database.datfrozenxid). A friend is doing the queries right now but it takes time :-(, mean time I was wondering if it will be safe to apply the following patch just to get the database up and be able to run pg_dumpall: *** postgresql-8.2.6/src/backend/access/transam/varsup.c~ 2006-11-05 16:42:07.0 -0600 --- postgresql-8.2.6/src/backend/access/transam/varsup.c2008-04-08 18:34:51.0 -0500 *** *** 225,231 * vacuuming requires one transaction per table cleaned, we had better be * sure there's lots of XIDs left...) */ ! xidStopLimit = xidWrapLimit - 100; if (xidStopLimit < FirstNormalTransactionId) xidStopLimit -= FirstNormalTransactionId; --- 225,231 * vacuuming requires one transaction per table cleaned, we had better be * sure there's lots of XIDs left...) */ ! xidStopLimit = xidWrapLimit - 50; if (xidStopLimit < FirstNormalTransactionId) xidStopLimit -= FirstNormalTransactionId; Regards, Manuel. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Manuel Sugawara wrote: Going back to your first message I see that I missed something important: > 1: relname = "pg_tablespace" (typeid = 19, len = 64, typmod = -1, byval = f) > 2: age = "2146484675" (typeid = 23, len = 4, typmod = -1, byval = t) > 1: relname = "pg_pltemplate" (typeid = 19, len = 64, typmod = -1, byval = f) > 2: age = "2146484675" (typeid = 23, len = 4, typmod = -1, byval = t) > 1: relname = "pg_shdepend"(typeid = 19, len = 64, typmod = -1, byval = f) > 2: age = "2146484675" (typeid = 23, len = 4, typmod = -1, byval = t) > 1: relname = "pg_shdescription" (typeid = 19, len = 64, typmod = -1, > byval = f) > 2: age = "2146484675" (typeid = 23, len = 4, typmod = -1, byval = t) > 1: relname = "pg_database"(typeid = 19, len = 64, typmod = -1, byval = f) > 2: age = "2146484675" (typeid = 23, len = 4, typmod = -1, byval = t) > 1: relname = "pg_auth_members"(typeid = 19, len = 64, typmod = -1, > byval = f) > 2: age = "2146484675" (typeid = 23, len = 4, typmod = -1, byval = t) These are all shared catalogs AFAIR. Have you vacuumed those? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Manuel Sugawara wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > Hmm, nope -- take away the relnamespace check, because there is a > > different namespace for each backend (pg_temp_2, pg_temp_3, etc). > > Still no luck, changed the query to: > > select relname, age(relfrozenxid) from pg_class join pg_namespace n on > (n.oid = relnamespace) where nspname ~ '^pg_temp' > > and no temp tables showed in any database :-(. Any other idea?. None :-( Is there any table with a large age value, regardless of temp-ness? Does the age of the oldest table correspond to the age of pg_database.datfrozenxid? The interesting database is the one with the largest age(pg_database.datfrozenxid). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Hmm, nope -- take away the relnamespace check, because there is a > different namespace for each backend (pg_temp_2, pg_temp_3, etc). Still no luck, changed the query to: select relname, age(relfrozenxid) from pg_class join pg_namespace n on (n.oid = relnamespace) where nspname ~ '^pg_temp' and no temp tables showed in any database :-(. Any other idea?. Regards, Manuel. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Hmm, nope -- take away the relnamespace check, because there is a > different namespace for each backend (pg_temp_2, pg_temp_3, etc). And > as far as I've seen, most leftover temp tables are on "high" temp > schemas (i.e. those belonging to backends that are only used when the > load is high). Yeah, because the low-numbered ones get recycled first. To have a temp table survive for long enough to create this problem, it's pretty much got to be in a high-numbered temp schema. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Manuel Sugawara wrote: > In each database executed: > > select relname, age(relfrozenxid) from pg_class where relnamespace = > '10406'::oid; > > (note that 10406 is the oid of the pg_temp_1 namespace) none of them > showed temp tables, Is this the correct way?, Any other idea?. Hmm, nope -- take away the relnamespace check, because there is a different namespace for each backend (pg_temp_2, pg_temp_3, etc). And as far as I've seen, most leftover temp tables are on "high" temp schemas (i.e. those belonging to backends that are only used when the load is high). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Manuel Sugawara wrote: > > Hi Manuel, > >> The funny thing is that there was no open transactions, even after >> restarting the cluster the same message was logged. Today, the >> database stopped working as expected: >> >> ERROR: database is shut down to avoid wraparound data loss in database >> "postgres" >> HINT: Stop the postmaster and use a standalone backend to VACUUM in >> "postgres" > > I suggest you look for temp tables that have not been reclaimed. We've > had a couple of reports where leftover temp tables have stopped the > frozen-xid counter from advancing. (They would have a very old > relfrozenxid.) In each database executed: select relname, age(relfrozenxid) from pg_class where relnamespace = '10406'::oid; (note that 10406 is the oid of the pg_temp_1 namespace) none of them showed temp tables, Is this the correct way?, Any other idea?. Regards, Manuel. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Manuel Sugawara <[EMAIL PROTECTED]> writes: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > >> Hi Manuel, > > Hi Alvaro! > >> I suggest you look for temp tables that have not been reclaimed. >> We've had a couple of reports where leftover temp tables have >> stopped the frozen-xid counter from advancing. (They would have a >> very old relfrozenxid.) > > Thank you very much for the suggestion. Any pointers on how to do > that? A quick serch in google didn't show anything relevant. Will look into pg_class, of course. Somehow I was thinking something else. Thanks again. Regards, Manuel. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Hi Manuel, Hi Alvaro! > I suggest you look for temp tables that have not been reclaimed. > We've had a couple of reports where leftover temp tables have > stopped the frozen-xid counter from advancing. (They would have a > very old relfrozenxid.) Thank you very much for the suggestion. Any pointers on how to do that? A quick serch in google didn't show anything relevant. Regards, Manuel. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot use a standalone backend to VACUUM in "postgres""
Manuel Sugawara wrote: Hi Manuel, > The funny thing is that there was no open transactions, even after > restarting the cluster the same message was logged. Today, the > database stopped working as expected: > > ERROR: database is shut down to avoid wraparound data loss in database > "postgres" > HINT: Stop the postmaster and use a standalone backend to VACUUM in "postgres" I suggest you look for temp tables that have not been reclaimed. We've had a couple of reports where leftover temp tables have stopped the frozen-xid counter from advancing. (They would have a very old relfrozenxid.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
