Postgres Pro
Downloads
Home   >   mailing lists

Re: oldest xmin is far in the past - Mailing list pgsql-hackers

From John Snow
Subject Re: oldest xmin is far in the past
Date
Msg-id CAM+o-ApuKO=UMRZmkY6NwLrcqCZanXWmaThCgFFzY7jVz0mbRA@mail.gmail.com
Whole thread Raw
In response to Re: oldest xmin is far in the past  (Tomas Vondra <tomas.vondra@2ndquadrant.com>)
Responses Re: oldest xmin is far in the past
List pgsql-hackers
Tree view
There is no any long transaction neither prepared transaction.
#autovacuum_freeze_max_age = 200000000 - default value

I have 9.4.5 version. Also it all started after I've setup Slony replication(mb just a coincidence). All tables in public schema have the same "age", I believe this is weird.

How can I calculate how long DB can live in this stage?

2016-03-19 0:28 GMT+03:00 Tomas Vondra <tomas.vondra@2ndquadrant.com>:
Hi,

On 03/18/2016 09:42 AM, John Snow wrote:
Hi everyone!

Trying to make VACUUM FREEZE on PG instance and keep getting this error:

2016-03-18 05:56:51 UTC   46750 WARNING:  oldest xmin is far in the past
2016-03-18 05:56:51 UTC   46750 HINT:  Close open transactions soon to
avoid wraparound problems.
2016-03-18 05:56:51 UTC   46750 DEBUG:  transaction ID wrap limit is
2654342112, limited by database with OID 1
2016-03-18 05:56:51 UTC   46750 DEBUG:  MultiXactId wrap limit is
2147483648, limited by database with OID 12451

Also "age" and "relfrozenxid" doesnt't change.

 That probably means there's an old transaction somewhere - either a regular one (check pg_stat_activity) or a prepared one (pg_prepared_xacts).

The meaning of "old" depends on autovacuum_freeze_max_age - what value is set in the session running the VACUUM FREEZE?

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

pgsql-hackers by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: WIP: Covering + unique indexes.
Next
From: Andres Freund
Date:
Subject: Re: logger process infinite loop