pglogical 2.3.2 on 9.4 --> 12.3 CONFLICT: remote DELETE (tuple not found). Resolution: skip.

[milist ujang]

Configured pglogical source is 9.4 target is 12.3

a table has PK from multiple column (varchar(50),char(2),char(1)).

initial syncronization is OK, but when at source there is delete , but got
error at target...

LOG:  CONFLICT: remote DELETE on relation public.a_ replica identity
index a_XXX_pk (tuple not found). Resolution: skip.

replica identity was set to default for the table so yes there is old image
on those 3 columns:

but why delete was error at target?
collation exactly same on all GUC.

any direction to print debug verbosity on why delete on target didnt find
row and skipped?



-- 
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Re: pglogical 2.3.2 on 9.4 --> 12.3 CONFLICT: remote DELETE (tuple not found). Resolution: skip.

[milist ujang]

replication identity correctly displayed on detail after resolution message:

  LOG:  CONFLICT: remote DELETE on relation public.a_ replica identity
index a_XXX_pk (tuple not found). Resolution: skip.

DETAIL:  remote tuple {created_by[int8]:(null)
created_date[timestamp]:(null) updated_by[int8]:(null)
updated_date[timestamp]:(null)
pk_col_1[varchar]:ZZZ18 pk_col_2[bpchar]:12 pk_col_3[bpchar]:G   <<
this is oldval
status_data[varchar]:(null)} in xact origin=2,timestamp=2020-08-12
08:06:33.956683+08,commit_lsn=0/CA6499D0

I am curious about  bpchar in log DETAILS.
Is it the correct error message for the data type char?



On Wed, Aug 12, 2020 at 10:50 AM milist ujang 
wrote:

> Configured pglogical source is 9.4 target is 12.3
>
> a table has PK from multiple column (varchar(50),char(2),char(1)).
>
> initial syncronization is OK, but when at source there is delete , but got
> error at target...
>
> LOG:  CONFLICT: remote DELETE on relation public.a_ replica identity
> index a_XXX_pk (tuple not found). Resolution: skip.
>
> replica identity was set to default for the table so yes there is old
> image on those 3 columns:
>
> but why delete was error at target?
> collation exactly same on all GUC.
>
> any direction to print debug verbosity on why delete on target didnt find
> row and skipped?
>
>
>
> --
> 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

ERROR: catalog is missing 3 attribute(s) for relid 150243

[milist ujang]

Hi lists,

I have an index anomaly on a table; getting the error as subject.

dumping queries behind \dS+ of a table, got 4 queries (see detail below)
1st query --> OK
2nd query --> OK
3rd query ERROR same as subject (ERROR:  catalog is missing 3 attribute(s)
for relid 150243)

comment on 3rd query line --pg_catalog.pg_get_indexdef looks good.
so my assumption we have an issue when call pg_catalog.pg_get_indexdef
function.

select pg_catalog.pg_get_indexdef ( 150243, 0, TRUE );
return blank.

any ideas?


1st query:

SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS
relhasoids, c.relispartition, pg_catalog.array_to_string(c.reloptions ||
array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')
, c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE
c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence,
c.relreplident, am.amname
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
WHERE c.oid = '150243';

2nd query:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
a.atthasdef),
  a.attnotnull,
  (SELECT c.collname FROM pg_catalog.pg_collation c,
pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
a.attcollation <> t.typcollation) AS attcollation,
  a.attidentity,
  a.attgenerated,
  a.attstorage,
  CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END
AS attstattarget,
  pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '150243' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

3rd query:

SELECT
c2.relname,
i.indisprimary,
i.indisunique,
i.indisclustered,
i.indisvalid,
pg_catalog.pg_get_indexdef ( i.indexrelid, 0, TRUE ),
pg_catalog.pg_get_constraintdef ( con.oid, TRUE ),
contype,
condeferrable,
condeferred,
i.indisreplident,
c2.reltablespace
FROM
pg_catalog.pg_class C,
pg_catalog.pg_class c2,
pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON ( conrelid = i.indrelid AND
conindid = i.indexrelid AND contype IN ( 'p', 'u', 'x' ) )
WHERE
C.oid = '150243'
AND C.oid = i.indrelid
AND i.indexrelid = c2.oid
ORDER BY
i.indisprimary DESC,
i.indisunique DESC,
c2.relname;

