Thread: FW: More than 1 pg_database Entry for Database - Thread #2
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 failing a couple weeks back with the following error:
pg_dump: query returned more than one (2) pg_database entry for database "sqlsnbs"
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 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 with 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
Samuel Stearns <SStearns@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
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@wicourts.gov] Sent: Saturday, 17 April 2010 1:36 AM To: Samuel Stearns Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] FW: More than 1 pg_database Entry for Database - Thread #2 Samuel Stearns <SStearns@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