pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtransaction ID query
5+ messages / 4 participants
[nested] [flat]
* transaction ID query
@ 2007-08-30 12:47 Kevin Kempter <kevin@kevinkempterllc.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Kempter @ 2007-08-30 12:47 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: transaction ID query
@ 2007-08-30 13:12 Alvaro Herrera <alvherre@commandprompt.com>
parent: Kevin Kempter <kevin@kevinkempterllc.com>
0 siblings, 2 replies; 5+ messages in thread
From: Alvaro Herrera @ 2007-08-30 13:12 UTC (permalink / raw)
To: Kevin Kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: transaction ID query
@ 2007-08-30 13:22 Guillaume Lelarge <guillaume@lelarge.info>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 5+ messages in thread
From: Guillaume Lelarge @ 2007-08-30 13:22 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Kevin Kempter <kevin@kevinkempterllc.com>; pgsql-admin
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/ -->
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: transaction ID query
@ 2007-09-03 18:54 Kevin Kempter <kevin@kevinkempterllc.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 5+ messages in thread
From: Kevin Kempter @ 2007-09-03 18:54 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: transaction ID query
@ 2007-09-03 19:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Kempter <kevin@kevinkempterllc.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-09-03 19:21 UTC (permalink / raw)
To: Kevin Kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-09-03 19:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-30 12:47 transaction ID query Kevin Kempter <kevin@kevinkempterllc.com>
2007-08-30 13:12 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-08-30 13:22 ` Guillaume Lelarge <guillaume@lelarge.info>
2007-09-03 18:54 ` Kevin Kempter <kevin@kevinkempterllc.com>
2007-09-03 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox