pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Some advice required please
3+ messages / 2 participants
[nested] [flat]

* Some advice required please
@ 2006-08-30 10:34 Nigel Bishop <Nigel.Bishop@ioko.com>
  2006-08-30 13:48 ` Re: Some advice required please Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Nigel Bishop @ 2006-08-30 10:34 UTC (permalink / raw)
  To: pgsql-admin

Hi,

 

I've just inherited a PG DB (7.3.10 on RH 2.4.21 )

 

Chatting to the person who administered(?) the DB prior to me I
discovered that it had never been vacuumed/analyzed...

 

When performing a vacuum/analyze on the template1 DB I got the following
at the end:

 

WARNING:  Some databases have not been vacuumed in over 2 billion
transactions.

        You may have already suffered transaction-wraparound data loss.

 

Selecting from pg_database shows the following:

 

    datname     | datvacuumxid | datfrozenxid 

----------------+--------------+--------------

 template1      |   2160549932 |   1086808109

 template0      |          427 |          427

 live01.db      |          427 |          427

 stage01.db     |   2153231186 |   1079489364

 

I get the feeling that all is not well with this DB.

 

Virtually every line in the logfile consists of:

 

2006-08-30 11:07:57 WARNING:  ROLLBACK: no transaction in progress

2006-08-30 11:07:57 WARNING:  ROLLBACK: no transaction in progress

2006-08-30 11:07:57 WARNING:  ROLLBACK: no transaction in progress

2006-08-30 11:07:57 WARNING:  ROLLBACK: no transaction in progress

2006-08-30 11:07:57 WARNING:  ROLLBACK: no transaction in progress

2006-08-30 11:07:57 WARNING:  ROLLBACK: no transaction in progress

 

 

Could someone advise me on how to reset the datvacuumxid on the
template1 DB (I don't think it should be this high), also is the message
about data loss really implying that we may have lost data?

 

TIA

 

Nigel

 



Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes.

Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation.

ioko365 Ltd.  VAT reg 656 2443 31. Reg no 3048367. All rights reserved.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Some advice required please
  2006-08-30 10:34 Some advice required please Nigel Bishop <Nigel.Bishop@ioko.com>
@ 2006-08-30 13:48 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2006-08-30 13:48 UTC (permalink / raw)
  To: Nigel Bishop <Nigel.Bishop@ioko.com>; +Cc: pgsql-admin

"Nigel Bishop" <Nigel.Bishop@ioko.com> writes:
> Selecting from pg_database shows the following:

>     datname     | datvacuumxid | datfrozenxid
> ----------------+--------------+--------------
>  template1      |   2160549932 |   1086808109
>  template0      |          427 |          427
>  live01.db      |          427 |          427
>  stage01.db     |   2153231186 |   1079489364

The numbers for template1 and stage01.db look reasonable (but perhaps
you just recently vacuumed them?).  What the message is probably
complaining about is that live01.db has seemingly never been vacuumed.
You can ignore template0, it's not supposed to be touched, but the
others all need vacuuming every so often.

> Could someone advise me on how to reset the datvacuumxid on the
> template1 DB (I don't think it should be this high), also is the message
> about data loss really implying that we may have lost data?

Yes, otherwise we'd not bother to emit it.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Some advice required please
@ 2006-08-30 14:00 Nigel Bishop <Nigel.Bishop@ioko.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Nigel Bishop @ 2006-08-30 14:00 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

OK thanks Tom


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: 30 August 2006 14:48
To: Nigel Bishop
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Some advice required please 

"Nigel Bishop" <Nigel.Bishop@ioko.com> writes:
> Selecting from pg_database shows the following:

>     datname     | datvacuumxid | datfrozenxid
> ----------------+--------------+--------------
>  template1      |   2160549932 |   1086808109
>  template0      |          427 |          427
>  live01.db      |          427 |          427
>  stage01.db     |   2153231186 |   1079489364

The numbers for template1 and stage01.db look reasonable (but perhaps
you just recently vacuumed them?).  What the message is probably
complaining about is that live01.db has seemingly never been vacuumed.
You can ignore template0, it's not supposed to be touched, but the
others all need vacuuming every so often.

> Could someone advise me on how to reset the datvacuumxid on the
> template1 DB (I don't think it should be this high), also is the
message
> about data loss really implying that we may have lost data?

Yes, otherwise we'd not bother to emit it.

			regards, tom lane


Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes.

Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation.

ioko365 Ltd.  VAT reg 656 2443 31. Reg no 3048367. All rights reserved.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-08-30 14:00 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-30 10:34 Some advice required please Nigel Bishop <Nigel.Bishop@ioko.com>
2006-08-30 13:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-30 14:00 Re: Some advice required please Nigel Bishop <Nigel.Bishop@ioko.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox