public inbox for [email protected]
help / color / mirror / Atom feedFrom: milist ujang <[email protected]>
To: [email protected]
Subject: Re: pg 16.4, logical replication, use case = data archiving
Date: Tue, 20 Aug 2024 07:41:03 +0700
Message-ID: <CACG9ogyLRX6h0_FdhqyPhqovFQPTZww+9455+UuG+qyhZfFJLg@mail.gmail.com> (raw)
In-Reply-To: <CACG9ogy798YydEaQvjUqLF_fL8KC3XX3DQyTVHHs-h+d97yOoA@mail.gmail.com>
References: <CACG9ogy798YydEaQvjUqLF_fL8KC3XX3DQyTVHHs-h+d97yOoA@mail.gmail.com>
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 protected]> 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
view thread (2+ messages)
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: <CACG9ogyLRX6h0_FdhqyPhqovFQPTZww+9455+UuG+qyhZfFJLg@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