HI list, I have a database with bdr environment which keep alerting these messages in log file:
HINT: Close open transactions soon to avoid wraparound problems. WARNING: oldest xmin is far in the past Querying pg_stat_activity where state='active'; datname | template1 query | autovacuum: VACUUM pg_catalog.pg_depend (to prevent wraparound) datname | template1 query | autovacuum: VACUUM pg_toast.pg_toast_1255 (to prevent wraparound) datname | template1 query | autovacuum: VACUUM pg_catalog.pg_ts_parser (to prevent wraparound) SELECT pg_namespace.nspname ,c.relname AS relname ,greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age ,c.relfrozenxid ,t.relfrozenxid FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid LEFT JOIN pg_namespace ON pg_namespace.oid = c.relnamespace WHERE c.relkind = 'r' and c.relname='pg_depend'; -[ RECORD 1 ]+----------- nspname | pg_catalog relname | pg_depend age | 1165907267 relfrozenxid | 102246720 relfrozenxid | Trying to vacuum manual, but no luck: postgres=# vacuum pg_catalog.pg_depend; WARNING: oldest xmin is far in the past HINT: Close open transactions soon to avoid wraparound problems. VACUUM postgres=# vacuum freeze pg_catalog.pg_depend; WARNING: oldest xmin is far in the past HINT: Close open transactions soon to avoid wraparound problems. VACUUM postgres=# vacuum full pg_catalog.pg_depend; WARNING: oldest xmin is far in the past HINT: Close open transactions soon to avoid wraparound problems. VACUUM Check fozenxid again after vacuum, not moving: SELECT pg_namespace.nspname ,c.relname AS relname ,greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age ,c.relfrozenxid ,t.relfrozenxid FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid LEFT JOIN pg_namespace ON pg_namespace.oid = c.relnamespace WHERE c.relkind = 'r' and c.relname='pg_depend'; -[ RECORD 1 ]+----------- nspname | pg_catalog relname | pg_depend age | 1165908742 relfrozenxid | 102246720 relfrozenxid | Searching the similar situation, found this url: https://postgrespro.com/list/thread-id/1556972 but the above url was in streaming replication, not bdr; but symptom is likely same: there are a lot of files inside pg_subtrans directory, many files older than latest cluster restarted. query pg_prepared_xacts, pg_stat_activity, pg_locks with no indication about long running queries or even open transactions. -- regards ujang jaenudin | DBA Consultant (Freelancer) http://ora62.wordpress.com http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
