pg 16.4, logical replication, use case = data archiving
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
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 <ujang.milist@gmail.com> 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
--
regardsujang 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