Обсуждение: transaction ID query
Hi List; can I run queries to see the following for postgres v 8.1.4: a) where the db is per used transaction ID's (want to avoid a transaction ID wrap-around scenario) b) a list of tables in the db and the last time they were vacuumed Thanks in advance /Kevin
Kevin Kempter wrote: > Hi List; > > can I run queries to see the following for postgres v 8.1.4: Update to 8.1.9 *soon* unless you want to be victim of a nasty autovac bug. And in case you already were (which you won't know until your database starts causing you hard-to-fix headaches), I suggest you connect to template0 and run VACUUM FREEZE for precaution. > a) where the db is per used transaction ID's (want to avoid a transaction ID > wrap-around scenario) select age(datfrozenxid) from pg_database; > b) a list of tables in the db and the last time they were vacuumed Examine the pg_stat views, though I am not sure if the vacuum columns were already in 8.1. In any case, in 8.1 Xid wraparound is tracked per-database, so you need database-wide vacuums. In 8.2 it is per table so it is easier to keep up to date. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera a écrit : > Kevin Kempter wrote: > [...] >> b) a list of tables in the db and the last time they were vacuumed > > Examine the pg_stat views, though I am not sure if the vacuum columns > were already in 8.1. In any case, in 8.1 Xid wraparound is tracked > per-database, so you need database-wide vacuums. In 8.2 it is per table > so it is easier to keep up to date. > last vacuum information is available on 8.2, not 8.1. See http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html Regards. -- Guillaume. <!-- http://abs.traduc.org/ http://lfs.traduc.org/ http://docs.postgresqlfr.org/ -->
On Thursday 30 August 2007 07:12:48 Alvaro Herrera wrote: > Kevin Kempter wrote: > > Hi List; > > > > can I run queries to see the following for postgres v 8.1.4: > > Update to 8.1.9 *soon* unless you want to be victim of a nasty autovac > bug. And in case you already were (which you won't know until your > database starts causing you hard-to-fix headaches), I suggest you > connect to template0 and run VACUUM FREEZE for precaution. > > > a) where the db is per used transaction ID's (want to avoid a transaction > > ID wrap-around scenario) > > select age(datfrozenxid) from pg_database; > > > b) a list of tables in the db and the last time they were vacuumed > > Examine the pg_stat views, though I am not sure if the vacuum columns > were already in 8.1. In any case, in 8.1 Xid wraparound is tracked > per-database, so you need database-wide vacuums. In 8.2 it is per table > so it is easier to keep up tokempt date. I've run a vacuum verbose on the entire database (actually on all db's within the cluster) but now I still see large numbers for the following query. Am I missing something? Are these db's still in danger of a tx ID wrap-around failure? ems-1=# select datname, age(datfrozenxid) from pg_database; datname | age -------------+------------ postgres | 1083972950 rbc | 1083972554 emsam-1-old | 1085384465 template1 | 1083966365 template0 | 1195263818 ems-1 | 1086979868 Thanks in advance... /Kevin
Kevin Kempter <kevin@kevinkempterllc.com> writes: > I've run a vacuum verbose on the entire database (actually on all db's > within the cluster) but now I still see large numbers for the > following query. Those numbers are fine --- age() starts out at 1 billion (2^30) IIRC. regards, tom lane