4th query:

SELECT
c2.relname,
i.indisprimary,
i.indisunique,
i.indisclustered,
i.indisvalid,
pg_catalog.pg_get_indexdef ( i.indexrelid, 0, TRUE ),
pg_catalog.pg_get_constraintdef ( con.oid, TRUE ),
contype,
condeferrable,
condeferred,
i.indisreplident,
c2.reltablespace
FROM
pg_catalog.pg_class C,
pg_catalog.pg_class c2,
pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON ( conrelid = i.indrelid AND
conindid = i.indexrelid AND contype IN ( 'p', 'u', 'x' ) )
WHERE
C.oid = '150243'
AND C.oid = i.indrelid
AND i.indexrelid = c2.oid
ORDER BY
i.indisprimary DESC,
i.indisunique DESC,
c2.relname;


-- 
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Re: ERROR: catalog is missing 3 attribute(s) for relid 150243

[milist ujang]

my version is 12.9 on x86_64.

reading source code, got the message form RelationBuildTupleDesc() function:

/*
  * end the scan and close the attribute relation
  */
 systable_endscan(pg_attribute_scan);
 table_close(pg_attribute_desc, AccessShareLock);

 if (need != 0)
 elog(ERROR, "pg_attribute catalog is missing %d attribute(s) for
relation OID %u",
  need, RelationGetRelid(relation));

I'm not sure whether this function will compare the mentioned relation
attribute in pg_attribute and physical table (header) file?



On Thu, Aug 18, 2022 at 5:33 PM milist ujang  wrote:

