Thread: vacuum won't fix tx wraparound problem
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'tget 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
# psql -U drdb maindb
psql (8.4.4)
Type "help" for 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 also reset the transaction log aftwards which didn't help. Any help would be appreciated. Thanks
--Gene
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'tget 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'tget 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 weare 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 leftoff". It doesn't seem to be working like that when running vacuum in a standalone instance; it takes just as long eachtime, 3-4 hours. thanks for all your help, Gene On Jun 27, 2010, at 10:30 AM, Tom Lane wrote: > 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
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: > 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'tget 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?
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