vacuum won't fix tx wraparound problem

On Sun, Jun 27, 2010 at 9:27 AM, Gene Hart <genekhart@gmail.com> wrote:

I'm getting the following errors and I'm not quite sure what to do at this point. The database is very large and I can't get it to accept commands. Please help!

maindb =# create table test1();
ERROR:  database is not accepting commands to avoid wraparound data loss in database "maindb"
HINT:  Stop the postmaster and use a standalone backend to vacuum database "maindb".
You might also need to commit or roll back old prepared transactions.

I assume that here you did /etc/init.d/postgresql stop or something like that.

[root@P00C01S01-DBM04 data]# su postgres
bash-3.2$ postgres --single -D /data1/pg2/home/data -O maindb
 -  - 2010-06-27 13:07:05 UTC :WARNING:  database "maindb" must be vacuumed within 1000000 transactions
 -  - 2010-06-27 13:07:05 UTC :HINT:  To avoid a database shutdown, execute a database-wide VACUUM in "maindb".
       You might also need to commit or roll back old prepared transactions.

So what does

select * from pg_prepared_xacts;

say?

In response to Gene Hart <genekhart@gmail.com>:

I'm getting the following errors and I'm not quite sure what to do at this point. The database is very large and I can't get it to accept commands. Please help!

maindb =# create table test1();
ERROR: database is not accepting commands to avoid wraparound data loss in database "maindb"
HINT: Stop the postmaster and use a standalone backend to vacuum database "maindb".
You might also need to commit or roll back old prepared transactions.

[root@P00C01S01-DBM04 data]# su postgres
bash-3.2$ postgres --single -D /data1/pg2/home/data -O maindb
- - 2010-06-27 13:07:05 UTC :WARNING: database "maindb" must be vacuumed within 1000000 transactions
- - 2010-06-27 13:07:05 UTC :HINT: To avoid a database shutdown, execute a database-wide VACUUM in "maindb".
You might also need to commit or roll back old prepared transactions.

PostgreSQL stand-alone backend 8.4.4
backend> vacuum
backend> ^D^D
exit

Am I reading this wrong or did you not bother to allow the vacuum to finish?
Considering there's no command terminator (;) on the vacuum command, it's
unlikely that it ever actually started to do anything.

If you've neglected vacuuming long enough for tx wraparound to be an issue,
it's likely that vacuum is going to take a long time.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Bill Moran <wmoran@potentialtech.com> writes:

In response to Gene Hart <genekhart@gmail.com>:

PostgreSQL stand-alone backend 8.4.4
backend> vacuum
backend> ^D^D
exit

Am I reading this wrong or did you not bother to allow the vacuum to finish?
Considering there's no command terminator (;) on the vacuum command, it's
unlikely that it ever actually started to do anything.

No, Gene did it right --- standalone backends have a different command-line
syntax. (I assume also that he observed a suitably long delay before
the second backend> prompt came up...)

I think Scott's idea of ancient prepared transactions is probably the
most likely bet. Roll those back and then vacuum and you'll be OK.

regards, tom lane

Yeah I did wait long enough for the vacuum to finish. I did consider the prepared_transactions issue but I don't think we are using those. I'll look down that path though since I could be wrong about that.

On a related note I thought in 8.4 a successive vacuum would not take as long as the prior since it "knows where it left off". It doesn't seem to be working like that when running vacuum in a standalone instance; it takes just as long each time, 3-4 hours.

thanks for all your help,
Gene

On Jun 27, 2010, at 10:30 AM, Tom Lane wrote:

select * from pg_prepared_xacts;

returns 0 rows. Is there anything else I could check to see why the backend wouldn't accept commands?

thanks,
Gene

On Jun 27, 2010, at 9:37 AM, Scott Marlowe wrote:

Gene Hart <genekhart@gmail.com> writes:

select * from pg_prepared_xacts;
returns 0 rows.

Hm. You might also confirm that the directory $PGDATA/pg_twophase/ is
empty, but it really should be if there's nothing in that view.

I think you'll have to do some more sleuthing. Check the
pg_database.datfrozenxid value for the "maindb" database, then look in
pg_class for the table(s) with that same value for pg_class.relfrozenxid.
Vacuum these table(s) individually. Do their relfrozenxid values
change?

regards, tom lane