Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sgCx2-00Gj06-SY for pgsql-general@arkaria.postgresql.org; Tue, 20 Aug 2024 00:42:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sgCw2-005FpQ-MO for pgsql-general@arkaria.postgresql.org; Tue, 20 Aug 2024 00:41:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sgCw2-005Flh-20 for pgsql-general@lists.postgresql.org; Tue, 20 Aug 2024 00:41:18 +0000 Received: from mail-pj1-x1034.google.com ([2607:f8b0:4864:20::1034]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sgCvz-000U9B-P1 for pgsql-general@postgresql.org; Tue, 20 Aug 2024 00:41:17 +0000 Received: by mail-pj1-x1034.google.com with SMTP id 98e67ed59e1d1-2d3da94f059so2716748a91.2 for ; Mon, 19 Aug 2024 17:41:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724114474; x=1724719274; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=gKi5Ujux1h3EioR4lisCSYiijMnykAAtlL/HY4QLYps=; b=Z6jOyn8u9cXnPl2pAX2z5+fDe7PoVMPPJ49JXlKU7HkyE7sfFmgG5wgTiy1N9lqPIs pOihj7BhW8FWZJGCMtYNzn7I368xidbgu35prtuZ77DZ/iDniho9Sxnl5InfCCxU7hz8 Fhq18WxCYI5vk0w/mkbzw5WBrwM8H6TMsUJXn89fcLChjeyamxeMJ3Zs9kSKjKWyCiev NFczwwNE6T9EyCEC1ZwGyI0ZVLhzdlRkzCHJwUEBqm40qnbex60mZPH1s/bzLKDRZrTC U+4nC2Cjdj/lHXagTjnEF6N7jPT2cipqeRDaeEnqOc0ThYsOPIO2NfinzQpueU7geRG9 1hxw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724114474; x=1724719274; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=gKi5Ujux1h3EioR4lisCSYiijMnykAAtlL/HY4QLYps=; b=FdiHTmxmJm05KwQh2+lkoa4MucZYazf/YJPjv6e8DzZ+ijebdKo7fjrJoULg+/6Ptf tatRlRF7Z1B+gWPLD2LqvAznJR++zePoJcJ6Wva9IsNv7OB81UYhxgYjexvoju5mVlSy BPExoTQ7GhkG/moGzoMO+zon0GzvVeKdYi0eNEryXWdh03LQlMUq7j6uTzO2s007pYt8 UjTAlpEygh2+3cvkUcI/dzlBPdWXqYx6cO9r7qjMVyRuCakwSfKSfJRfGR4ivBog0Yk9 PF+X4Rz+4cfnflKEWonoWjwIQwtAeesiZmxjuAQI6qNtnMbXWm5+jIdYbyBcVbhUNz1T Koig== X-Gm-Message-State: AOJu0YxkS11NaaCkw9aBVz2/b3MukPGKunvattPiIAHhYtIyAoTKnkcf LOeFkvm3jscIb9lJelfVODJVU+g9QNRXdWIfsB2BbMf3oIL5L6gwXFZ9bQzpwnPOsa3QGCa+1lq EgZGOB8IBAjwZfgHKoQMHWt4B1NNBlRc= X-Google-Smtp-Source: AGHT+IHiCzTMjVHNkWwH4fqR4FqufO8v/e55nkymDmw/7+4JCO+ct+11ggCY148D858uMFvdkFm0zIlcM5173LPF4gQ= X-Received: by 2002:a17:90b:b07:b0:2d3:b5ca:dedf with SMTP id 98e67ed59e1d1-2d3dffc5e78mr10609712a91.17.1724114474393; Mon, 19 Aug 2024 17:41:14 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: milist ujang Date: Tue, 20 Aug 2024 07:41:03 +0700 Message-ID: Subject: Re: pg 16.4, logical replication, use case = data archiving To: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="000000000000cc692a062012ae09" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000cc692a062012ae09 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM 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=3D# ALTER PUBLICATION pub1 set TABLE users,likes,articles WHERE > (title <> 'del'); > > ALTER PUBLICATION > > tutorial=3D# \dRp+ > > Publication pub1 > > Owner | All tables | Inserts | Updates | Deletes | Truncates | Via ro= ot > > > ----------+------------+---------+---------+---------+-----------+-------= --- > > 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=3D'del' where id=3D10; > > wait a moment... > > tutorial=3D# ALTER PUBLICATION pub1 set TABLE users WHERE (password_diges= t > <> 'del'),likes,articles WHERE (title <> 'del'); > > ALTER PUBLICATION > > tutorial=3D# \dRp+ > > Publication pub1 > > Owner | All tables | Inserts | Updates | Deletes | Truncates | Via ro= ot > > > ----------+------------+---------+---------+---------+-----------+-------= --- > > 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=3D10; > > # source and target > > select * from users; > > id=3D10 gone at source, but exist at target > > > > -- > regards > > ujang jaenudin | Self-Employed, DBA Consultant > http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab > --=20 regards ujang jaenudin | Self-Employed, DBA Consultant http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab --000000000000cc692a062012ae09 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

My experience with oracle streams (upto version 1= 2.1).
there is a tag at capture, by default tag is null (if not d= efined).

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




On Tue, Aug = 20, 2024 at 7:26=E2=80=AFAM milist ujang <ujang.milist@gmail.com> wrote:
Hi all,

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

<= br>
scenario:
<= font face=3D"arial, sans-serif">

# on source


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

ALTER PUBLICATION


tutorial=3D# \dRp+

=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0Publication pub1

=C2=A0=C2=A0Owner =C2=A0 | All tables | Inserts | Updates | Deletes | T= runcates | Via root

----------+---= ---------+---------+---------+---------+-----------+----------

=C2=A0postgres | f=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | t =C2=A0 =C2=A0 =C2=A0 | t =C2=A0 =C2=A0 =C2=A0 | t =C2=A0 =C2=A0 = =C2=A0 | t =C2=A0 =C2=A0 =C2=A0 =C2=A0 | f

Tables:

=C2=A0=C2=A0=C2= =A0=C2=A0"public.articles" WHERE (title <> 'del'::t= ext)

=C2=A0=C2=A0=C2=A0=C2=A0"= ;public.likes"

=C2=A0=C2=A0= =C2=A0=C2=A0"public.users"


wait a moment....

update users= set password_digest=3D'del' where id=3D10;


wait a moment...

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

ALTER PUB= LICATION


tutorial=3D# \dRp+

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Public= ation pub1

=C2=A0=C2=A0Owner =C2= =A0 | All tables | Inserts | Updates | Deletes | Truncates | Via root

----------+------------+---------+-----= ----+---------+-----------+----------

=C2=A0postgres | f=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | t =C2=A0 =C2=A0 = =C2=A0 | t =C2=A0 =C2=A0 =C2=A0 | t =C2=A0 =C2=A0 =C2=A0 | t =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | f

Tables:

=C2=A0=C2=A0=C2=A0=C2=A0"public.artic= les" WHERE (title <> 'del'::text)

=C2=A0=C2=A0=C2=A0=C2=A0"public.likes"=

=C2=A0=C2=A0=C2=A0=C2=A0"public.use= rs" WHERE (password_digest <> 'del'::text)


delete from users where id=3D10;=


# source and target

select * from users;


id=3D10 gone at source, but exist at target
=


--
regards

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


--
regards

ujang jaenudin | Self-Employed, DBA Consultan= t
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab
--000000000000cc692a062012ae09--