Skip site navigation (1) Skip section navigation (2)

Re: Recovering a database in danger of transaction wrap-around

From: Steven Rosenstein <srosenst(at)us(dot)ibm(dot)com>
To: Tino Schwarze <postgresql(at)tisc(dot)de>
Cc: pgsql-admin(at)postgresql(dot)org, pgsql-admin-owner(at)postgresql(dot)org
Subject: Re: Recovering a database in danger of transaction wrap-around
Date: 2008-01-25 20:50:47
Message-ID: OFF6983028.1B1FD4EE-ON852573DB.0072338D-852573DB.00728399@us.ibm.com (view raw or flat)
Thread:
Lists: pgsql-admin
I am unable to run pg_dump because even though I can successfully start the
postmaster, every time I try to do something I receive the warning that the
postmaster will not allow any activity because we crossed the minimum
transaction threshold.  pg_dump dies almost immediately:

Starting dump to
/vsa/backups/db/20080125170401.vsa_pgsql_db_bak.gz...pg_dump: [archiver
(db)] connection to database "vsa" failed: FATAL:  database is not
accepting commands to avoid wraparound data loss in database "vsa"
HINT:  Stop the postmaster and use a standalone backend to vacuum database
"vsa".
The backup completed with the condition coded = 0

Any other suggestions?
___________________________________________________________________________________

Steven Rosenstein
IT Architect/Developer | IBM Virtual Server Administration
Voice/FAX: 845-689-2064 | Cell: 646-345-6978 | Tieline: 930-6001
Text Messaging: 6463456978 @ mobile.mycingular.com
Email: srosenst @ us.ibm.com

"Learn from the mistakes of others because you can't live long enough to
make them all yourself." -- Eleanor Roosevelt


                                                                                                                                                     
  From:       Tino Schwarze <postgresql(at)tisc(dot)de>                                                                                                     
                                                                                                                                                     
  To:         pgsql-admin(at)postgresql(dot)org                                                                                                             
                                                                                                                                                     
  Date:       01/25/2008 02:27 PM                                                                                                                    
                                                                                                                                                     
  Subject:    Re: [ADMIN] Recovering a database in danger of transaction wrap-around                                                                 
                                                                                                                                                     




On Fri, Jan 25, 2008 at 02:10:06PM -0500, Tom Lane wrote:

> > I used lsof to monitor which files the backend was actually working on.
It
> > took two of the four days for it to vacuum a single table with 43
> > one-gigabyte extents.  I have one table with over 300 extents.  I'm
looking
> > at a vacuum process which can ultimately take weeks (if not months) to
> > complete.
>
> Yipes.  You are just using plain VACUUM, right, not VACUUM FULL?
> Have you checked that vacuum_cost_delay isn't enabled?

pg_dump/pg_restore may be a lot faster here - we're in an emergency
situation anyway and after that, the whole DB will be clean again, all
indices rebuilt nicely, no bloat in the tables.

Tino.

--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de

Tino Schwarze * Lortzingstra├če 21 * 09119 Chemnitz

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org



In response to

pgsql-admin by date

Next:From: Tom LaneDate: 2008-01-25 21:14:06
Subject: Re: Recovering a database in danger of transaction wrap-around
Previous:From: Steven RosensteinDate: 2008-01-25 20:46:01
Subject: Re: Recovering a database in danger of transaction wrap-around

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group