More than 1 pg_database Entry for Database

--_000_68B59BEDCD36854AADBDF17E91B2937AB89DE232EXCHMAILstaff in_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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_68B59BEDCD36854AADBDF17E91B2937AB89DE232EXCHMAILstaff 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"address"/>
<o:SmartTagType namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"Street"/>

<style>

</style>

</head>

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

<div class=3DSection1>

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 billi=
on.
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 awa=
y
due to broken vacuuming procedures. We were running with 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_68B59BEDCD36854AADBDF17E91B2937AB89DE232EXCHMAILstaff in_--

More than 1 pg_database Entry for Database - Thread #2