Thread: postgres 8.2.9 transaction id wraparound failure
running on a linux box
i've inherited this test machine getting the following errors in the
logs....
scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181]
[scoogan_ange_1.2] FATAL: database is not accepting commands to avoid
wraparound data loss in database "postgres"
scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181]
[scoogan_ange_1.2] HINT: Stop the postmaster and use a standalone
backend to vacuum database "postgres".
I stopped the server and went into single user mode as the docs
suggested....
postgres --single -D /data/pg_devices
then i am issuing vacuum full analyze; it returns.....
2009-04-20 11:37:47.377 EDT [8513] [] WARNING: database "postgres"
must be vacuumed within 982038 transactions
2009-04-20 11:37:47.377 EDT [8513] [] HINT: To avoid a database
shutdown, execute a full-database VACUUM in "postgres".
trying to restart the server - I still am getting the same initial
errors....
Am i missing something??
thanks!! Maria Wilson
On Mon, 2009-04-20 at 13:49 -0400, Maria L. Wilson wrote: > running on a linux box > > i've inherited this test machine getting the following errors in the > logs.... > > scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181] > [scoogan_ange_1.2] FATAL: database is not accepting commands to avoid > wraparound data loss in database "postgres" > scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181] > [scoogan_ange_1.2] HINT: Stop the postmaster and use a standalone > backend to vacuum database "postgres". > > I stopped the server and went into single user mode as the docs > suggested.... > postgres --single -D /data/pg_devices > > > then i am issuing vacuum full analyze; it returns..... > > 2009-04-20 11:37:47.377 EDT [8513] [] WARNING: database "postgres" > must be vacuumed within 982038 transactions > 2009-04-20 11:37:47.377 EDT [8513] [] HINT: To avoid a database > shutdown, execute a full-database VACUUM in "postgres". > > trying to restart the server - I still am getting the same initial > errors.... You have to vacuum each database. Joshua D. Drake > > Am i missing something?? > > thanks!! Maria Wilson > > > -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
I was afraid you'd say that - any quick way to vacuum all 315 databases on this machine? thanks...... Joshua D. Drake wrote: > On Mon, 2009-04-20 at 13:49 -0400, Maria L. Wilson wrote: > >> running on a linux box >> >> i've inherited this test machine getting the following errors in the >> logs.... >> >> scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181] >> [scoogan_ange_1.2] FATAL: database is not accepting commands to avoid >> wraparound data loss in database "postgres" >> scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181] >> [scoogan_ange_1.2] HINT: Stop the postmaster and use a standalone >> backend to vacuum database "postgres". >> >> I stopped the server and went into single user mode as the docs >> suggested.... >> postgres --single -D /data/pg_devices >> >> >> then i am issuing vacuum full analyze; it returns..... >> >> 2009-04-20 11:37:47.377 EDT [8513] [] WARNING: database "postgres" >> must be vacuumed within 982038 transactions >> 2009-04-20 11:37:47.377 EDT [8513] [] HINT: To avoid a database >> shutdown, execute a full-database VACUUM in "postgres". >> >> trying to restart the server - I still am getting the same initial >> errors.... >> > > You have to vacuum each database. > > Joshua D. Drake > > > >> Am i missing something?? >> >> thanks!! Maria Wilson >> >> >> >>
Maria L. Wilson wrote: > I was afraid you'd say that - any quick way to vacuum all 315 databases > on this machine? Actually you only need to vacuum the database being complained about -- "postgres" in this case. You can issue SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC to find whether any other database is going to need vacuuming as well. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
I tried just vacuuming the "postgres" database first. No luck. Was
still unable to startup the server normally.
Here's is a snipped from the query you suggested.... All the databases
on this machine look similar.....
backend> SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY
age(datfrozenxid) DESC
2009-04-20 14:27:52.250 EDT [10097] [] WARNING: database "postgres"
must be vacuumed within 981218 transactions
2009-04-20 14:27:52.250 EDT [10097] [] HINT: To avoid a database
shutdown, execute a full-database VACUUM in "postgres".
1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
2: age (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "postgres" (typeid = 19, len = 64, typmod = -1,
byval = f)
2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "ange" (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t)
----
Alvaro Herrera wrote:
> Maria L. Wilson wrote:
>
>> I was afraid you'd say that - any quick way to vacuum all 315 databases
>> on this machine?
>>
>
> Actually you only need to vacuum the database being complained about --
> "postgres" in this case. You can issue
> SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC
> to find whether any other database is going to need vacuuming as well.
>
>
Maria L. Wilson wrote: > I tried just vacuuming the "postgres" database first. No luck. Was > still unable to startup the server normally. Did the vacuum actually work? Note that you need to open the database you're going to vacuum, on the --single command line. > Here's is a snipped from the query you suggested.... All the databases > on this machine look similar..... So you'll need to vacuum them all ... > backend> SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY > age(datfrozenxid) DESC > 2009-04-20 14:27:52.250 EDT [10097] [] WARNING: database "postgres" > must be vacuumed within 981218 transactions > 2009-04-20 14:27:52.250 EDT [10097] [] HINT: To avoid a database > shutdown, execute a full-database VACUUM in "postgres". > 1: datname (typeid = 19, len = 64, typmod = -1, byval = f) > 2: age (typeid = 23, len = 4, typmod = -1, byval = t) > ---- > 1: datname = "postgres" (typeid = 19, len = 64, typmod = -1, > byval = f) > 2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t) > ---- > 1: datname = "ange" (typeid = 19, len = 64, typmod = -1, byval = f) > 2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t) > ---- My guess is that autovacuum is failing to vacuum anything for some reason. Maybe it's dying due to an error, which you'd find in the server log file. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
ouch!! ok - this may take me all week! i'm opening each database when I start up the server ( postgres --single -D /data/pg_devices /database1/ ) i run the vacuum - just a plain old vacuum - nothing else... i am getting output like this with each vacuum... backend> [postgres@n116 ~]$ postgres --single -D /data/pg_devices n015ecs vacuum; 2009-04-20 14:40:02.015 EDT [10171] [] WARNING: database "postgres" must be vacuumed within 981142 transactions 2009-04-20 14:40:02.015 EDT [10171] [] HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres". 2009-04-20 14:40:02.053 EDT [10171] [] WARNING: database "postgres" must be vacuumed within 981142 transactions 2009-04-20 14:40:02.603 EDT [10171] [] HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres". 2009-04-20 14:40:02.603 EDT [10171] [] WARNING: oldest xmin is far in the past 2009-04-20 14:40:02.603 EDT [10171] [] HINT: Close open transactions soon to avoid wraparound problems. 2009-04-20 14:40:02.671 EDT [10171] [] WARNING: database "postgres" must be vacuumed within 981131 transactions it finishes - i then control -D to stop the server and start up with the next database. Alvaro Herrera wrote: > Maria L. Wilson wrote: > >> I tried just vacuuming the "postgres" database first. No luck. Was >> still unable to startup the server normally. >> > > Did the vacuum actually work? Note that you need to open the database > you're going to vacuum, on the --single command line. > > >> Here's is a snipped from the query you suggested.... All the databases >> on this machine look similar..... >> > > So you'll need to vacuum them all ... > > >> backend> SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY >> age(datfrozenxid) DESC >> 2009-04-20 14:27:52.250 EDT [10097] [] WARNING: database "postgres" >> must be vacuumed within 981218 transactions >> 2009-04-20 14:27:52.250 EDT [10097] [] HINT: To avoid a database >> shutdown, execute a full-database VACUUM in "postgres". >> 1: datname (typeid = 19, len = 64, typmod = -1, byval = f) >> 2: age (typeid = 23, len = 4, typmod = -1, byval = t) >> ---- >> 1: datname = "postgres" (typeid = 19, len = 64, typmod = -1, >> byval = f) >> 2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t) >> ---- >> 1: datname = "ange" (typeid = 19, len = 64, typmod = -1, byval = f) >> 2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t) >> ---- >> > > My guess is that autovacuum is failing to vacuum anything for some > reason. Maybe it's dying due to an error, which you'd find in the > server log file. > >
On Mon, Apr 20, 2009 at 1:43 PM, Maria L. Wilson <Maria.L.Wilson-1@nasa.gov> wrote: > ouch!! ok - this may take me all week! > i'm opening each database when I start up the server ( postgres --single -D > /data/pg_devices /database1/ ) is database1 the directory where you initialized the server? > i run the vacuum - just a plain old vacuum - nothing else... > i am getting output like this with each vacuum... > > backend> [postgres@n116 ~]$ postgres --single -D /data/pg_devices n015ecs can't understand this one... you have initialized several directories? so you have variuos instances of postgresql running? if so, you only have to vacuum the one that is giving you troubles... > vacuum; you execute this inside psql... rigth? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
On Mon, Apr 20, 2009 at 12:43 PM, Maria L. Wilson <Maria.L.Wilson-1@nasa.gov> wrote: > ouch!! ok - this may take me all week! > i'm opening each database when I start up the server ( postgres --single -D > /data/pg_devices /database1/ ) This is not specifying the db to connect to. You need: postgres --single -D /data/pg_devices /database1/ postgres
sorry for the confusion - from what the docs - i am starting out the single user like this..... postgres --single -D /data/pg_devices postgres *that connects me to the "postgres" database postgres --single -D /data/pg_devices n001acs *that connects me to the "n001acs" database....... then I am issuing a vacuum command with each session..... Sound okay? Scott Marlowe wrote: > On Mon, Apr 20, 2009 at 12:43 PM, Maria L. Wilson > <Maria.L.Wilson-1@nasa.gov> wrote: > >> ouch!! ok - this may take me all week! >> i'm opening each database when I start up the server ( postgres --single -D >> /data/pg_devices /database1/ ) >> > > This is not specifying the db to connect to. You need: > > postgres --single -D /data/pg_devices /database1/ postgres >
Maria L. Wilson wrote: > ouch!! ok - this may take me all week! > i'm opening each database when I start up the server ( postgres --single > -D /data/pg_devices /database1/ ) > i run the vacuum - just a plain old vacuum - nothing else... > i am getting output like this with each vacuum... Heh. You can actually script this, you know. You need to provide a list of databases, then do something like (maybe bash-specific): for db in database1 database2 database3 ...; do echo "vacuum $db" | postgres --single -D /data/pg_devices $db done To produce the list of databases you could connect to any one of them and do "SELECT datname FROM pg_database", redirect this to a file, and then fix up the resulting file so that there's a single database name per line, then the above loop could look like this: for db in $(cat file-with-database-names); do echo "vacuum $db" | postgres --single -D /data/pg_devices $db done If there are names with funny characteres in them (spaces, uppercase, symbols) then some quoting is called for. You'd be also wise to check the output for errors in case something goes ill. Note that something *will* go wrong at some point. Some database somewhere will throw you an error and it'll tell you that it cannot be vacuumed. That is, after all, the reason that autovacuum has not being doing this automatically for you. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
ok - i am still having the same errors after removing the majority of the databases on this server - leaving only 46 left. I have gone into single user mode and vacuumed the rest. any other ideas would be greatly appreciated!!! log file..... 2009-04-21 16:14:36.688 EDT [10757] [] LOG: database system is ready 2009-04-21 16:14:37.754 EDT [10757] [] WARNING: database "postgres" must be vacuumed within 976347 transactions 2009-04-21 16:14:37.754 EDT [10757] [] HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres". 2009-04-21 16:14:37.811 EDT [10819] [] ERROR: database is not accepting commands to avoid wraparound data loss in database "postgres" 2009-04-21 16:14:37.811 EDT [10819] [] HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres". [unknown] 2009-04-21 16:14:41.767 EDT 0 [10821] [[unknown]] LOG: connection received: host=10.10.100.11 port=53704 jboss 10.10.100.11 2009-04-21 16:14:41.781 EDT 0 [10821] [cluster0809oaid] LOG: connection authorized: user=jboss database=cluster0809oaid jboss 10.10.100.11 2009-04-21 16:14:41.785 EDT 0 [10821] [cluster0809oaid] FATAL: database "cluster0809oaid" does not exist thanks.... Maria Wilson Alvaro Herrera wrote: > Maria L. Wilson wrote: > >> ouch!! ok - this may take me all week! >> i'm opening each database when I start up the server ( postgres --single >> -D /data/pg_devices /database1/ ) >> i run the vacuum - just a plain old vacuum - nothing else... >> i am getting output like this with each vacuum... >> > > Heh. You can actually script this, you know. You need to provide a > list of databases, then do something like (maybe bash-specific): > > for db in database1 database2 database3 ...; do > echo "vacuum $db" | postgres --single -D /data/pg_devices $db > done > > To produce the list of databases you could connect to any one of them > and do "SELECT datname FROM pg_database", redirect this to a file, and > then fix up the resulting file so that there's a single database name > per line, then the above loop could look like this: > > for db in $(cat file-with-database-names); do > echo "vacuum $db" | postgres --single -D /data/pg_devices $db > done > > If there are names with funny characteres in them (spaces, uppercase, > symbols) then some quoting is called for. You'd be also wise to check > the output for errors in case something goes ill. > > Note that something *will* go wrong at some point. Some database > somewhere will throw you an error and it'll tell you that it cannot be > vacuumed. That is, after all, the reason that autovacuum has not being > doing this automatically for you. > >
All, How does one tell how much space is available (probably by table), after a "vacuum verbose analyze" has completed. As I understand it, the emptied space will still be only available to the table that originally housed it. We are running Postgresql 8.3 in various Solaris and Linux environments. Thanks, Naomi
"Naomi Walker" <nwalker@mhs.mphasis.com> wrote: > How does one tell how much space is available (probably by table), > after a "vacuum verbose analyze" has completed. Personally, I mostly pay attention to these lines: NNNNN pages contain useful free space. (where NNNNN is a large number). We grep for large numbers in the file where we've redirected the nightly VACUUM ANALYZE VERBOSE output, and if we find something we email our DBA team. -Kevin