Обсуждение: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory
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.
regards
ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory
От
Craig Ringer
Дата:
On 14 September 2017 at 13:35, milist ujang <ujang.milist@gmail.com> wrote:
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
Do you have any idle/old replication slots, perhaps from failed node joins or abandoned nodes not properly parted?
SELECT *
FROM pg_replication_slots;
Also check
SELECT oid,*
FROM pg_database;
Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory
От
milist ujang
Дата:
Hi Craig,
--
Thanks again for pointing to inactive replication slot.
After inactive replication slot been dropped, the relfrozenxid now moving.
I wonder if replication identifier will have some issue if left un-chained? since at other side there are inactive replication identifier.
On Fri, Sep 15, 2017 at 9:36 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
Do you have any idle/old replication slots, perhaps from failed node joins or abandoned nodes not properly parted?SELECT *FROM pg_replication_slots;Also checkSELECT oid,*FROM pg_database;--
regards
ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory
От
Craig Ringer
Дата:
On 15 September 2017 at 11:46, milist ujang <ujang.milist@gmail.com> wrote:
Hi Craig,Thanks again for pointing to inactive replication slot.After inactive replication slot been dropped, the relfrozenxid now moving.I wonder if replication identifier will have some issue if left un-chained? since at other side there are inactive replication identifier.
No, that's harmless.
However, if there's still an "other side" at all, you presumably have broken replication.
Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory
От
milist ujang
Дата:
Hi Craig,
So, is it safe to drop those list from this query output?
select riname from pg_replication_identifier where riname not in
(select external_id from pg_replication_identifier_progress);
I cannot read pg_get_replication_identifier_progress function, is it likely c function?
On Fri, Sep 15, 2017 at 11:14 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
On 15 September 2017 at 11:46, milist ujang <ujang.milist@gmail.com> wrote:Hi Craig,Thanks again for pointing to inactive replication slot.After inactive replication slot been dropped, the relfrozenxid now moving.I wonder if replication identifier will have some issue if left un-chained? since at other side there are inactive replication identifier.No, that's harmless.However, if there's still an "other side" at all, you presumably have broken replication.--
regards
ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
Re: [GENERAL] BDR, near xid wraparound, a lot of files in pg_subtrans directory
От
milist ujang
Дата:
Hi craig,
I have a bunch of orphaned replication identifier.
How to cleanup those orpaned identifier without affecting currently
configured slots?
I mean what is relation the replication identifier with slot_name in
replication slots?
On Mon, Sep 18, 2017 at 3:00 PM, milist ujang
wrote:
> Hi Craig,
>
> So, is it safe to drop those list from this query output?
>
> select riname from pg_replication_identifier where riname not in
> (select external_id from pg_replication_identifier_progress);
>
> I cannot read pg_get_replication_identifier_progress function, is it
> likely c function?
>
>
>
>
> On Fri, Sep 15, 2017 at 11:14 AM, Craig Ringer
> wrote:
>
>> On 15 September 2017 at 11:46, milist ujang
>> wrote:
>>
>>> Hi Craig,
>>>
>>> Thanks again for pointing to inactive replication slot.
>>> After inactive replication slot been dropped, the relfrozenxid now
>>> moving.
>>>
>>> I wonder if replication identifier will have some issue if left
>>> un-chained? since at other side there are inactive replication identifier.
>>>
>>
>> No, that's harmless.
>>
>> However, if there's still an "other side" at all, you presumably have
>> broken replication.
>>
>>
>> --
>> Craig Ringer http://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Training & Services
>>
>
>
>
> --
> regards
>
> ujang jaenudin | DBA Consultant (Freelancer)
> http://ora62.wordpress.com
> http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
>
--
regards
ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab