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

From: milist ujang <[email-masked]>
Date: Tue, 20 Aug 2024 07:26:30 +0700

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

---

From: milist ujang <[email-masked]>
Date: Tue, 20 Aug 2024 07:41:03 +0700

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 <[email-masked]> wrote:

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

-- 
regards