Hi all,
I had many waste replication identifier (164 of 457), the env is BDR 0.9.3
and 9.4.4.
I'm going to remove those junk replication identifiers, but there is no
exactly how to do in docs.
# select slot_name,plugin,datoid,restart_lsn from pg_replication_slots
where slot_name like '%654018%';
slot_name | plugin | datoid | restart_lsn
------------------------------------------+--------+--------+--------------
bdr_654018_6258509090026857134_1_24612__ | bdr | 654018 | 1443/FDC0A58
(1 row)
# select * from pg_replication_identifier where riname like '%654018%';
riident | riname
---------+-----------------------------------------
217 | bdr_6258509090026857134_1_24612_654018_
(1 row)
# select * from pg_replication_identifier_progress where external_id like
'%654018%';
local_id | external_id | remote_lsn |
local_lsn
----------+-----------------------------------------+-------------+---------------
217 | bdr_6258509090026857134_1_24612_654018_ | 35/38181F98 |
1448/CC3D90C8
(1 row)
>From those 3 queries, summarize the slot, and identifier:
slot_name : bdr_654018_6258509090026857134_1_24612__
riname : bdr_6258509090026857134_1_24612_654018_
external_id: bdr_6258509090026857134_1_24612_654018_
Validate number of the junk identifier, is it tally?:
# select count(*) from pg_replication_slots;
count
-------
293
(1 row)
# select count(*) from pg_replication_identifier;
count
-------
457
(1 row)
Identify the junk identifiers with the following query:
# select count(*) from pg_replication_identifier where riname not in (
# select
split_part(slot_name,'_',1)||'_'||split_part(slot_name,'_',3)||'_'||split_part(slot_name,'_',4)||'_'||split_part(slot_name,'_',5)||'_'||split_part(slot_name,'_',2)||'_'
as aaa from pg_replication_slots
# );
count
-------
164
(1 row)
457-293 = 164, seems exactly match...
Luckily this query return 6 rows, which I'm not understand :(
# select * from pg_replication_identifier_progress where external_id not in
(
select
split_part(slot_name,'_',1)||'_'||split_part(slot_name,'_',3)||'_'||split_part(slot_name,'_',4)||'_'||split_part(slot_name,'_',5)||'_'||split_part(slot_name,'_',2)||'_'
as aaa from pg_replication_slots
);
local_id | external_id | remote_lsn |
local_lsn
----------+------------------------------------------+------------+---------------
121 | bdr_6258509090026857134_1_27953_394071_ | 2/F795DDA0 | 0/0
331 | bdr_6258509090026857134_1_16385_133577_ | 4/91562CB8 | 0/0
274 | bdr_6258509090026857134_1_16385_797268_ | 2/2DC5D518 |
1280/83F70D10
163 | bdr_6258509090026857134_1_16385_1104572_ | 0/2059400 | 0/0
430 | bdr_6258509090026857134_1_26348_1108062_ | 4/DD9E0488 | 0/0
431 | bdr_6258509090026857134_1_26359_1107286_ | 4/E474D8F8 | 0/0
(6 rows)
my question is:
Is it OK to remove those 164 junk identifiers? or are they any other
approach?
--
regards
ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
