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:
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.
--