FW: More than 1 pg_database Entry for Database - Thread #2

--_000_68B59BEDCD36854AADBDF17E91B2937AB89DE7F7EXCHMAILstaff in_
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 [at] postgresql.org [mailto:pgsql-admin-owner [at] postgresql=
..org] On Behalf Of Samuel Stearns
Sent: Wednesday, 14 April 2010 10:11 AM
To: pgsql-admin [at] 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 [at] postgresql.org/msg25 978.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_68B59BEDCD36854AADBDF17E91B2937AB89DE7F7EXCHMAILstaff in_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" xmlns=3D"http://ww=
w.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<o:SmartTagType
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags" name=3D"Street=
"/>
<o:SmartTagType namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"address"/>
<o:SmartTagType namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"PersonName"/>

<style>

</style>

</head>

<body lang=3DEN-AU link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

Hi,<o:p></o:p>

<o:p> </o:p>

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

<o:p> </o:p>

Thanks,<o:p></o:p>

<o:p> </o:p>

Sam<o:p></o:p>

<o:p> </o:p>

<div>

<div class=3DMsoNormal align=3Dcenter style=3D'text-align:center'>

<hr size=3D2 width=3D"100%" align=3Dcenter tabindex=3D-1>

</div>

From:=

pgsql-admin-owner [at] postgresql.org [mailto:pgsql-admin-owner [at] postgresql.org] =
On Behalf Of Samuel Stearns 
Sent: Wednesday, 14 April 20=
10
10:11 AM 
To: <st1:PersonName w:st=3D"=
on">pgsql-admin [at] postgresql.org</st1:PersonName> 
Subject: [ADMIN] More than 1
pg_database Entry for Database - Thread #2=
<o:p></o:p>

</div>

<o:p> </o:p>

Howdy,<o:p></o:p>

<o:p> </o:p>

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:<o:p></o:p></s=
pan>

<o:p> </o:p>

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

pg_dumpall: pg_dump fa=
iled
on database "sqlsnbs", exiting<o:p></o:p>

<o:p> </o:p>

I followed your thread here:<o:p></o:p></p=
>

<o:p> </o:p>

<a
href=3D"http://www.mail-archive.com/pgsql-admin [at] postgresql.o rg/msg25978.htm=
l">http://www.mail-archive.com/pgsql-admin [at] postgresql.org/ms g25978.html</a>=
<o:p></o:p>

<o:p> </o:p>

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.<o:p></o:p></=
span>

<o:p> </o:p>

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


<o:p> </o:p>

select xmin, cmin, xmax, cmax, oid, ctid, datname from
pg_database;<o:p></o:p>

<o:p> </o:p>

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 <st1:Street w:st=3D"on=
"><st1:address
w:st=3D"on">Tom Lane</st1:address></st1:Street> 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.<o:p></o:p>

<o:p> </o:p>

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

<o:p> </o:p>

1. Should I be running a scheduled vacuum analyze =
in
addition to the auto-vacuum?<o:p></o:p>

2. Should I be running a scheduled vacuum full?<o:=
p></o:p>

3. Does 8.4 address this problem?<o:p></o:p></span=
>

<o:p> </o:p>

Thank you,<o:p></o:p>

<o:p> </o:p>

Sam<o:p></o:p>

</div>

</body>

</html>

--_000_68B59BEDCD36854AADBDF17E91B2937AB89DE7F7EXCHMAILstaff in_--

Samuel Stearns [ Fr, 16 April 2010 03:33 ] [ ID #2039126 ]

Re: FW: More than 1 pg_database Entry for Database -

Samuel Stearns <SStearns [at] internode.com.au> wrote:

> I am running version 8.3.3 and encountered a problem

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

> 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.

Applying the last 22 months of bug fixes would be a good start.

http://www.postgresql.org/support/versioning

http://www.postgresql.org/docs/8.3/static/release.html

http://www.postgresql.org/download/

Autovacuum is supposed to protect you from wraparound, so it appears
to have fallen down somehow. There were fixes for autovacuum bugs
in 8.3.4 and 8.3.6, so it's entirely possible that just moving to
8.3.somethingrecent will prevent a recurrence.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Kevin Grittner [ Fr, 16 April 2010 18:05 ] [ ID #2039138 ]

Re: FW: More than 1 pg_database Entry for Database -

Thanks very much for the advice, Kevin. I'll be raising a request with our=
sysadmins to upgrade to 8.3.10.

Sam

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner [at] wicourts.gov]
Sent: Saturday, 17 April 2010 1:36 AM
To: Samuel Stearns
Cc: pgsql-admin [at] postgresql.org
Subject: Re: [ADMIN] FW: More than 1 pg_database Entry for Database - Threa=
d #2

Samuel Stearns <SStearns [at] internode.com.au> wrote:

> I am running version 8.3.3 and encountered a problem

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

> 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.

Applying the last 22 months of bug fixes would be a good start.

http://www.postgresql.org/support/versioning

http://www.postgresql.org/docs/8.3/static/release.html

http://www.postgresql.org/download/

Autovacuum is supposed to protect you from wraparound, so it appears
to have fallen down somehow. There were fixes for autovacuum bugs
in 8.3.4 and 8.3.6, so it's entirely possible that just moving to
8.3.somethingrecent will prevent a recurrence.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Samuel Stearns [ So, 18 April 2010 05:21 ] [ ID #2039280 ]