FW: More than 1 pg_database Entry for Database - Thread #2
am 16.04.2010 03:33:11 von Samuel Stearns --_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@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/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
xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" xmlns=3D"http://ww=
w.w3.org/TR/REC-html40">
"/>
=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Hi,
=3D'font-size:
10.0pt;font-family:Arial;color:navy'>
=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Does anyone have any ideas how I can k=
eep
from getting into this duplicate database scenario? Any advice would =
be
greatly appreciated.
=3D'font-size:
10.0pt;font-family:Arial;color:navy'>
=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Thanks,
=3D'font-size:
10.0pt;font-family:Arial;color:navy'>
=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Sam
=3D'font-size:
10.0pt;font-family:Arial;color:navy'>
face=3D"Times New Roman">
style=3D'font-size:10.0pt;font-family:Tahoma;font-weight:bol d'>From:=
pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] =
Sent: Wednesday, 14 April 20=
10
10:11 AM
To:
Subject: [ADMIN] More than 1
pg_database Entry for Database - Thread #2=
'font-size:
12.0pt'>
0.0pt;
font-family:Arial'>Howdy,
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>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:
0.0pt;
font-family:Arial'>
ourier New">
d more
than one (2) pg_database entry for database "sqlsnbs"
/span>
ourier New">
iled
on database "sqlsnbs", exiting
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>I followed your thread here:
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>
l">http://www.mail-archive.com/pgsql-admin@postgresql.org/ms g25978.html=
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>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.
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>I did not capture the queries I was running at the time =
of
diagnosing but I can summarize. I ran the following query:
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>select xmin, cmin, xmax, cmax, oid, ctid, datname from
pg_database;
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>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
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.
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>I was wondering if you could please answer the following=
for
me to help mitigate this in the future:
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>1. Should I be running a scheduled vacuum analyze =
in
addition to the auto-vacuum?
0.0pt;
font-family:Arial'>2. Should I be running a scheduled vacuum full?
0.0pt;
font-family:Arial'>3. Does 8.4 address this problem?
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>Thank you,
0.0pt;
font-family:Arial'>
0.0pt;
font-family:Arial'>Sam
--_000_68B59BEDCD36854AADBDF17E91B2937AB89DE7F7EXCHMAILstaff in_--