transaction ID query

Поиск
Список
Период
Сортировка
Искать

transaction ID query

От:
Kevin Kempter <kevin@kevinkempterllc.com>
Дата:
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

Re: transaction ID query

От:
Alvaro Herrera <alvherre@commandprompt.com>
Дата:
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

Re: transaction ID query

От:
Guillaume Lelarge <guillaume@lelarge.info>
Дата:
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.

Re: transaction ID query

От:
Kevin Kempter <kevin@kevinkempterllc.com>
Дата:
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

Re: transaction ID query

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Kevin Kempter  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
FAQ