public inbox for [email protected]  

help / color / mirror / Atom feed

From: milist ujang <[email protected]>
To: [email protected]
Subject: pg 16.4, logical replication, use case = data archiving
Date: Tue, 20 Aug 2024 07:26:30 +0700
Message-ID: <CACG9ogy798YydEaQvjUqLF_fL8KC3XX3DQyTVHHs-h+d97yOoA@mail.gmail.com> (raw)

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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: pg 16.4, logical replication, use case = data archiving
  In-Reply-To: <CACG9ogy798YydEaQvjUqLF_fL8KC3XX3DQyTVHHs-h+d97yOoA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox