More than 1 pg_database Entry for Database - Thread #2
am 14.04.2010 02:41:22 von Samuel Stearns --_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@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
xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" xmlns=3D"http://ww=
w.w3.org/TR/REC-html40">
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 billi=
on.
In your thread above it was stated by
Lane
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=
..
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_68B59BEDCD36854AADBDF17E91B2937AB89DE232EXCHMAILstaff in_--