Re: Transaction wraparound problem with database postgres

Поиск
Список
Период
Сортировка
От Markus Wollny
Тема Re: Transaction wraparound problem with database postgres
Дата
Msg-id 28011CD60FB1724DBA4442E38277F62607CD83B6@hermes.computec.de
обсуждение исходный текст
Ответ на Transaction wraparound problem with database postgres  ("Markus Wollny" <Markus.Wollny@computec.de>)
Ответы Re: Transaction wraparound problem with database postgres
Список pgsql-general
Дерево обсуждения
Hi!
 
Just some more info, hoping that it helps with a diagnosis:
 
 1: datname     (typeid = 19, len = 64, typmod = -1, byval = f)
         2: age (typeid = 23, len = 4, typmod = -1, byval = t)
         3: datfrozenxid        (typeid = 28, len = 4, typmod = -1, byval = t)
        ----
         1: datname = "postgres"        (typeid = 19, len = 64, typmod = -1, byval = f)
         2: age = "-2147321465" (typeid = 23, len = 4, typmod = -1, byval = t)
         3: datfrozenxid = "1835116837" (typeid = 28, len = 4, typmod = -1, byval = t)
        ----
Then I issue a vacuum:
1: datname     (typeid = 19, len = 64, typmod = -1, byval = f)
         2: age (typeid = 23, len = 4, typmod = -1, byval = t)
         3: datfrozenxid        (typeid = 28, len = 4, typmod = -1, byval = t)
        ----
         1: datname = "postgres"        (typeid = 19, len = 64, typmod = -1, byval = f)
         2: age = "-2147321383" (typeid = 23, len = 4, typmod = -1, byval = t)
         3: datfrozenxid = "1835116837" (typeid = 28, len = 4, typmod = -1, byval = t)
 
It worries me, that 'age' is negative.
 
Kind regards
 
   Markus

Von: Markus Wollny
Gesendet: Fr 21.03.2008 21:50
An: pgsql-general@postgresql.org
Betreff: Transaction wraparound problem with database postgres

Hi!
 
My database cluster has just stopped working. I get the following message:
psql: FATAL:  Datenbank nimmt keine Befehle an, um Datenverlust in Datenbank »postgres« wegen Transaktionsnummernüberlauf zu vermeiden
TIP:  Halten Sie den Postmaster an und verwenden Sie ein Standalone-Backend, um VACUUM in der Datenbank »postgres« auszuführen.
 
I did as suggested, stopped tzhe postmaster and started a single backend on database 'postgres'; I issued a "VACCUM" there.
su postgres -c "/opt/pgsql/bin/postgres --single -D /var/lib/pgsql/data postgres" 

backend> VACUUM

However, this doesn't seem to help - I receive lots and lots of messages like this:

<2008-03-21 21:43:27 CET - 11845: @>WARNUNG:  Datenbank »postgres« muss innerhalb von 4294805194 Transaktionen gevacuumt werden
<2008-03-21 21:43:27 CET - 11845: @>TIPP:  Um ein Abschalten der Datenbank zu vermeiden, führen Sie in »postgres« ein VACUUM über die komplette Datenbank aus.

i.e. "database 'postgres' must be vacuumed within 4294805194 transactions."

That's what I just did, but the problem persists. Whenever I issue a 'vacuum', the number of transactions simply decreases.

This is PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)

I am absolutely lost about what to do now - and it's a puzzle how this could have happened in the first place. I have configured autovaccum AND I run a vacuum verbose analyze over all databases every single night. What do I do now? Is there some alternative to reinit and going back to the last dump?

Urgent help would be very much appreciated.

Kind regards

   Markus

Computec Media AG

Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)

Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann

Vorsitzender des Aufsichtsrates: Jürg Marquard

Umsatzsteuer-Identifikationsnummer: DE 812 575 276

В списке pgsql-general по дате отправления:

Предыдущее
От: "Edward Blake"
Дата:
Сообщение: MySQL to Postgres question
Следующее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: Transaction wraparound problem with database postgres