Re: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes

On Sun, 4 Jul 2021 at 15:53, Avi Weinberg <AviW(at)gilat(dot)com> wrote:

> I'm using logical replication to copy data from multiple tables to a
> single destination table. At times the structure of the source table needs
> to change. However, not all source table will have their structure updated
> at the same time. Let's assume, for example, a column type needs to be
> changed (but solution needs to work for column addition, deletion, rename
> etc.). What is the preferable approach:
>
>
>
> 1. To add another column to the destination table where its type will
> be the new type. Source tables, that have the new column type, will write
> to the new column. While source tables with old column type will write to
> the old column. A view will do the proper casting and will show a single
> column to user.
> 2. Add a new table (versioned: table_name_v2) where source tables that
> have a new structure will write to the new destination table, while old
> source tables will write to the old destination table. A view with UNION
> and casting will combine all tables.
> 3. A better way?
>
>
Does the below not work for all alter table changes on publisher.
I have been testing on a demo setup pg14beta, and subscribers are able to
catch up fine.

on publisher (port 5001)
postgres=# alter table t alter COLUMN name type bigint using name::bigint;

on subscriber (port 5002)
postgres=# alter table t alter COLUMN name type bigint using name::bigint;
ALTER TABLE
postgres=# alter subscription mysub refresh publication;

this should work for all the cases for ddl changes right.

-- demo

-- create table on pub and sub
postgres(at)db:~/playground/logical_replication$ psql -p 5001
psql (14beta1)
Type "help" for help.
postgres=# create table t(id int primary key, name text);
CREATE TABLE
postgres=# \q

postgres(at)db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.
postgres=# create table t(id int primary key, name text);
CREATE TABLE
postgres=# \q

-- insert dummy data to check sub rx changes
postgres(at)db:~/playground/logical_replication$ psql -p 5001
psql (14beta1)
Type "help" for help.
postgres=# insert into t values (1, 1::text);
INSERT 0 1
postgres=# \q

postgres(at)db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.
postgres=# table t;
id | name
----+------
(0 rows)
postgres=# alter subscription mysub refresh publication; -- this is because
i dropped table with publication enabled
ALTER SUBSCRIPTION
postgres=# table t;
id | name
----+------
1 | 1
(1 row)
postgres=# \q

-- alter table alter column change type on pub
postgres(at)db:~/playground/logical_replication$ psql -p 5001
psql (14beta1)
Type "help" for help.
postgres=# alter table t alter COLUMN name type bigint using name::bigint;
ALTER TABLE
postgres=# \q

-- alter table alter column change type on sub
postgres(at)db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.
postgres=# alter table t alter COLUMN name type bigint using name::bigint;
ALTER TABLE
postgres=# \q

-- insert new data based on new column type
postgres(at)db:~/playground/logical_replication$ psql -p 5001
psql (14beta1)
Type "help" for help.
postgres=# insert into t values (2, 100000000000000);
INSERT 0 1
postgres=# \q

-- check new data on sub
postgres(at)db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.
postgres=# table t;
id | name
----+-----------------
1 | 1
2 | 100000000000000
(2 rows)
postgres=# \q

--alter table alter col type on pub and insert data
postgres(at)db:~/playground/logical_replication$ psql -p 5001
psql (14beta1)
Type "help" for help.
postgres=# alter table t alter COLUMN name type text using name::text;
ALTER TABLE
postgres=# insert into t values (3, 'three');
INSERT 0 1
postgres=# \q

--alter table alter col type on sub, changes will not come till refresh pub
postgres(at)db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.
postgres=# table t;
id | name
----+-----------------
1 | 1
2 | 100000000000000
(2 rows)
postgres=# alter table t alter COLUMN name type text using name::text;
ALTER TABLE
postgres=# table t;
id | name
----+-----------------
1 | 1
2 | 100000000000000
(2 rows)
postgres=# alter subscription mysub refresh publication; -- refresh would
get back changes
ALTER SUBSCRIPTION
postgres=# table t;
id | name
----+-----------------
1 | 1
2 | 100000000000000
3 | three
(3 rows)

or
have I misunderstood the question?

--
Thanks,
Vijay
Mumbai, India