> Hi lists,
>
> I have an index anomaly on a table; getting the error as subject.
>
> dumping queries behind \dS+ of a table, got 4 queries (see detail below)
> 1st query --> OK
> 2nd query --> OK
> 3rd query ERROR same as subject (ERROR:  catalog is missing 3 attribute(s)
> for relid 150243)
>
> comment on 3rd query line --pg_catalog.pg_get_indexdef looks good.
> so my assumption we have an issue when call pg_catalog.pg_get_indexdef
> function.
>
> select pg_catalog.pg_get_indexdef ( 150243, 0, TRUE );
> return blank.
>
> any ideas?
>
>
> 1st query:
> 
> SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
> c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS
> relhasoids, c.relispartition, pg_catalog.array_to_string(c.reloptions ||
> array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')
> , c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE
> c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence,
> c.relreplident, am.amname
> FROM pg_catalog.pg_class c
>  LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
> LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
> WHERE c.oid = '150243';
>
> 2nd query:
> 
> SELECT a.attname,
>   pg_catalog.format_type(a.atttypid, a.atttypmod),
>   (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
>FROM pg_catalog.pg_attrdef d
>WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
> a.atthasdef),
>   a.attnotnull,
>   (SELECT c.collname FROM pg_catalog.pg_collation c,
> pg_catalog.pg_type t
>WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
> a.attcollation <> t.typcollation) AS attcollation,
>   a.attidentity,
>   a.attgenerated,
>   a.attstorage,
>   CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END
> AS attstattarget,
>   pg_catalog.col_description(a.attrelid, a.attnum)
> FROM pg_catalog.pg_attribute a
> WHERE a.attrelid = '150243' AND a.attnum > 0 AND NOT a.attisdropped
> ORDER BY a.attnum;
>
> 3rd query:
> 
> SELECT
> c2.relname,
> i.indisprimary,
> i.indisunique,
> i.indisclustered,
> i.indisvalid,
> pg_catalog.pg_get_indexdef ( i.indexrelid, 0, TRUE ),
> pg_catalog.pg_get_constraintdef ( con.oid, TRUE ),
> contype,
> condeferrable,
> condeferred,
> i.indisreplident,
> c2.reltablespace
> FROM
> pg_catalog.pg_class C,
> pg_catalog.pg_class c2,
> pg_catalog.pg_index i
> LEFT JOIN pg_catalog.pg_constraint con ON ( conrelid = i.indrelid AND
> conindid = i.indexrelid AND contype IN ( 'p', 'u', 'x' ) )
> WHERE
> C.oid = '150243'
> AND C.oid = i.indrelid
> AND i.indexrelid = c2.oid
> ORDER BY
> i.indisprimary DESC,
> i.indisunique DESC,
> c2.relname;
>
> 4th query:
> 
> SELECT
> c2.relname,
> i.indisprimary,
> i.indisunique,
> i.indisclustered,
> i.indisvalid,
> pg_catalog.pg_get_indexdef ( i.indexrelid, 0, TRUE ),
> pg_catalog.pg_get_constraintdef ( con.oid, TRUE ),
> contype,
> condeferrable,
> condeferred,
> i.indisreplident,
> c2.reltablespace
> FROM
> pg_catalog.pg_class C,
> pg_catalog.pg_class c2,
> pg_catalog.pg_index i
> LEFT JOIN pg_catalog.pg_constraint con ON ( conrelid = i.indrelid AND
> conindid = i.indexrelid AND contype IN ( 'p', 'u', 'x' ) )
> WHERE
> C.oid = '150243'
> AND C.oid = i.indrelid
> AND i.indexrelid = c2.oid
> ORDER BY
> i.indisprimary DESC,
> i.indisunique DESC,
> c2.relname;
>
>
> --
> 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

Re: ERROR: catalog is missing 3 attribute(s) for relid 150243

[milist ujang]

On Fri, Aug 19, 2022 at 11:02 AM Julien Rouhaud  wrote:

> Hi,
>
> Please don't top-post on this list (and please trim quoted messages too).
>
>
>
> No, this function is comparing pg_class.relnatts to rows fetched querying
> pg_attribute for the given relation.
>
> any concern if I update pg_class.natts and or pg_attribute to let it match?



> What it means is that you likely have data corruption.  You could try to
> reindex pg_attribute and see if that fixes that specific problems, but
> even if
> it does you will have to investigate how data got corrupted, fix that root
> problem, and then try to check for other corrupted data or restore from a
> sane
> backup.
>

yes we have a problem with vm; suddenly restart without reason

I've done recovery by creating empty files under pg_xact , cluster can
bring up
then a file under pg_multixact due to error on a table.

I've done reinding pg_class, pg_attribute, pg_constraint without luck.


-- 
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

[image: width=]

Virus-free.www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

does postgres has snapshot standby feature?

[milist ujang]

hi all,

read about removing standby.signal file behavior in pg14 from
https://dbaclass.com/article/how-to-open-postgres-standby-database-for-read-writesnapshot-standby/

I was shocked about the hidden feature of snapshot standby, does it really
exist?
I could not find anything about snapshot standby in docs so far.

I have removed the standby.signal file in pg12 but got strange behavior
until I have to recreate the standby.

-- 
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Postgres to edb AS, need conversion?

[milist ujang]

Hi all,

Anyone have experiences or someone from edb can guid about change engine
from postgres open source to edb as, di I need to convert the data or
simply plug-and-play? Or need run a tool to add catalog table owned by edb
as?


-- 
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Re: Postgres to edb AS, need conversion?

[milist ujang]

So, do I have to perform conversion/migration?


On Thursday, November 3, 2022, Abdul Sayeed  wrote:

> After migration you need to execute below command from SQL Prompt:
>
>
>
> vacuum (verbose, analyze);  --> to update statistics.
>
> Rest is good.
>
>
> Regards,
> Abdul Sayeed
>

>
> --
> Thanks & Regards,
> Abdul Sayeed
> PostgreSQL DBA
> Postgres Professional Certified
> Skype: abdul.sayeed24
>


-- 
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

db was corrupted, ERROR: cannot freeze committed xmax; fix by deleting rows in catalog tables

[milist ujang]

Hi all,

I have a case on pg12.9 - rhel8 which crashed a long time ago and now in
log keep saying:

automatic vacuum of table "dbnamexx.pg_catalog.pg_statistic"automatic
vacuum of table "dbnamexx.pg_catalog.pg_class"automatic vacuum of table
"dbnamexx.pg_toast.pg_toast_2619"

automatic vacuum of table "dbnamexx.pg_catalog.pg_class"automatic vacuum of
table "dbnamexx.ibent.loginsession_old"automatic vacuum of table
"dbnamexx.pg_toast.pg_toast_2619"

The problem is on the user tables:

vacuum schema.tbl1_old;
ERROR:  cannot freeze committed xmax ...

vacuum schema.tbl2_old;
ERROR:  cannot freeze committed xmax ...

vacuum schema.tbl3_bak;
ERROR:  cannot freeze committed xmax ...

vacuum schema.tbl4_old;
ERROR:  cannot freeze committed xmax ...

unluckily cannot drop those tables. will return "ERROR:  cannot freeze
committed xmax " too

figured out by pg_catcheck utility there are orphaned catalog entries, so
try to fix on restore database by these DML:

delete from pg_attribute where attrelid=;
delete from pg_index where indrelid=;
delete from pg_statistic where starelid=;
delete from pg_constraint where conrelid=...;
delete from pg_depend where refobjid=...;
delete from pg_depend where objid=;
delete from pg_shdepend where objid=...;

delete from pg_type where typrelid=...;
delete from pg_type where  typelem=...;

delete from pg_statistic where starelid= and staattnum=27 and
stainherit='f';

recheck by pg_catcheck, now clean.
then vacuum now without error.

My question:
1. is it OK DML on catalog tables?
2. Are there any impacts in the future?


-- 
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

signal 11: Segmentation fault ; query constraint list; pg16.3

[milist ujang]

postgres 16.1; rocky 9.3

when connect to database postgres this query is OK, but run on user
database, got segmentation fault.


2024-05-20 17:16:54.942 WIB,,,134291,,6648420a.20c93,165,,2024-05-18
12:52:10 WIB,,0,LOG,0,"server process (PID 487952) was terminated by
signal 11: Segmentation fault","Failed process was running: select
ct.conname as constraint_name,
a.attname as column_name,
fc.relname as foreign_table_name,
fns.nspname as foreign_table_schema,
fa.attname as foreign_column_name
from
(SELECT ct.conname, ct.conrelid, ct.confrelid, ct.conkey, ct.contype,
ct.confkey, generate_subscripts(ct.conkey, 1) AS s
   FROM pg_constraint ct
) AS ct
inner join pg_class c on c.oid=ct.conrelid
inner join pg_namespace ns on c.relnamespace=ns.oid
inner join pg_attribute a on a.attrelid=ct.conrelid and a.attnum =
ct.conkey[ct.s]
left join pg_class fc on fc.oid=ct.confrelid
left join pg_namespace fns on fc.relnamespace=fns.oid
left join pg_attribute fa on fa.attrelid=ct.confrelid and fa.attnum =
ct.confkey[ct.s]
where
ct.contype='f'
and c.relname='table1'
and ns.nspname='schemauser'
order by
fns.nspname, fc.relname, a.attnum
;""","postmaster",,0

2024-05-20 17:16:54.942 WIB,,,134291,,6648420a.20c93,166,,2024-05-18
12:52:10 WIB,,0,LOG,0,"terminating any other active server
processes","","postmaster",,0

-- 
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Re: signal 11: Segmentation fault ; query constraint list; pg16.3

[milist ujang]

as of subject, it should be 16.3.

scenario was:
- a few months ago install + deployed user data pg 16.2.
- 2 days ago, updated to 16.3

now I can reproduce the issue, the segment fault comes from
postgres[495645]: segfault at 0 ip 7f318b17e1f4 sp 7ffc7f1b15d8
error 4 in citus.so[7f318b0a4000+ee000] likely on CPU 93 (core 1, socket 1)

after dropping the citus extension, now it's OK.

Thanks for the reply.


On Mon, May 20, 2024 at 6:01 PM David Rowley  wrote:

> On Mon, 20 May 2024 at 22:32, milist ujang  wrote:
> >
> > postgres 16.1; rocky 9.3
> >
> > when connect to database postgres this query is OK, but run on user
> database, got segmentation fault.
>
> I tried your query on 16.1 and I'm unable to reproduce the crash.
>
> Are you able to recreate this on a freshly installed instance after
> creating the table in question? If not, does it crash after you do a
> pg_dump --schema-only from the problem database and restoring that to
> the fresh instance and running ANALYZE? The crash may depend on the
> query plan chosen and that will depend on the schema in that database.
>
> We're probably going to need a recreator script for this. You might be
> able to come up with one from doing the --schema-only dump and
> restoring that somewhere and dropping unrelated objects to find the
> minimum set you need for the crash.
>
> Alternatively, a stack trace would be useful. See:
>
>
> https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
>
> David
>


-- 
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

pg 16.4, logical replication, use case = data archiving

[milist ujang]

Hi all,

I Play logical replication with row filter feature, my use case is data
archiving.
in lab env, it worked, my questions:
- during alter subscription, what about on-going DML? Can it be lost?
- any lock during alter subscription should I aware?


scenario:

# on source

tutorial=# ALTER PUBLICATION pub1 set TABLE users,likes,articles WHERE
(title <> 'del');

ALTER PUBLICATION

tutorial=# \dRp+

  Publication pub1

  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root

--++-+-+-+---+--

 postgres | f  | t   | t   | t   | t | f

Tables:

"public.articles" WHERE (title <> 'del'::text)

"public.likes"

"public.users"

wait a moment

update users set password_digest='del' where id=10;

wait a moment...

tutorial=# ALTER PUBLICATION pub1 set TABLE users WHERE (password_digest <>
'del'),likes,articles WHERE (title <> 'del');

ALTER PUBLICATION

tutorial=# \dRp+

  Publication pub1

  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root

--++-+-+-+---+--

 postgres | f  | t   | t   | t   | t | f

Tables:

"public.articles" WHERE (title <> 'del'::text)

"public.likes"

"public.users" WHERE (password_digest <> 'del'::text)


delete from users where id=10;

# source and target

select * from users;

id=10 gone at source, but exist at target



-- 
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Re: pg 16.4, logical replication, use case = data archiving

[milist ujang]

My experience with oracle streams (upto version 12.1).
there is a tag at capture, by default tag is null (if not defined).

at the session level we set DBMS_STREAMS.SET_TAG ; so whatever this session
does, will not replicate.
Maybe this kind of "tag" is planned in the upcoming logical replication
feature ?




On Tue, Aug 20, 2024 at 7:26 AM milist ujang  wrote:

> Hi all,
>
> I Play logical replication with row filter feature, my use case is data
> archiving.
> in lab env, it worked, my questions:
> - during alter subscription, what about on-going DML? Can it be lost?
> - any lock during alter subscription should I aware?
>
>
> scenario:
>
> # on source
>
> tutorial=# ALTER PUBLICATION pub1 set TABLE users,likes,articles WHERE
> (title <> 'del');
>
> ALTER PUBLICATION
>
> tutorial=# \dRp+
>
>   Publication pub1
>
>   Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
>
>
> --++-+-+-+---+--
>
>  postgres | f  | t   | t   | t   | t | f
>
> Tables:
>
> "public.articles" WHERE (title <> 'del'::text)
>
> "public.likes"
>
> "public.users"
>
> wait a moment
>
> update users set password_digest='del' where id=10;
>
> wait a moment...
>
> tutorial=# ALTER PUBLICATION pub1 set TABLE users WHERE (password_digest
> <> 'del'),likes,articles WHERE (title <> 'del');
>
> ALTER PUBLICATION
>
> tutorial=# \dRp+
>
>   Publication pub1
>
>   Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
>
>
> --++-+-+-+---+--
>
>  postgres | f  | t   | t   | t   | t | f
>
> Tables:
>
> "public.articles" WHERE (title <> 'del'::text)
>
> "public.likes"
>
> "public.users" WHERE (password_digest <> 'del'::text)
>
>
> delete from users where id=10;
>
> # source and target
>
> select * from users;
>
> id=10 gone at source, but exist at target
>
>
>
> --
> regards
>
> ujang jaenudin | Self-Employed, DBA Consultant
> http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
>


-- 
regards

ujang jaenudin | Self-Employed, DBA Consultant
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

BDR: remove junk replication identifier

[milist ujang]

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