Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6FD556321E1 for ; Thu, 15 Apr 2010 22:33:29 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 17807-01 for ; Fri, 16 Apr 2010 01:33:18 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from imail.internode.com.au (exchht01-ext.internode.com.au [203.26.95.185]) by mail.postgresql.org (Postfix) with ESMTP id A330A633535 for ; Thu, 15 Apr 2010 22:33:17 -0300 (ADT) Received: from EXCHMAIL.staff.internode.com.au ([169.254.2.211]) by exchht01.staff.internode.com.au ([fe80::193f:3ff6:d38f:74dc%12]) with mapi; Fri, 16 Apr 2010 11:03:12 +0930 From: Samuel Stearns To: "pgsql-admin@postgresql.org" Date: Fri, 16 Apr 2010 11:03:11 +0930 Subject: FW: More than 1 pg_database Entry for Database - Thread #2 Thread-Topic: More than 1 pg_database Entry for Database - Thread #2 Thread-Index: AcrbazTKR0sA1BO/Rv+HMk9Xc9gkIQBmRNmw Message-ID: <68B59BEDCD36854AADBDF17E91B2937AB89DE7F7@EXCHMAIL.staff.internode.com.au> Accept-Language: en-US, en-AU Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US, en-AU Content-Type: multipart/alternative; boundary="_000_68B59BEDCD36854AADBDF17E91B2937AB89DE7F7EXCHMAILstaffin_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201004/204 X-Sequence-Number: 34298 --_000_68B59BEDCD36854AADBDF17E91B2937AB89DE7F7EXCHMAILstaffin_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, Does anyone have any ideas how I can keep from getting into this duplicate = database scenario? Any advice would be greatly appreciated. Thanks, Sam ________________________________ From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql= .org] On Behalf Of Samuel Stearns Sent: Wednesday, 14 April 2010 10:11 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] More than 1 pg_database Entry for Database - Thread #2 Howdy, I am running version 8.3.3 and encountered a problem with my pg_dump failin= g a couple weeks back with the following error: pg_dump: query returned more than one (2) pg_database entry for database "s= qlsnbs" pg_dumpall: pg_dump failed on database "sqlsnbs", exiting I followed your thread here: http://www.mail-archive.com/pgsql-admin@postgresql.org/msg25978.html in an attempt to resolve the problem and in the end, just like was stated i= n the above thread, I ended up dumping the old Postgres environment into a = new initialized one to resolve the problem. I did not capture the queries I was running at the time of diagnosing but I= can summarize. I ran the following query: select xmin, cmin, xmax, cmax, oid, ctid, datname from pg_database; which returned the same oid and different xmax for each row of the duplicat= e database. One xmax was 0 and the other was 3.7 billion. In your thread = above it was stated by Tom Lane that the large xmax number may indicate a p= roblem with xid wraparound and that the problem row was never vacuumed away= due to broken vacuuming procedures. We were running with auto-vacuum turn= ed on and I verified that it was working by querying pg_stat_all_tables. I was wondering if you could please answer the following for me to help mit= igate this in the future: 1. Should I be running a scheduled vacuum analyze in addition to the auto-= vacuum? 2. Should I be running a scheduled vacuum full? 3. Does 8.4 address this problem? Thank you, Sam --_000_68B59BEDCD36854AADBDF17E91B2937AB89DE7F7EXCHMAILstaffin_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

Does anyone have any ideas how I can k= eep from getting into this duplicate database scenario?  Any advice would = be greatly appreciated.

 

Thanks,

 

Sam

 


From:= pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] = On Behalf Of Samuel Stearns
Sent: Wednesday, 14 April 20= 10 10:11 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] More than 1 pg_database Entry for Database - Thread #2
=

 

Howdy,

 

I am running version 8.3.3 and encountered a problem wit= h my pg_dump failing a couple weeks back with the following error:

 

pg_dump: query returne= d more than one (2) pg_database entry for database "sqlsnbs"<= /span>

pg_dumpall: pg_dump fa= iled on database "sqlsnbs", exiting

 

I followed your thread here:

 

http://www.mail-archive.com/pgsql-admin@postgresql.org/msg25978.html=

 

in an attempt to resolve the problem and in the end, jus= t like was stated in the above thread, I ended up dumping the old Postgres environment into a new initialized one to resolve the problem.

 

I did not capture the queries I was running at the time = of diagnosing but I can summarize.  I ran the following query:=

 

select xmin, cmin, xmax, cmax, oid, ctid, datname from pg_database;

 

which returned the same oid and different xmax for each = row of the duplicate database.  One xmax was 0 and the other was 3.7 billion.  In your thread above it was stated by Tom Lane that the large xmax number= may indicate a problem with xid wraparound and that the problem row was never vacuumed away due to broken vacuuming procedures.  We were running wit= h auto-vacuum turned on and I verified that it was working by querying pg_stat_all_tables.

 

I was wondering if you could please answer the following= for me to help mitigate this in the future:

 

1.  Should I be running a scheduled vacuum analyze = in addition to the auto-vacuum?

2.  Should I be running a scheduled vacuum full?

3.  Does 8.4 address this problem?

 

Thank you,

 

Sam

--_000_68B59BEDCD36854AADBDF17E91B2937AB89DE7F7EXCHMAILstaffin_--