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 1sgCi0-00GfML-NK for pgsql-general@arkaria.postgresql.org; Tue, 20 Aug 2024 00:26:48 +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 1sgChx-0053Cc-VR for pgsql-general@arkaria.postgresql.org; Tue, 20 Aug 2024 00:26:46 +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 1sgChx-0053CU-BX for pgsql-general@lists.postgresql.org; Tue, 20 Aug 2024 00:26:46 +0000 Received: from mail-pl1-x634.google.com ([2607:f8b0:4864:20::634]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sgChv-000U3M-9q for pgsql-general@postgresql.org; Tue, 20 Aug 2024 00:26:44 +0000 Received: by mail-pl1-x634.google.com with SMTP id d9443c01a7336-20227ba378eso17556335ad.0 for ; Mon, 19 Aug 2024 17:26:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724113602; x=1724718402; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=MLkVemuk8jra5kTADbLMWZLkcMnclD6qsh2mhX13Xo8=; b=jz62IBZDWOww2IKbMwNweo/h1TBtoHYE5td8vz9TgZ6GbALFkc5ekE2hnEnEIPhehQ uz/oi7YUqg9ey6QuXKczGd/xye1jg0pn8lqrNxuTZs08QnaOq02ZGSYD+kqPWbIOU+yv x0AVr7YgYKVtx6WKVYknVqeYLbkkQmNGCjyCtw9cioKLQXwcTa/iKN2/T/h/O1P0k6AJ 1wt9s5rzgMlqWzWaXeB/AK8jE8E+MT7PLDp7a9e/8FZryF2r+d1G1ajL6mutw4doNwiT E/7xJmlodBZRDp3Dvgg+mYxXd3gDS5CmE0S1eEdGSI04WM14b8WsEDqTLkJ3DI7c3UdK Ul+w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724113602; x=1724718402; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=MLkVemuk8jra5kTADbLMWZLkcMnclD6qsh2mhX13Xo8=; b=J7bjPCuO8hZAgjg/s7cnybNKrW/VlDBKJqLwnbiuVgKcommzO+/aHXQAaybLcJlxQv 7Ct/1Afiq0bpuQjuLk/XqTWDRzpad9F84HfgBnEC0YXrylGdkQUAy67ZcaG6QOq2EdCs 6mEFjuruiA5ugjAW1+hek43mRgkp87GPTW2myxMAFMqxKrgG+eKX7Oeveu1sIAYpCSKr JujBUnTUJxRVYkRbzGCpdOwM0jfIEPmWAeHPEMyNtkdsVQTmBQLJF+Z4V8PzfVKEZco6 07pghgjFQkQbsFjWsTlUfmAzoJbOrYjdEqK1j8VhmxnAKkZpfY46dlOfWNCwpTclLouG tHfA== X-Gm-Message-State: AOJu0YxuWNvUP8Pti6JCl89V5SZHQ41Ty7xifkUSvDO3ZogC2Qq3u5dR SDSg02sfISjFwUALH2OakY94NZAm22OVmMhvfwdNAAzPlQHFWsE66RopXoG/nJF/pS4guVQlC99 wchkRBmXFBuqicV9KeVB74eJGQyJ0yjQ= X-Google-Smtp-Source: AGHT+IFAFjuQXKUkFhLKiFqk3YCCmZvYdM+PKra4P/HmzbSAlExBu0M039NO/FsgS+4cE5vmLiDyfkdhr6/i3sxCvuU= X-Received: by 2002:a17:90a:a787:b0:2cb:3306:b2cc with SMTP id 98e67ed59e1d1-2d3dfc2aa4amr14800160a91.1.1724113601445; Mon, 19 Aug 2024 17:26:41 -0700 (PDT) MIME-Version: 1.0 From: milist ujang Date: Tue, 20 Aug 2024 07:26:30 +0700 Message-ID: Subject: pg 16.4, logical replication, use case = data archiving To: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="000000000000c447ad0620127aef" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c447ad0620127aef Content-Type: text/plain; charset="UTF-8" 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 --000000000000c447ad0620127aef Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi all,

I Play logical replication with row filter feature, my use case is d= ata archiving.=C2=A0
in l= ab 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


tutorial=3D# ALTER PUBL= ICATION 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=A0Pub= lication 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.a= rticles" WHERE (title <> 'del'::text)

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

=C2=A0=C2=A0=C2=A0=C2=A0"publi= c.users"


wait a moment....

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


wait a mom= ent...

<= div>

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

ALTER PUBLICATION


t= utorial=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 | Trunc= ates | 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'::text= )

=C2=A0=C2=A0=C2=A0=C2=A0"pu= blic.likes"

=C2=A0=C2=A0=C2= =A0=C2=A0"public.users" WHERE (password_digest <> 'del&= #39;::text)



delete from us= ers where id=3D10;


# source an= d 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
--000000000000c447ad0620127aef--